Enhancing SQL Database Performance


Overview

Performance tuning your database is always a daunting task. It can be the stuff of nightmares for even the most seasoned SQL DBA. Complex databases can create situations where something that would enhance performance in one scenario can hinder it in another. The DBA has to weigh all possible interactions with the database against each other to determine how best to implement performance enhancements.

Although I’ll be the first to admit that I don’t know everything when it comes to SQL tuning, this article will help you get those “quick gains” in performance by using Microsoft recommendations that are specifically tuned to your database. In some situations you can see gains of 30-40% or more in certain queries, depending on any enhancements you already have in place. Below may seem like quite a lengthy process, but its really not. It just lists every step in the process for clarity.

Tuning Your Database

  1. Preparation
    1. Backup your database!
      1. This is absolutely critical! If any issues arise, you want to be able to get back to where you started.
    2. Ensure that you are prepared in case something goes wrong.
      1. Depending on your environment, this could mean anything from… well nothing… to notifying management and doing this after hours.
      2. Just do whatever you need to do to cover your bases in case something goes wrong.
  2. Capture Trace
    1. Start SQL Server Profiler.
      1. This should be in your Start Menu -> Microsoft SQL Server xxxx [Rx] -> Performance Tools.
    2. Click File -> New Trace.
    3. Enter your connection information, including Server name and any authentication information that is necessary.
    4. Click Connect.
    5. Enter Performance Trace in Trace name field.
    6. Select Tuning from Use the template field.
    7. Select the Events Selection tab.
    8. Click the Column Filters button at the lower right of the dialog box.
    9. Select the DatabaseName column from the list on the left.
    10. Expand the Like comparison operator on the right by clicking on the plus.
    11. Enter the name of the database you want to tune in the box that appears under Like.
    12. Click OK.
    13. Click Run.
    14. Let the trace run.
      1. You will want to let this trace run long enough to capture every kind of normal tasks that take place against your database. You want to give the Engine Tuning Advisor the most possible information to consider when making recommendations. This means capturing for a day or two, depending on available space for trace file and number of transactions taking place.
    15. Click Stop button on toolbar (red square).
    16. Click File -> Save As -> Trace File.
    17. Save the file as Performance Trace in a folder where you will be able to find it again.
    18. Don’t exit SQL Server Profiler.
  3. Analyze Trace
    1. Within SQL Server Profiler, click Tools -> Database Engine Tuning Advisor.
    2. Enter your connection information, including Server name and any authentication information that is necessary.
    3. Click Connect.
    4. Click the binocular button in the Workload group box.
    5. Browse to the Performance Trace.trc file created in step 2.17 and select it.
    6. Click Open.
    7. Select the check box next to the database you captured from in step 2.11.
      1. You can optionally choose to only run tuning advice against certain tables by selecting those tables from the drop down to the right of the table name you just checked.
    8. Click the Tuning Options tab.
    9. Select appropriate options on this tab for your environment and what you want to accomplish.
      1. Uncheck Limit tuning time
        1. If you have a very large database, very large capture file or are in a crunch for time you might want to select this option and set the tuning time to whatever is appropriate in your situation.
      2. Select Index and indexed views from Physical Design Structures (PDS) to use in database area.
        1. Depending on your particular situation, you might be restricted to only tuning advice that utilizes your existing physical database structure (Evaluate utilization of existing PDS only) or you might be able to create IndexesIndexed views, Nonclustered indexes or a combination of Indexes and indexed views to assist with performance.
      3. Select No partitioning from Partitioning strategy to employ area.
        1. If you are interested in using partitions to increase performance, you can choose from either Aligned partitioning or Full partitioning. In this example we will not create any partitions.
      4. Select Keep all existing PDS from Physical Design Structures (PDS) to keep in database area.
        1. If you are interested in performance advice that possibly physically modifies the existing structures in your database, you can choose from Do not keep any existing PDS, Keep indexes only, Keep clustered indexes only and Keep aligned partitioning. For this example we will not modify any existing structures.
      5. Click Advanced Options button at top right.
      6. Ensure All recommendations are offline is selected from the Online index recommendations group box.
      7. Click OK.
    10. Click Start Analysis on the toolbar.
    11. Wait for the Database Engine Tuning Advisor to finish its analysis.
  4. Review Database Engine Tuning Advisor findings
    1. Recommendations tab.
      1. Estimated Improvement – Displayed at the top of the Recommendations tab will be an Estimated improvement number. This shows in percentage the value of implementing the recommendations the tuning advisor has made. Depending on the database, how it was designed and the work captured in section 2.*, this can be significant. In my case with a production database I inherited, it was nearly 30%!
      2. Partition Recommendations – This section of the Recommendations tab will show any recommendations regarding the creation of partitions to increase performance. In this article we do not go into partition creation. However, you can view the details of this section in the documentation.
      3. Index Recommendations – This section of the Recommendations tab shows the indexes that the tuning advisor recommends you implement. Here are the details on important fields in this section.
        1. Object Name – This is the name of the object in the database that the recommended index will affect.
        2. Recommendation – This is the operation that will be performed against the recommended object. In our example, this will always be create since we selected Keep all existing PDS in step 3.9.4. Otherwise it could have recommended the removal of some indexes as well.
        3. Target of Recommendation – This is the name of the proposed index.
        4. Size – This is the size of the proposed index in KB.
        5. Definition – You can click on this field and it will show you the exact text of the index it proposes to create. You can also copy it to your clipboard in case you wish to use it elsewhere.
    2. Reports tab.
      1. Tuning Summary – This tells you a lot of useful information regarding the system and database the recommendations were based on, the trace file used, the space requirements and so on.
      2. Tuning Reports – This section can be invaluable regarding what statements were analyzed, what their current and proposed costs would be, index usage, table use percentages and even what columns are referenced, how often and what percentage of use they get over other columns. There is a lot of other information in these reports as well. I would highly recommend pouring over them.
  5. Applying Recommendations
    1. With Database Engine Tuning Advisor still open, select Actions -> Save Recommendations from the menu at the top.
      1. This article assumes you will save these changes for later implementation. This is mostly because I imagine you will want to look closely at the script in this file before unleashing it on your database. If you instead want to implement these changes directly, you can choose Actions -> Apply Recommendations from the menu at the top and either skip the remaining steps or save the file for later reference to what was implemented.
    2. Enter Performance Changes in the File name field.
    3. Click Save.
    4. Once saved, click OK to the dialog verifying the file was saved.
    5.  

Where To Go From Here

Before implementing these changes, I would highly recommend reviewing what is in the file to ensure you are aware of everything that will be done to your database. This file will create objects that can dramatically impact your database (potentially badly), so be sure you know what you’re doing. To review this file, simply open the SQL Server Management Studio (SSMS) and open the .SQL script file saved in step 5.2.

When you are satisfied with what is in the file and have decided that you want to implement the changes, BACKUP YOUR DATABASE and then execute the script. It is highly recommended that you verify your backups work before making changes! All too often things happen and it’s only when you try to restore your backup that you realize that there’s a problem with it. Often I make more than one backup and verify both just to be sure.

Conclusion

I hope this has helped you perform a quick analysis of the performance that can be gained in your database. This is not meant to be a comprehensive approach to database performance, but a way to get the “low hanging fruit” quickly. Please search the Internet for SQL Performance if you’d like to read more about this subject.

If you like this article or see anything I’ve missed, please feel free to leave me a comment.

Advertisements

4 thoughts on “Enhancing SQL Database Performance

  1. hey dude!! it’s really superb.It would be more benefical if u include more information on the REports tab that deta generates.an example of the same would be more benefical

    1. Hey nithya, thanks! Yea, the article was already getting long, so I didn’t want to get into too much detail, but that’s a great idea! Thanks for the recommendation!

  2. Hey would you mind stating which blog platform you’re working with? I’m going to start my own blog in the near future but I’m having a difficult time deciding between BlogEngine/Wordpress/B2evolution and Drupal. The reason I ask is because your design and style seems different then most blogs and I’m looking for something completely unique. P.S My apologies for getting off-topic but I had to ask!

    1. No problem. I use WordPress for this blog. I don’t have much experience with some of the other blogs you mentioned, but I really like the features of WordPress, how easy it is to use, that there are lots of great themes for it and that there’s a great Android app for it. I hope that helps!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s