We have a 20 GB database and reorganize indexes and update statistics maintainance takes about 4 hours and the log files grows out of control what is a serious problem since it can not be truncated (database mirroring).
Ivan
I am using the Maintencance Plan wizard, but it only allows me to either select the "reorganize data and indexes" option or the "update statistics" option (in the Optimizations tab). I can't select both of them. What is the reason for this?
joe
We face slow performance issue for like taking long time for same query execution after We apply index rebuild and reorganize index. But, after execution of query or procedure for 2 -3 times, performance will be faster. I have following questions
1 do we need to update stats after we rebuild an reorganize index.
2. is it will be slow for 1-2 times for every query and stored procedure execution after we rebuild and reorganize index?
Is it neccessary to schedule a update statistics on index in sql server 2005 on daily basis
Is it neccessary to schedule a rebuild index on index in sql server 2005 on daily basis
Hi ..
Please advice whether i have to do update statistics along with Index rebuild (online /off line)
Thanks in advance
At one of your client sides we have configured Always on with synchronous mode.Also we have schedule rebuild index and update statistics job which runs in night every alternate day. the issue is there are more then 100 sleeping queries which is blocking update statistics job.
I have to stop update statistics job manually once i come to office manually.
Once I have killed blocking sleeping query but then other sleeping query blocked it and so on.
Hi,
I just want to know whether any advantage or disadvantage
in doing Reorganize Index And Rebuild Index ....
Plz do comment on this ASAP !!!!
Thanks in advance
Regards
Arv
Hi,
I just want to know whether any advantage or disadvantage
in doing Reorganize Index And Rebuild Index ....
Plz do comment on this ASAP !!!!
Thanks in advance
Regards
Arv
DECLARE @strIndex_Name varchar(50)
DECLARE @strTable_Name varchar(50)
SET @strTable_Name = 'DIM_AR_CLASS'
SET @strIndex_Name = 'IX_DIM_AR_CLASS_1'
PRINT @strTable_Name
PRINT @strIndex_Name
ALTER INDEX @strTable_Name ON @strIndex_Name REORGANIZE
Result msg:
DIM_AR_CLASS
IX_DIM_AR_CLASS_1
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '@strTable_Name'.
If i change the statement as below then it work fine.
ALTER INDEX IX_DIM_AR_CLASS_1 ON DIM_AR_CLASS
REORGANIZE
Anyone know why ?Does this alter index statement not support variable during execution time?
Appreciate any help.
Thanks.
Job is failing. Job contains reorganize index for 4 user databases.
Nothing in SQL error logs,
In event log and job history..just showing job was failed
One Database is huge size
Any assistance with this issue would be greatly appreciated. TIA!
Server: DBServer-1
Task Detail: Reorganize index on Local server connection
Databases: dbA,dbB,dbC,dbD,dbE,master,model,msdb
Object: Tables and views
Compact large objects
Error No: -1073548784
Error Message: Executing the query "ALTER INDEX [PK_Residential] ON [dbo].[Residential] REORGANIZE WITH ( LOB_COMPACTION = ON )" failed with the following error: "A severe error occurred on the current command. The results, if any, should be discarded.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Windows Server 2003 Standard Edition w/ SP2
SQL Server 2005 Standard Edition (9.0.3054)
Hello all,SQL 2000 on Windows 2000. If I go into all tasks, maintenance plan, itgives me an option to reorganize data and index pages. When I check onit, it populates the line "change free space per page percentage to" andputs in 10 in there. Is this the default for free space? Is it thedata pages that will have 10% free space or just the index pages? Aredata and index on the same pages?Thanks,Raziq.*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies View RelatedHi
I am trying to configure the SelectedDatabases property of the Reorganize Index Task using an expression.
The Expressions property of the task provides the ability to configure the SelectedDatabases property of the task using an expression. The properties pane shows that the type of the SelectedDatabases property should be a "(Collection)" (which is edited using the 'Object Collection Editor').
How do I create an expression to configure the SelectedDatabases property? Can I build the collection in text? Or do I need to provide a variable of type System.Object that contains a collection type (and if so exactly what type should it contain)?
TIA . . . Ed
I am build up a maintenance plan to reorganize and rebuild the index of one database. After that maintenance plan is performed, i found that most of indexes's avg_fragmentation_in_percent doesn't reduce. Is that any efficiency way to reduce fragmentation of the indexes?
View 9 Replies View RelatedWhat are some ways to analyze index coverage and usage? I have a 18 GB database, half is data, other half is indexes and I want to cut down that number as much as I can without affecting performance. Thanks
Peter Karhatsu
I have been monitoring some indexes on a table with a lot of inserts, no updates and no deletes. I was wanting to determine when to update the statistics on the index. Does anyone know what would be a good target range for the density when you run the dbcc show_statistics?
View 1 Replies View RelatedI was wondering how often you should reindex. By looking at dbcc showcontig and statistics I see that I am heavily fragmented and scan density is between 10-30% on my important indexes. I'm thinking of scheduling this to be done nightly. nay help is much appreciated.
Pete Karhatsu
At managing index SQL Server 6.5, there is distribution button. I have a database on production server, one of the table has 20 indexes. When I press the Distribution button, it reveals that most of the indexes have very poor selection of index, from 30% to 99%. There are 6 of them are very good. Based on this statistics, do you think I should remove these indexes? As the book says, if the statistics is higher than 5%, the optimizer will not use index but do a table scan. Removing those index should not affect the performance, is that right?
Your suggestion is very appreciated.
Hi
I have a question regarding updating statistics for a primary key.
Background: An update statistics with fullscan is sometimes taking 30 minutes - the table is 80 million rows, with only 4 columns. The table is truncated, and then 80 million rows inserted all in one go.
Now why the update stats is taking that long is another question (I have no idea - any thoughts?), but my question is; Since you can't disable the "not automatically recompute statistics" option for a primary key, and you would think it would be imperitive for the stats to be kept up to date for a PK for inserts.... does this mean the stats would be kept up to date? and an update stat with fullscan isn't required?
Hope someone can help
Thanks
James
I ran the DBCC SHOW_STATISTICS command for all of my indexes; I was told that high density numbers are bad, low numbers good. I have some questions about my results, though; I'm not sure how to interpret them.
Of 48 indexes, 14 have a density of 0. Does this mean that the indexes are not selective enough? Does it mean they're garbage and I should toss them?
6 have a density of NULL. They are all primary keys. I suppose this just means that they're never used because these tables are rarely queried. Would this assumption be correct?
13 have a density of 1. I have no idea what this means.
The others have densities ranging from 0.01210491 to 0.5841165. I was told that the lower this number is, the more selective and thus more useful an index is. I think 0.5841165 is too high a number. Would this be correct?
Thanks in advance.
Hello!
Is there any way to determine index usage statistics for a given table?
For examle, I have a table, with three indices. I need to know how many times each index was used. Is it possible?
And second part of question: I need to know, which user overloads my base with their giantic queries. Is there any way to determine, how many system resources each of user's sessions uses?
MS SQL Server 2000 Enterprise Edition.
Thank you!
I have an index that shows distribution statistics of 98.20%, which is very poor. I set show query plan and show statis I/O on. This table has 1113675 rows of data.
*************
select orderID, custId, intertcsi from tblorders
where intertcsi = '2815'
STEP 1
The type of query is SELECT
FROM TABLE
tblorders
Nested iteration
Index : indxInterTCSI
orderID custId intertcsi
----------- ----------- ---------
1015245 1011313 2815
2556392 2556392 2815
....
Table: tblOrders scan count 1, logical reads: 104, physical reads: 58, read ahead reads: 0
***************
Then I use the same select statement to force a table scan:
select orderID, custId, intertcsi from tblorders (index=0)
where intertcsi = '2815'
STEP 1
The type of query is SELECT
FROM TABLE
tblorders
Nested iteration
Table Scan
orderID custId intertcsi
----------- ----------- ---------
60472 61084 2815
102184 102333 2815
...
Table: tblOrders scan count 1, logical reads: 110795, physical reads: 6891, read ahead reads: 103980
When the index is not provided, the logical reads and physical reads increased dramatically. Does this tell me that I should keep that index though it is a poor selection? Is that because a huge table like this make the optimizer use the index. The query without using index takes longer time to run.
Any idea or comment would be very appreciated.
We have implemented a very small reporting database which has a main table that started off small and has now grown to around half a million rows. Initially, there were no indexes on the table apart from a clustered index, but as the data has grown, performance has dropped and so we have added a number of indexes. This has resolved the performance issues.
Before creating the indexes SQL Server had auto created a number of statistic objects (_WA_Sys_000... etc). After creating the indexes, new statistic objects where created for the new indexes. In some cases, there are duplicate statistics (auto and index) for the same columns.Should I go through and drop the duplicate auto statistics? Will having duplicates cause issues at all?
I want to be able to reproduce my production execution plans on development with copying data.
I am really puzzled by an apparent difference between table index key column order and its statistics order. I was under understanding that index statistics mirror index definition. However, in my db 2470 index ordinal definitions match statistics definition but 66 do not. I also can reproduce such discrepancy in 2008 R2, 2012 and 2014.
As per definition,
stats_column_id
int
1-based ordinal within set of stats columns
This script duplicates this for me.
BEGIN TRAN
GO
use tempdb
GO
CREATE TABLE [dbo].[ItemProperties](
[itmID] [int] NOT NULL,
[cpID] [smallint] NOT NULL,
[ipuID] [tinyint] NOT NULL,
[Code] ....
The result I get is this:
object_id stats_name
stats_column_list
1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID, cpID, ipuID,
and
object_id index_name
index_column_list
1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID, ipuID, cpID,
Also a query I used to discover this in my db is:
WITH stat AS
(
SELECT
s.object_id
,s.name as stats_name
,(
SELECT
c.name + ', ' as [data()]
FROM sys.stats_columns as sc
[Code] .....
I am using SQL 6.5. We have tables of upto 2.5GB in size. Running update statistics againts these tables takes too long, and locks out users.
Anyone know how I can make this quicker?
Davy
Hi,
I am contemplating creating a job to execute every 5 mins which will update index statisics if they are more than say 8% out. I would like to know what thoughts people have on this? i.e. pros and cons.
I like forward to what you have to say.
I have auto stats on. Our stats are often more than 10% out. At what level do you reckon the query plan might be effected by out of data stats?
Thanks
Jamie.
Is the Update Statistics found in a Maintenance Plan performed online or offline? Will it kick users out when this is run on SQL Server 2000?
Thanks
Hi,
It seems to me there are many ways to update statistics for a table. i.e. "sp_updatestats", "sp_recompile", "dbcc updateusage"
Can somebody tell me the difference between those commands and what's the best way for updating your statistics? Does reindexing update the statistics?
thx,
Wilfred van Dijk
HI,
To update statistics for entire DB i have taken the script from under given link.But need to know the
1 : what is sample percent on update statistics
2 : will it be applicable for 2005 ?
script taken from :
http://weblogs.sqlteam.com/tarad/archive/2006/08/14/11194.aspx
kindly reply soon.
How do you schedule a weekly job to do "update statistics" thru all the databases?
View 6 Replies View RelatedHi All,
I update statistics for three tables every day 2:00 AM and in the job we call
one stored procedure and, in that stored procedure only three statements are
writtern for update statistics
Like:
Exec('update statistics TBL1 with fullscan')
Exec('update statistics TBL2 with fullscan')
Exec('update statistics TBL3 with fullscan')
And this job was working fine since many months but last two days its getting fail and it gives the error messages like :
could not continue scan with NOLOCK due to data movement
So could you help me what is the solution for this
Thanks,
Aric
Hi,
I would like to know when we upgrade SQL Server 2000 database to SQL Server 2005 is it required to update the statistics even if we rebuild all the indexes or create new indexes?
Thanks,
Ritesh