May 27, 2009 at 5:32 pm
Pradyothana Shastry (5/27/2009)
Statistics will be updated automatically after index rebuild
Right, all this echoing is starting to get creepy. You can stop now.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 27, 2009 at 10:27 pm
If you don't need to update your statistics with full scan - then you should use the procedure sp_updatestats which will only select statistics that need to be updated.
One interesting thing I found out about sp_updatestats the other day is that even if a single row changes in a table, no matter how many rows the table has, sp_updatestats updates the stats on the table. Even a failed insert marks the table for update.
I would suggest running sp_updatestats daily and make sure the schedule runs before the index maintenance job. This will make sure you update all the statistics on the table, not just the ones on the index columns. Scheduling the job prior to the index maintenance will also ensure that if a row is table is flagged for updating the stats, you do not overwrite the fullscan performed by the index rebuild.
May 27, 2009 at 10:41 pm
Hi,
You can create this task using below command as well.
sp_MSForEachTable @command1='Update Statistics ? with FULLSCAN'
It will Update Statistics with Full Scan.
Regards
Gurpreet Sethi
May 28, 2009 at 9:11 am
Ken Simmons (5/27/2009)
If you don't need to update your statistics with full scan - then you should use the procedure sp_updatestats which will only select statistics that need to be updated.
One interesting thing I found out about sp_updatestats the other day is that even if a single row changes in a table, no matter how many rows the table has, sp_updatestats updates the stats on the table. Even a failed insert marks the table for update.
I would suggest running sp_updatestats daily and make sure the schedule runs before the index maintenance job. This will make sure you update all the statistics on the table, not just the ones on the index columns. Scheduling the job prior to the index maintenance will also ensure that if a row is table is flagged for updating the stats, you do not overwrite the fullscan performed by the index rebuild.
By updating statistics before index rebuild - you end up performing duplicate work. I use the option to resample - and perform the update statistics after the index rebuild. This makes sure that in the rare cases where a table I just rebuilt gets selected, it's going to be rebuilt using the previous sampling rate which is a full scan.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 28, 2009 at 12:11 pm
Resample is a good idea to make sure you don't overwrite the full sample by the index rebuild. You still may end up doing duplicate work, depending on how busy your system is. I have been seeing a lot of discussion around updating statistics before or after an index rebuild lately.
I guess it really depends on how busy your system is. If you know that hardly any activity is going on during your maintenance, after would be a better choice since all of the stats would be skipped anyway. If you have activity during your maintenance, you would be updating the stats for all the tables with a full scan twice, instead of just a sample followed by a full scan. I guess this is just like everything else in technology. It just depends on the situation.
May 28, 2009 at 12:17 pm
Ken - yep, it is one of the 'it depends' type of things. It really should be something that is tested and accounted for on each system you support.
There really shouldn't be a one size fits all model for performing this type of maintenance, although I see that is what everyone wants to do.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 28, 2009 at 4:42 pm
Let say we have the following case:
Rebuilding indexes are done on a case by case. (no ALL keyword)
The table contain the following indexes:
Clustered on col z
Non clustered 1 using col a and b
Non clustered 2 using col c and d
Non clustered 3 using col e and f
col g,h,i,j and not used in any indexes
If it is rebuilding only non clustered 2, will only statistics on col c and d be updated? Or will a,b,e,f and z will also be part of the statistics update along c and d?
May 28, 2009 at 4:51 pm
Column statistics are not updated during and index rebuild. Index statistics are updated for the index being rebuilt.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 28, 2009 at 5:02 pm
Ok but the question still remain.
Does the sampling before rebuilding is done only for the column(s) used by the index which will be rebuilt or all columns used by all indexes?
May 28, 2009 at 6:35 pm
Below are three sets of SQL statements that show that when an index is rebuilt or all indexes on a table are rebuilt, column statistics not associated with the indexes being rebuilt are not updated.
1) Create a database, turn auto statistics off, and populate a table:
CREATE DATABASE TestStats
GO
ALTER DATABASE TestStats
SET RECOVERY SIMPLE
, AUTO_CREATE_STATISTICS OFF
, AUTO_UPDATE_STATISTICS OFF
, AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
USE TestStats
GO
CREATE TABLE dbo.foo
(FooGuid1 UNIQUEIDENTIFIER NOT NULL
,FooGuid2 UNIQUEIDENTIFIER NOT NULL
,FooChar CHAR(8) NOT NULL
, CONSTRAINT Foo_P PRIMARY KEY (FooGuid1)
, CONSTRAINT Foo_U UNIQUE (FooGuid1)
)
GO
CREATE STATISTICS Foo_S_FooChar ON dbo.Foo ( FooChar )
GO
DECLARE @LoopOuter INTEGER
SET @LoopOuter = 0
WHILE @LoopOuter < 4
BEGIN
INSERT INTO dbo.Foo
(FooGuid1, FooGuid2 , FooChar )
SELECT NEWID()
, NEWID()
, SUBSTRING(CAST(NEWID() AS VARCHAR(128) ) , 1,8)
FROM master.dbo.tally
SET @LoopOuter = @LoopOuter + 1
END
2) List statistics last update timestamp:
select Tables.name
, stats.Name
, STATS_DATE(stats.object_id, stats.stats_id)
from sys.tables as tables
join sys.stats as stats
on stats.object_id = tables.object_id
where Tables.name = 'Foo'
3) Index rebuild and stats update - run the "list statistics" before and after executing each statement:
ALTER INDEX Foo_P ON dbo.Foo rebuild
GO
ALTER INDEX ALL ON dbo.Foo rebuild
GO
UPDATE STATISTICS dbo.Foo ( Foo_S_FooChar )
GO
SQL = Scarcely Qualifies as a Language
May 28, 2009 at 8:37 pm
Thank you Carl Federl That's exactly what I wanted to know!
As soon as I can get my hand on a SQL box I'll test your scripts, curiosity simply.
May 29, 2009 at 6:39 am
Thanks Carl Federl !
The provided scripts clearly shows what I wanted to test out.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply