How to tell if Index stats need updating?

Question

How to tell if Index stats need updating?

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

DBADave SSChampion Points: 10962 More actions · Answer 1

Looks like I'm basically out of luck with trying to determine when statistics need updating. I'll give up on that process and turn my attention to determining why Update Statistics appears to always get stuck in a 160GB database on a 64bit Enterprise Edition. 8.5 hours and still running. Always something...

By the way, here is the vendor's response when asked why they recommend turing off AUTO CREATE / AUTO UPDATE.

The reason we turn off Auto Create/Update stats is because the Database Tuning Advisor can kick off Update/Creation during production time. If you’re running a massive job during the day, there is a high possibility that the advisor would go to work thus crippling your system. Updating/Creating stats are very cpu intensive and should only be done manually during off hours.

george sibbald SSC Guru Points: 104210 More actions · Answer 2

even with Gails info it sounds as if rowmodctr value is still in the ballpark so perhaps is the best guide you are going to get.

Else possibly run update stats with a sample value rather than fullscan?

I have seen reports where auto update stats causes an overhead because it runs so often but that was from people running close to their servers capacity and they were clutching at straws. I would have thought 99% of the time letting update stats run was better than having out of date stats.

stats update only takes a while on a large table, but on a large table it would take a lot of updates to cause it to run. I am not sure but I doubt auto update stats does a fullscan.

how about turning it on and monitoring how often it runs and cpu usage with and without.

might be their app but its your database and data.

---------------------------------------------------------------------

DBADave SSChampion Points: 10962 More actions · Answer 3

I'm going to turn it on, but before I do so there is a Trace flag I am trying to find that will log everytime Auto Update runs (I believe).

Another option is to leave it on, but turn it off on specific tables. Only a small number of their tables are very large

george sibbald SSC Guru Points: 104210 More actions · Answer 4

DBADave (10/16/2008)
I'm going to turn it on, but before I do so there is a Trace flag I am trying to find that will log everytime Auto Update runs (I believe).
not aware of one, but could be. there is a profiler event you can use.
Another option is to leave it on, but turn it off on specific tables. Only a small number of their tables are very large

dont think you can do that, unless thats another SQL2005 option i have missed.

see my comment in middle of your quote!

---------------------------------------------------------------------

DBADave SSChampion Points: 10962 More actions · Answer 5

This Microsoft comment pertained to 2000. I'm not sure if it also works with 2005.

It is also possible to enable trace flag 8721, which will dump information into the error log when AutoStat has been run. The following is an example of the type of message that you can expect to see:

1998-10-14 16:22:13.21 spid13 AUTOSTATS: UPDATED Tbl: [authors]

Rows: 23 Mods: 501 Bound: 500 Duration: 47ms UpdCount: 2

Kalen wrote an article on some trace flags.

http://www.sqlmag.com/Articles/ArticleID/96767/www.windowsitpro.com/go/cdfundamentals/www.msbiconference.com/?code=sqlflink

Trace Flags

SQL Server 2005 SP1 introduced the T-SQL trace flag 2389, which detects when the statistics on a particular index are repeatedly ascending. After three consecutive statistics-updating operations have noted ascending values exceeding the previous upper boundary of key values, SQL Server internally marks the index as an ascending index. You can use the undocumented trace flag 2388 to show you whether an index's leading column has been determined to be ascending. This trace flag changes the output of the DBCC SHOW_STATISTICS command to give you a historical look at the most recent statistics-updating operations. In this case, the only column in the DBCC SHOW_STASTISTICS output you'll be interested in is the last one, called Leading column Type.

Another trace flag, 2390, introduced in SQL Server 2005 SP1, enables the same behavior as trace flag 2389 - but only in cases where the optimizer doesn't know whether the leading index column is ascending. So if you use both 2389 and 2390, your statistics should be automatically updated much more often. You should never use trace flag 2390 alone, since doing so would mean that statistics would be updated only when the ascending nature was unknown and not when the column was known to be ascending. If you're interested in exploring how to use trace flags 2389 and 2390, see the Microsoft articles "FIX: You may notice a large increase in compile time when you enable trace flags 2389 and 2390 in SQL Server 2005 Service Pack 1"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

DBADave (10/16/2008)
The reason we turn off Auto Create/Update stats is because the Database Tuning Advisor can kick off Update/Creation during production time. If you’re running a massive job during the day, there is a high possibility that the advisor would go to work thus crippling your system. Updating/Creating stats are very cpu intensive and should only be done manually during off hours.

The database tuning advisor? That's a load of garbage on it's own. The tuning advisor's a tool that has to be run manually.

Yes, autoupdate stats does have an impact. I've never seen it cripple a system though.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

george sibbald SSC Guru Points: 104210 More actions · Answer 7

dbadave,

perhaps this is your answer, a sort of halfway house, available from SQL2005 onwards, use AUTO_UPDATE_STATISTICS_ASYNC option

see

http://www.mssqltips.com/tip.asp?tip=1193