January 30, 2007 at 10:42 am
I have auto update statistics on for a database. Certain tables get uploaded with a bunch of data every night. However when I run DBCC SHOW_STATISTICS (table,index), I see that statistics were updated over 2 days ago.
Shouldnt statistics be update after every few inserts, deletes etc?
any thoughts?
January 30, 2007 at 11:19 am
Are they very big tables?
With auto update stats, the stats get updated after a certain percentage of the table changed. With larger tables, it takes more changes to trigger the update.
I find, on my largest tables, I have to update the stats manually.
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
January 30, 2007 at 11:43 am
Well I found out that is the normal behaviour.
http://support.microsoft.com/default.aspx?scid=kb;en-us;195565
The basic algorithm for auto update statistics is:
| • | If the cardinality for a table is less than six and the table is in the tempdb database, auto update with every six modifications to the table. |
| • | If the cardinality for a table is greater than 6, but less than or equal to 500, update status every 500 modifications. |
| • | If the cardinality for a table is greater than 500, update statistics when (500 + 20 percent of the table) changes have occurred. |
| • | For table variables, cardinality changes does not trigger auto update statistics. |
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply