One of the most important aspects of keeping your system running well is updating and managing your statistics. Since the optimizer relies on statistics to determine the best execution plan it is imperative that you keep an eye on them and maintain them just like you would indexes. One of the disappointing things in SQL Server 2005/2008 is the deprecation of some of the functionality of the sysindexes system view. In particular the rowmodctr column, since there is no equivalent in any of the new sys schema DMV’s. With that said, the GatherStatisticsInformation stored procedure listed here is missing that bit of information.What I do include is the last time the statistics were updated for a particular index. It is a rare thing to see information from SQL Server take a step backwards. Hopefully they will correct this mistake and add the modified row count back in to a DMV that is accessible.
Series to Date SQLDIY: Manage and Monitor SQL Server Yourself
Link to the script Gather Information On Statistics
I’m hosting all my scripts from this series on GitHub as I do with all my open source projects.
As always, if you find any bugs please let me know and I will correct them!
Your Homework:
Statistics Used by the Query Optimizer in Microsoft SQL Server 2008
SQL Server 2008 Statistics Jose Barreto (blog|twitter)
Lies, Damned Lies and Statistics Elisabeth Redei
How Stale are my Statistics? Kendra Little (blog|twitter)
And everything on SQL Skills
