Why rebuild statistics on large, older tables?
-
This question may be purely academic. But take a large table that's collected years of data already, hundreds of thousands of rows. The law of large numbers dictates in that the data contained therin will follow a normal distribution.
Statistics keeps track of indexed and key columns, and assuming we don't have a bunch of varchar(255) index colums, we shouldn't see any surprizes.
Aside from keeping a count of the number of rows in the table is it very important to maintain statistics regularly on these types of tables, and if so why?
Keith Wiggans
-
Have the best of both worlds... from Books Online...
In SQL Server 2005, sp_updatestats updates only those statistics that require updating based on the rowmodctr information in the sys.sysindexes compatibility view; therefore, preventing unnecessary updates of unchanged items.
I'm not sure if that means that you need a "0" in the rowmodctr column or if there's some percentage threshold. I do know that left to themselves, statistics update themselves when something like 20% of the data has changed. Not sure if that applies to sp_updatestats but it seems reasonable that it may.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
kwiggans (4/30/2010)
This question may be purely academic...No, you raise a number of important points. Rather than try to summarise an entire White Paper here, forgive me for just providing a link:
Statistics and the Query Optimiser
It is a reasonably long document, but quite information-dense, and you will find all the details you seek there.
Prepare for some surprises 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply