Maintaining Statistics
Yesterday we were talking about the importance of statistics for the performance of your SQL Server database. In order to maintain the usefulness of your statistics, they have to be updated to represent the distribution in the index or table to which they refer.
You can have them automatically created and updated as data is entered. Doing this causes resources to be used maintaining those statistics as your daily work is done.
Another technique is to manage your statics during off-peak hours. So, what statistics do you maintain? Well, one option is to use the SQL Server database maintenance utility and configure a job to manage your statics according to some pre-determined parameters. I have worked on systems where this technique works fine. I have also worked on some systems where there is not a long enough window to maintain all statistics in a single maintenance window.
At this point, you have the option to roll your own statistics management process. I recommend Ian Stirk’s book “SQL Server DMVs in Action.” Ian does a great job explaining the Dynamic Management Views built into SQL Server allowing you to tune your statistics without taking all your server resources. He has a whole section titled, “Intelligently Update Statistics.”
He demonstrates the use of four system dynamic management views to determine what data has changed, and what needs to be updated.
- sys.indexes – contains details about each index
- sys.sysindexes – contains counter type data for each index
- sys.tables – contains information about the table in which an index resides
- sys.schemas – contains information about table ownership
A query joining these four tables provides all the information you require to determine what indexes need updated statistics, what table the index belongs in, and who owns the table.
That should get your feet wet. If you still need more help, get the book. There are many other examples in it that will help you become much more proficient at managing your database servers.
Do you have other resources for Dynamic Management Vviews or managing your statistics?Share them with us by sending an email to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
Encryption & Key Management for Microsoft SQL Server 2008/2012
Written by Townsend Security
Simplify encryption and key management on … (read more)
Featured Script
RebuildAllIndexes
Rebuilds all indexes in the indicated database…. (read more)