The Magic of SQL Profiler
By Kalman Toth, M.Phil., M.Phil., MCDBA

March 29, 2005

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 procs (sprocs) in need of optimization. The worst offender did almost a million reads.

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