Crash Course in SQL Server Database Tuning Made Easy
Author: Sean Howe, Ross Group, Inc.
August 2010
Situation
Your application and SQL Server 2005 database have been running for several months. While you know the hardware or the network have not changed, you do know that you have been adding data into the database as you see your database file growing. Even more importantly, the application is slower to respond and users are complaining about how long daily operations are taking.
Problem
As the amount of data grows within the confines of the Microsoft SQL Server 2005 database, it takes longer to scan a table to retrieve the requested data.You do not have a dedicated DBA to look at the performance.Your application was developed by another organization and they are slow to respond to your concerns regarding performance.
Tips & Tricks
Use tools, built into Microsoft SQL Server, to trace and analyze sql that is creating the work on the database and look for opportunities to make those queries more efficient; hence, the application faster and more responsive.
The tools we will use to accomplish this are SQL Server Management Studio, SQL Profiler and Database Engine Tuning Advisor.
1. Launch Microsoft SQL Server Management Studio via Start> All Programs>Microsoft SQL Server 2005> SQL Server Management Studio (Figure 1).
2. Authenticate to the database server.
3. On the top menu choose Tools> SQL Server Profiler.
4. After SQL Profiler starts, it’s time to start collecting data. From the menu, choose File >New Trace.
5. Now Authenticate to the database server for SQL Profiler.
6. Save the trace to a file by checking Save to File and choose an appropriate location. If you enable file rollover, be aware that you could fill up your volume if you do not monitor the process. Increase the size of the trace file limit to 20MB.
7. There is a lot of customization that can be done during this step.You can filter on given database attributes. For example, you can only trace activity for a given database or given user. I encourage you to look at this based upon individual needs.
8. To begin the trace, select Run and you will begin to see activity as shown in (Figure 2).
9. Now run the application for those functions that seem to be running more slowly than they have run in the past.
10. Stop the trace by selecting the standard push button on the menu.
11. Now you have collected the queries and related information used to retrieve data that fulfill your application requests. Next, use the Database Engine Tuning Advisor.
12. From within SQL Server Profiler and the upper menu bar go to Tools> Database Engine Tuning Advisor.
13. Authenticate to the database server using windows authentication or a SQL Server username and password.
14. In our previous step we created a file that will be used as input to the Database Engine Tuning Advisor. In the workload section, browse to the location of your .trc file created with SQL Server Profiler and select it.
15. In the bottom section, select the database you are most concerned about or that corresponds to any filter you created during the trace collection. If you did not filter out databases, you can select all databases. The tuning advisor will interrogate objects that correspond to the individual sql statements within the trace file. It’s smart that way.
16. Choose Start Analysis from the menu.
17. After the analysis has been performed the tool will indicate what percentage improvement would likely be obtained as a result of implementing its recommendations.
18. From the Database Engine Tuning Advisor menu choose Actions> Save Recommendations.
The recommendations file will contain the SQL DDL that will implement the changes necessary to incur the performance boost indicated by the tuning engine. At this point, having someone look at the recommendations or doing some research
on the individual recommendations would be advised. Most importantly, this process can identify indexes that can dramatically speed up data retrieval.
Indexes do take up disk storage and there is some overhead associated with maintaining an index, but these disadvantages are overcome most of the time with the advantages they offer. After you have eliminated scanning your tables for data, looking at your server memory, i/o configuration, and other server configurations is advised. Note: all of these items involve an investment.
The contents of these Tips are already paid for with your initial purchase of the Microsoft SQL Server product.
If you have any questions or need support to tune your SQL Server databases, please contact Gary Codeluppi at 937-912-3273 or visit the Ross Group Inc web site at www.rossgroupinc.com.
