Performing maintenance tasks in SQL Server

Problem

With any database systems there are a couple of key items that help with performance.  These items include:

• good database design
• properly written SQL code
• correct indexes
• current statistics
• defragmented indexes and data

The first two items are often the most difficult challenges, because systems grow and their usage patterns change over time getting these right the first time is not that easy. To further complicate the issue making changes to these two items is often very time consuming, difficult or maybe impossible.  When applications are first written the code is written to complete the job and the developer doesn’t think about load testing or issues as the database grows.  Also, the database is often designed to handle the need at that time, but this can change very dramatically over time. Although these two items can be modified to handle the changes in usage, the level of effort is quite high and therefore not as much is done that could be done.

The third item is something that can be easily changed as time goes by and usage patterns change in the system. By reviewing the statements that are being issued new indexes can be created, old indexes removed or existing indexes modified to meet the need.

The last two items can be handled by maintenance tasks that can be run on a periodic basis to ensure that the SQL statements are running as efficiently as possible. Maintenance tasks do not take much level of effort to do, but could provide significant results if done properly.  The core maintenance tasks should include index rebuilds, statistics updates and defragmenting indexes and data.

Solution

With SQL Server there are several options that you can use to perform maintenance tasks. The area that most people are familiar with is using Database Maintenance Plans.  This is a simple way to ensure that maintenance routines are being run on your databases.  The following screen shows you the options that can be selected to rebuild your indexes or update your statistics.

The first item is “Reorganize data and index pages”.  This option will rebuild your indexes so that the data is laid out in a more efficient manner when queries are run to use the data.  There are two options that can be used:

• Reorganize pages with the original amount of free space – this will use whatever was specified when the table was created
• Change free space per page percentage to – this will use the same setting for all tables

What this option does is determine how much free space is left on a page of data for additional inserts. Whenever there is not enough space on the page SQL Server needs to do a page split where a new page is created and some of the data from this existing page will go to one page and other data will go to another page.  So this setting could be very critical in a very busy system.

The second item is “Update statistics used by query optimizer”.  This option will rebuild the statistics on the tables which SQL Server will use to determine how to access the data.  From the statistics SQL Server determines what index to use and whether to use the index or just read through the entire table.  The “% of the database” tells this process how much data should be sampled to determine the statistics.  If you specify 100%, SQL Server will read through all of your data to rebuild the stats. 

A couple of notes: when you rebuild the indexes the stats are rebuilt as well. This is why the option is disable if you select “Reorganize data and index pages”  Also, when you create your databases you have the option of setting “Auto Create Statistics” and “Auto Update Statistics”.  So when data changes these changes are applied to the statistics automatically.

In addition to setting these tasks up using Database Maintenance Plans, all of this work can be done using T-SQL commands.  Following is a list of some of the commands that you can use in place of the Maintenance Plans.  These can be put in SQL Agent jobs or run from Query Analyzer.

The advantage to using T-SQL commands is that you can control what happens and when it happens. With the Maintenance Plan the same operation is done on all tables.  With the T-SQL commands you can set which tables to rebuild indexes on or even which index to rebuild.  Using DBCC SHOWCONTIG you can also see how fragmented a table or index is and then determine whether you need to rebuild the index or not.

For additional reading on any of these commands or processing refer to SQL Server Books Online or click on the links above.

Next Steps

• Make sure you have a maintenance process in place to rebuild indexes and update statsistics.
• If nothing else, you can use a Database Maintenance Plan.
• Take a look at these other options in this tip to make your maintenance routines more robust.
• To improve the performance of your system, take the time to understand your index fragmentation and statistics.