Whats the best way to profile a sqlserver 2005 database for performance?

Question

What techinques do you use? How do you find out which jobs take the longest to run? Is there a way to find out the offending applications?

Answer 1

Step 1: Install the SQL Server Performance Dashboard.

Step2: Profit.

Seriously, you do want to start with a look at that dashboard. More about installing and using it can be found here and/or here

Answer 2

To identify problematic queries start the Profiler, select following Events:

• TSQL:BatchCompleted
• TSQL:StmtCompleted
• SP:Completed
• SP:StmtCompleted

filter output for example by

• Duration > x ms (for example 100ms, depends mainly on your needs and type of system)
• CPU > y ms
• Reads > r
• Writes > w

Depending on what you want to optimize. Be sure to filter the output enough to not having thousands of datarows scrolling through your window, because that will impact your server performance!

Its helpful to log output to a database table to analyse it afterwards. Its also helpful to run Windows system monitor in parallel to view cpu load, disk io and some sql server performance counters. Configure sysmon to save the data to a file.

Than you have to get production typical query load and data volumne on your database to see meaningfull values with profiler.

After getting some output from profiler, you can stop profiling.

Then load the stored data from the profiling table again into profiler, and use importmenu to import the output from systemmonitor and the profiler will correlate the sysmon output to your sql profiler data. Thats a very nice feature.

In that view you can immediately identifiy bootlenecks regarding to your memory, disk or cpu sytem.

When you have identified some queries you want to omtimize, go to query analyzer and watch the execution plan and try to omtimize index usage and query design.

Answer 3

I have had good sucess with the Database Tuning tools provided inside SSMS or SQL Profiler when working on SQL Server 2000.

The key is to work with a GOOD sample set, track a portion of TRUE production workload for analsys, that will get the best overall bang for the buck.

Answer 4

I use the SQL Profiler that comes with SQL Server. Most of the poorly performing queries I've found are not using a lot of CPU but are generating a ton of disk IO.

I tend to put in filters on disk reads and look for queries that tend to do more than 20,000 or so reads. Then I look at the execution plan for those queries which usually gives you the information you need to optimize either the query or the indexes on the tables involved.