Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Performing maintenance tasks in SQL Server


By:   |   Last Updated: 2006-07-07   |   Comments (2)   |   Related Tips: More > Maintenance

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.

T-SQL Command Description
DBCC SHOWCONTIG Displays fragmentation information for the data and indexes of the specified table.
DBCC DBREINDEX Rebuilds one or more indexes for a table in the specified database.
DBCC INDEXDEFRAG Defragments clustered and secondary indexes of the specified table or view.  DBCC INDEXDEFRAG is an online operation, so it does not hold long-term locks that can block running queries or updates. DBCC INDEXDEFRAG can be considerably faster than running DBCC DBREINDEX because a relatively unfragmented index can be defragmented much faster than a new index can be built.
   
DBCC SHOW_STATISTICS Displays the current distribution statistics for the specified target on the specified table.
UPDATE STATISTICS Updates information about the distribution of key values for one or more statistics groups in the specified table or indexed view.
sp_autostats Displays or changes the automatic UPDATE STATISTICS setting for a specific index and statistics, or for all indexes and statistics for a given table or indexed view in the current database.
sp_updatestats Runs UPDATE STATISTICS against all user-defined tables in the current database.

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.


Last Updated: 2006-07-07


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, November 09, 2012 - 1:20:15 PM - harsha Back To Top

Hi,

 

All MSSQLTIPS  articles are excellent. But it would be great if you could start writing articles using SQL Server 2008 or 2008 r2 versions as we are in 2012 and Denali is already in the market.

Thank you for all your efforts and it is a great resource for the DBA's.

Thanks again!


Friday, June 15, 2012 - 2:21:26 AM - Viji Back To Top

What is the command(sql query) to get the order of tables in which maintenance will be going on in the server?


Learn more about SQL Server tools