running sp_updatestats and reindexing of tables 1

[gbraden]

Quick question.

One of the DBA has a SQL Agent Job that runs on weekends that looks at indexes, and if there is 30% or more fragmentation, the indexes are dropped and recreated.

On the other hand, as I explore the various issues we have with performance, I am seeing articles that indicate several solutions, including running sp_updatestats to update the statistics in the query plans. The recommendation is to turn off automatic updates and run a nightly manual job using sp_updatestats.

I am being told by the other DBA that this (sp_updatestats) would not be necessary because the stats are updated on those tables where the index is rebuilt during the weekend.

Is this true? We are running SQL Server 2005 with sp2 in our production environment.


Glen Braden

 

[MartinCroft]

Hi

I would not advise stopping the system updating statistic as stats have a big impact on the indexes SQL chooses to use in its execution plans. If the indexes are rebuilt then statistics are automatically updated, however if you defragment the index then you would need to manually update the statistics. sp_updatestats only gives a sample of the table compared with UPDATE STATISTICS table WITH FULLSCAN. you can use DBCC SHOW_STATISTICS(tablename,indexname) to view the statistic on a table, this will show when last updated and if you compare rows and rows sampled, in an ideal world these figures should be the same. SQL will automatically update statistics when they get out of date through inserts and updates, deletes if automatically set, if switched off they wont be updated till re-indexed. Going by what you said if a table does not meet the 70% threshold then the stats would never get updated!

A useful bit of code is STATS_DATE, showing when stats last updated. all these items are a/v in BOL

SELECT 'Index Name' = i.name, 
	'Statistics Date' = STATS_DATE(i.id, i.indid)
FROM 
       sysobjects o 
JOIN
	sysindexes i ON o.id = i.id

Hope that's some help

 

[SQLWilts]

Glen, my "two penneth":
If you can rely on your DBAs, then leave this to them. If you can't, replace them.