Query Profiling 101 — Yes, It Really Can Improve Your SQL Server Performance

Query profiling is how you figure out what’s going on inside the black box of SQL Server performance.

Users have it easy. DBAs don’t.

Think about the easy options users have when a database application is running poorly:

• Go get coffee and wait.
• Insult the computer.
• Complain to fellow users.
• Submit a trouble ticket.

Isn’t that the life of Riley compared to the options you have as a DBA?

• Look for blocked sessions.
• Check buffer cache hit ratios.
• Measure maximum I/O wait.
• Examine page life expectancy.
• See whether indexes are missing or need rebuilding.
• Look for locks/deadlocks.
• Check CPU usage.
• Review the application log for out-of-memory messages.
• Make sure the tempdb database is configured properly.

It could be any of those software problems, and they usually lead you to the solution of optimizing a query or changing your configuration. Or it could be a hardware problem, and the solution is to buy more memory or processing power.

Because database applications are mostly about executing lots of SQL queries, performance problems have a lot of places to hide in SQL Server. If you’re a user, you can say, “Oh, well, the problem must be inside that black box somewhere. Not my job.”

But as a DBA, you don’t have that luxury. You have to open the black box, climb in, find the mess and fix it.

Query profiling 101 with SQL server performance monitoring

In general, your goal with server performance monitoring is to keep an eye on how your SQL queries are performing over time and over the growth of transaction volume on your SQL Server. You can achieve that goal in several ways.

Have a look at the explain plan

The explain plan shows what SQL Server will do in executing the query, including the tables it will join, the type of join it will perform, the number of rows it will touch and the indexes it will use.

What can the explain plan tell you? For one thing, you can see how to improve the query itself by, for example, removing a NESTED LOOP JOIN that one of the database developers added against a huge table. Or, you could figure out from the explain plan that you need to create or rebuild an index for a certain table.

The explain plan is a good starting point for query profiling, even before you actually execute the suspect queries.

Execute the query

To execute the queries and see which resources they affect at runtime, you first create traces to mark events as they occur. With traces, you can capture data and watch for errors to arise. A profiling tool stores the data the traces have captured and displays it in a way that makes it easier for you to find and troubleshoot problematic queries.

The combination of the traces and the profiling tool can answer plenty of questions:

• Which queries consume the most memory?
• How long does each query take to execute?
• Which locks does SQL Server set for each query?
• Which queries can SQL Server execute from the buffer cache? How often does it need to go to disk?
• How many rows does each query examine?
• How many requests per minute is the database fulfilling?

You’ll get the most accurate read by executing the query on your production databases, but that can also slow processing by your real-world customers and users. If you can, test first on development or test instances where you’re not competing for memory or I/O with your production instance.

Speaking of customers and users, your goal in query profiling is to make them happy. Profiling can easily uncover dozens of problems in your database, but the reason you’ve opened up the black box is to solve the most painful issues. After capturing data from a day or two of normal use, you can find the SQL server performance problems that give your users the most trouble. Maybe a missing index is slowing down record retrieval, or too many indexes are slowing down record insertion and database updates. Maybe a frequently used query is gathering information nobody cares about anymore.

Use profiling tools wisely

Profiling tools rescue you from the tedious process of manually setting up each event, filter and procedure call for everything you want to trace. With so much going on inside the black box of SQL Server performance, you can easily capture too much data to see the forest for the trees.

Good tools enable you to carefully select what you trace so that, for example, you’re not capturing hundreds of Lock:Acquired events and needlessly filling up your display with them. However, if you do need to examine a frequently occurring event, use filters like application name or table name.

Instead of writing trace data to a table in a database, consider saving it to its own, separate file. That keeps the overhead of the trace from becoming a burden on SQL Server and potentially skewing results. If your profiling tool favors data pulled from a table, you can import the data from the file to the table later.

Improve your SQL Server performance monitoring with query profiling

Your users stay away from the black box of SQL Server performance, but you don’t have to. Query profiling is how you open up the box, figure out what’s going on inside and start troubleshooting.

But don’t wait until you’re in trouble to use it. Query profiling is more like checking the oil than replacing the engine. It helps with the usual DBA duties of updating obsolete queries and modifying design so that your databases keep pace with changes in the business.