Log in

Leonard Sperry

Whimsical musings of a Software Engineer, Seattlite, and Father

Tuning your MSSQL Database

by Leonard

This article is intended to teach you how to tune your MSSQL Database.  This article was written using MSSQL 2008 Developer Edition, but concepts should carry over to other versions.

What you need

A lot of development is done using MSSQL Express.  The tools described in this article are not available in the express version.  If you do not have a full version or a developer edition, I highly recommend you purchase a developer edition.  Prices are very reasonable and the payoff can be huge.

Getting started

The first thing you need to do is profile your database under load, but you’re going to need some place to store that profiling information.  You can use a file on disk, or you can use another database.  I recommend the later.   Create a new database called “Profiling”. 

Next, you will use SQL Server Profiler.  You can get to this application via your start menu or from the tools menu in SSMS. (This option is not available in the express version)

image

If you never used the SQL Server Profiler, you should spend some time exploring the application.  Think of it like a debugger for SQL.  You can use to to see all the actual information that is being sent to/from your application. 

Next we need to create a new trace. Click “New Trace” from the file menu or the icon in the toolbar.

image

You are presented a window for setting up the properties of the new trace.

image

  • Make sure to select “Tuning” under “Use the template”. 
    • There are several templates you can use and you can make your own to trace template for tracking different events.  For tuning, we need a specific template that the Database Tuning Advisor will use later.
  • Select “Save to table”
    • select your new “Profiling” database and a table to save the data to.
  • Next we need to make sure that we are not profiling the “Profiling” database or else our profiler will get into an infinite loop.
    • Select “Events Selection” tab.
    • Select Column Filters
    • Select DatabaseName
    • Click “Not like” and enter “Profiling”

image

    • Click “OK” then “Run”

You are now profiling your SQL Server. Time to give some load to your application. Run your application and perform actions that will call SQL or use a load generating tool.  Make sure that the load you put on your application will reflect a real world scenario.  This part is VERY IMPORTANT.  Yes, I just used all caps.  The Database Tuning Advisor that we will use in the next step will give recommendations based on the data in the trace.  So, if you do a bunch of inserts during the trace, but in a production environment, a higher percentage of selects are done, the recommendations for indexes will not actually be the best recommendations.

When you feel you have sufficient data, click the “Stop Selected Trace” button from the toolbar.  Your data is now saved to your Profiling database and is ready to be consumed by the Database Tuning advisor.

Analyzing the data

Now that you have your trace data, let’s find out how much we can improve your database.  For this you will need to open the Database Tuning Advisor.  You can find it in your start menu or from the Tools menu in either the SQL Management Studio or SQL Server Profiler. 

Once the application is open and connected to your database.  Click “Start New Session” from the File menu or the icon in the toolbar.

image

This is where thing get interesting, confusing and awesome.

image

  1. Under "Workload” select “Table”
  2. Click the binoculars and select your Profiling database
  3. For “Database for workload analysis” you need to select a database you are trying to tune.
  4. Also select the database being tuned under “Select databases and tables to tune”

Finally, in the toolbar click “Start Analysis”.  This is where the magic happens.  Depending on the size of your dataset, this step can take several minutes to an hour.  It is essentially creating execution plans for each statement that exists in the trace and calculating what indexes would help.  When it completes, you will be presented with indexes to add or delete to improve the performance of your database.

  • Review the recommendations.
  • Select those you’d like to apply.
  • In the Actions menu select “Apply Recommendations”

If you’ve never gone through this process or have not spent time deciding what indexes you need, you can expect to see performance increases of 30-50% or More !


TAGS:

Code

blog comments powered by Disqus

About the author

Leonard is a Software Engineer who works for a web CMS firm in Seattle Washington.  He is the primary technical contractor for Seattle Engraving.   Where he built a custom CRM to help manage customer interactions and will soon be rolling out a new and improved website based on Orchard CMS.

He has lived in the Puget Sound region for most of his life, and is a father of 3.

Month List