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

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 2

I use a few different techniques.

If you're trying to optimize a specific query, use Query Analyzer. Use the tools in there like displaying the execution plan, etc.

For your situation where you're not sure WHICH query is running slowly, one of the most powerful tools you can use is SQL Profiler.

Just pick the database you want to profile, and let it do its thing.

You need to let it run for a decent amount of time (this varies on traffic to your application) and then you can dump the results in a table and start analyzing them.

You are going to want to look at queries that have a lot of reads, or take up a lot of CPU time, etc.

Optimization is a bear, but keep going at it, and most importantly, don't assume you know where the bottleneck is, find proof of where it is and fix it.

Answer 3

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 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.

Answer 5

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.