SQL Server Tuning

Although tuning a database is fairly easy, you can make things worse if you don't know what you're doing. Be careful when performing this.

Check the DB_ID

1. Create an account in SQL that has sysadmin rights. This user is to be removed upon completion of the tuning
2. From your workstation, use Management Studio tpo log into the database server using a sysadmin SQL user
3. Expand databases, right-click the database you'll be profiling and select New Query
   • Note: If you're using Enterprise Manager, go to Tools > SQL Query Analyzer

4. Execute the following query:

   SELECT DB_ID()				

5. Remeber the DB_ID number that was returned

Run a trace on the DB

1. From your workstation, open SQL Profiler
2. Choose File >> New Trace >> Connect
3. Connect with the SQL sysadmin user you created earlier, this will open the Trace properties window

4. Choose the General tab

   • Specify a Trace Name
   • From the Use the template dropdown box select Tuning

   • Select the checkbox for Save to file

     • Specify a location to save your trace to in the popup window

5. Choose the Events Selection Tab

   • Select the Checkbox for Show all columns
   • Keep the existing Data Columns events selected and add these additional ones:
     • CPU
     • Reads
     • Writes
   • Click Column Filters
   • Select DatabaseID from tjhe left side, expand Equals from the right side and specify the DB_ID from the previous section.
6. Click Run
7. Stop the trace after about 50,000-75,000 rows of data, or 1 hour - whichever comes first. See the notes below for reference while that is running.
   • There is no correct number of events to capture or a correct amount of time to run the trace. As a general rule of thumb, get anywhere from 50,000-75,000 rows of data or 1 hour, whichever comes first.
   • For very active databases it's best to ask someone with more experience for some help.
   • It's very important to trace during a good time, not during backups - but likely during the time when the database is busiest.

While the trace is running, keep the following in mind:

Run the Tuning Advisor

This will put a heavy load on the server but should be performed during peak times in order to ensure accurate tuning results.  

If you have a really small workload (say, less the 1MB) then you can do this whenever you want only if you keep an eye on performance on the db server to ensure it doesn't cause an unreasonable CPU spike or slows I/O too much.

1. Open the Database Engine Tuning Advisor
   • Programs >> SQL Server 2005 >> Performance Tools
2. Connect with the SQL sysadmin user you created earlier
3. On the General tab
4. Ensure the File radio button is selected in the Workload section.
5. Click on the little binoculars button to locate the trace file(s) you created in the previous section
6. From the Database for workload analysis drop down box select the database you are tuning.
7. Select only the checkbox next to the database you are profiling at bottom of the screen.
8. If you are running this late at night uncheck the Limit tuning time check box, otherwise, during the day keep it checked (and don't forget to monitor db server performance!!).
   • Click the Start Analysis button and wait for the tuning recommendations to complete.
   • When the tuning completes go to the Actions menu and choose Save Recommendations.
     • Save it to a location where it will be available for a week or so. You probably won't need the file but it's always good to keep it around for a week or two after the tune in case something needs to be rolled back.
   • From the Actions select Apply Recommendations.

Note what % increase in performance the tuner said it would give, this is a theoretical improvement and what you will see from the application will likely not match the theoretical DB performance improvement.

Cleanup

1. Remove the sysadmin user you created in the first section