SQL Server, Analytics, .Net, Machine Learning, R, Python
Mitch Wheat has been working as a professional programmer since 1984, graduating with a honours degree in Mathematics from Warwick University, UK in 1986. He moved to Perth in 1995, having worked in software houses in London and Rotterdam. He has worked in the areas of mining, electronics, research, defence, financial, GIS, telecommunications, engineering, and information management. Mitch has worked mainly with Microsoft technologies (since Windows version 3.0) but has also used UNIX. He holds the following Microsoft certifications: MCPD (Web and Windows) using C# and SQL Server MCITP (Admin and Developer). His preferred development environment is C#, .Net Framework and SQL Server. Mitch has worked as an independent consultant for the last 10 years, and is currently involved with helping teams improve their Software Development Life Cycle. His areas of special interest lie in performance tuning
Monday, February 13, 2012
SQL Server Maintenance Plans: Updating Statistics, A Simple Gotcha
Out of the box, SQL Server’s maintenance plans enable you to set up common maintenance tasks such as running a CHECKDB, backing up up databases and logs, rebuilding indexes etc.
In terms of rebuilding indexes, there are two flavours: a reorganise and a full-blown rebuild (Reorganizing and Rebuilding Indexes). Reorganising an index is usually done when the index is not heavily fragmented.
It is not uncommon to find a maintenance plan that first full rebuilds all indexes (regardless of fragmentation level, but more on that in a moment), and then subsequently updates all statistics. Not only is this unnecessary, it is actually worse than simply rebuilding all indexes! The update statistics task samples your data, whereas a full index rebuild also updates the associated statistics but does so by performing a FULL SCAN of your data. That’s right: updating statistics with the default setting of sample after a full index rebuild will leave you with (potentially) less accurate statistics.
Rather than rebuilding all indexes regardless whether they need it or not, Ola Hallengren’s maintenance script contains a production tried-and-tested stored procedure SQL Server Index and Statistics Maintenance for rebuilding and reorganizing indexes and updating statistics. It has ability to run for a set period to fit into tight maintenance windows, set fragmentation threshold levels, and update statistics, such as unindexed column statistics that are not updated as part of an index rebuild. Highly recommended.
MSN, Email: mitch døt wheat at gmail.com