SQL Profiler is a DBA's Best Friend

By Kalman Toth, Business Intelligence Architect

September 15, 2007

The other day someone has posted a challenging issue on a forum: My CPU is running 70% of capacity consistently. What should I do?

Start a SQL trace with duration greater than 1000 msec was my advice to her. That is queries and stored procedures taking longer than 1 second.

She did, and the results were astonishing: 50 stored procedures (sprocs) in need of optimization. The worst offender did almost a million reads. Generally performance hogs are doing excessive reads. Rarely too many writes or too much CPU use is the issue.

Since SQL Server is not used for scientific computing typically, CPU running near 100% is an indication of excessive IO activity. An OLTP stored procedure which is properly using the indexes, should not do more than 1,000 reads. Decision support, data warehouse and business intelligence stored procedures can easily do a million reads, but they usually run on a different server such as the standby.

Typically we can start by monitoring the queries and sprocs which take longer than 10 sec. After optimizing these, we can lower the bar to 8 sec. Finally, we want to optimize stored procedures taking longer than 1 sec. Generally below 1000 msec (sub-second) performance is acceptable in most OLTP operations.

For proper analysis, we should run SQL Profiler on a different server and save the trace into a table on a second server. If we were to run on the same server, the monitoring itself may make the performance worse.

Besides monitoring for slow running queries and stored procedures, SQL Profiler can be used to monitor many other database events. Among them: database file auto-growth, security audit, deadlocks and more.

Deadlocks were pretty hard to resolve in SQL Server 2000. Not any more!  SQL Server 2005 has a deadlock visualizer which graphically displays the parties to a 2-way, 3-way, etc. deadlock. It is a real dream tool for a DBA who gets deadlock messages on a regular basis.