UPDATE STATISTICS

hi,

Does UPDATE STATISTICS block or lock tables ?

Ive heard alot on this subject with different opinions RE SCH-M, SCH-S locks etc so I'm not sure what is correct, there doesnt seem to be anything stated from microsoft on it.

I have a 300+ GB DB with only a 4 hour maintenance window, not much, this 4 hours has to be spent REINDEXing what tables I can in a monthly schedule and Defragging the big daddy Tables.

From what Ive read REINDEX will update the stats, so its more the big tables that get defragged I'm worried about.

The server hardware should handle any performance hit from the UPDATE STATISTICS command but its the access to the actual tables that I'm concerned about.

Any help would be great.

thanks

Hi

UPDATE STATISICS does not lock the table for which it is executed.The table is available to use.It makes dirty reads to get its info.

cheers
Parik****

thanks guys,

Just one more thing, I cant find out much info on the WA_SYS indexes or statistics.


As REINDEXing a tablename will drop and create all indexes, is this how the stats actually get updated or is there some other procedure that SQL performs to UPDATE STATS after a REINDEX ?

Would these take a long time to reindex on large tables or should I not worry about them ?

Hi

Time taken would depend on the size of ur tables.Also while using DBCC REINDEX your tables would not be available to users.It is an offline method.But as indexes are dropped and created the statistics are updated.

You can use DBCC INDEXDEFRAG.This is an online method.
more information on
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_30o9.asp

But i am not sure if DBCC INDEXDEFRAG updates statistics automatically.



cheers
Parik****

thanks

I'm quite sure indexdefrag doesnt update stats, was just wanting to know which part of the reindex statement actually updates the stats

Basically I will be doing REINDEX on all tables apart from those that will take over the maintenance window which will be defragged, I will then run update stats on those defragged tables.

As update stats doesnt block I will also now try and sneak in sp_updatestats on a daily basis in the early hours of the morn after backups have completed. If i havnt got time for the whole DB, i'll just do a couple of the main tables

Thanks for the swift responses

Indexdefrag does NOT update stats.
You need to run:
UPDATE STATISTICS TableName WITH FULLSCAN
-- WITH FULLSCAN is an option

Remember also:
EXEC sp_recompile TableName
You need to set the table that has updated stats for Recompile. Then ANY sproc that hits that table will recalc its execution plan with the new table Stats. THIS IS IMPORTANT as your sprocs might still work sloooowly. (And you will wonder why the same statement in Query Analyser is fast!)

If you have a heap of tables that you would like to create Statements for Indexdefrag, Update Statistics and Recompile you could so something like this:
select 'DBCC INDEXDEFRAG (DataBaseName, ' + NAME + ', PK_' + NAME + ')' from sysobjects where xtype = 'u'
-- This assumes your Primary Key starts with PK_ and ends with the table name.

select 'UPDATE STATISTICS ' + Name + ' WITH FULLSCAN' from sysobjects where xtype = 'u'

select 'EXEC sp_recompile ' + Name from sysobjects where xtype = 'u'
OH And remember too, if your DB is set to AUTOGROW and index pages become defragmented, why not GROW your DB to a very big size and then not have to put up with the pain of Defraging quite so often? (depending on your circumstances this can help a lot).
Hope it helps.

Satya, I thought "why not!".
You have some good SEO pointing to that topic and the extra info on updating stats and recompile might help someone out there searching on Google.
Keep up the great work! This site is an excellent resource for DBA's.
[]

Thanks abroadway that sp_recompile may just be the thing I was missing from my maintenance plans.
Jake

Thank you all - the piece I was missing was running update stats before sp_recompile - so thanks to people who update posts even after two years!
So the process I'm going to use is:
1. Indexdefrag
2. Update Staistics
3. sp_recompile
If only SQL Server would do this itself then I'd have more time for