Find the top n slowest queries in your database

Find the top and slowest queries in your database

When your database grows it’s easy to lose track of all processes it is executing. Wouldn’t it be great to track down slow queries that bottleneck operations?

In this article we’ll create a query that provides you with all the tools you need to analyze and optimize your database: it selects problematic queries, provides relevant information on those queries and offers you the means to find and improve them. After reading this article you will be able to reveal a top n of queries with information on each query:

• in which database and object to find the query (e.g. in the FreshPot stored procedure in the Coffee database)
• the actual query itself
• the query plan
• time of creation and last execution
• number of executions
• total, min, max and average of number of rows returned, elapsed runtime and elapsed CPU time

Notice that this article analyzes SQL Server databases but similar functionalities also exist for Postgres; check out this article for statistics on all queries in a Postgres database.

Hunting slow queries

We are going to write a query that provides information on which queries are slow and offers us the tools to analyze the query. Then we can

1. Detection: which queries are problematic?
2. Enriching: adding more information that gives us an idea about the cause of slowness
3. Analysis: where can we find the query and where lies its problem?
4. Optimization: how can we improve our queries and database further?

The full query can be found at the bottom of this article or here. Notice that some of the views we’ll be using require the VIEW DATABASE STATE permission.

1. Finding problematic queries

In this first part we use a CTE to select in which queries we’re interested. We want to take some data from sys.dm_exec_query_stats ; this keeps track of performance statistics for cached query plans in SQL Server.

The goal here is to select the records we’re interested in. In this example we select records that are new (less than 30 days since last execution) and are used frequently (more than 100 times in the last 30 days). We don’t mind slow queries that only get executed very infrequently. Next we order the queries by the average CPU time and then return the top 10 slowest queries.

Of course there are many criteria on which we can filter:

• records with a high variation in execution time (large difference between min and max CPU time e.g.)
• records with a great difference between elapsed time and CPU time (time that the CPU is actually executing; maybe the query wastes a lot of time waiting)
• order by avg_rows returned or total cpu time.

2. Providing more information about the queries

In the previous part we’ve selected queries that we execute frequently and take a lot of time to execute; these are our problem queries. The goal of this part is to collect as much relevant information as possible about these queries. Let’s go through the code

The code above is pretty long but it does only a few things:

• use the view sys.dm_exec_sql_text on line 34 to add the actual SQL statement (and cleaning the statement on lines 21 to 31)
• use the view sys.dm_exec_query_plan to add the query plan (line 35)
• use the view sys.dm_exec_plan_attributes to get the database id and object id that we’ll need later. In lines 37 to 41 we convert the dbid and objectid records from rows to columns so that we can cross apply them in line 36 more easily.

This is the output (with a anonymized statement text):

3. Analyzing our problem queries

We can now begin analyzing the queries; below are some suggestions:

1. Check out difference between cpu time and elapsed time; maybe the query is waiting a lot?
2. Check out the differences between min and max cpu and execution times; maybe schedule certain jobs at night when the server has more memory available?
3. Analyze statement text (format the sql in vscode; cntrl-shift-p → format document with → sql)
4. Click and check out query_plan

Once you have spotted a problem or an opportunity to improve the query we need to find the location of the query. We already have the database name; let’s find the name of the object where the query lives (in a stored procedure e.g.). We can do this by plugging the db_name and objectid in to the query below:SELECT * FROM CoffeeDB.sys.objects WHERE object_id = 808389949

This will tell you the type and name of the object that we’re looking for. For example:

Now we can search for a stored procedure called ‘My_Stored_Procedure’ in the CoffeeDB and speed up the query!

4. Further optimization

Check out these articles to improve your query, in particularly this one.

Conclusion

With this article you’ll be fully equipped to optimize your database! We’ve seen how the code works and how we can use it, now it’s time to apply it to your situation. Check out the full query here.

If you have suggestions/clarifications please comment so I can improve this article. In the meantime, check out my other articles on all kinds of programming-related topics like these:

• Understand how indices work to speed up your queries
• Track stats on all queries in Postgres to prevent bottlenecks
• Getting started with Postgres in Docker
• DELETE INTO another table
• UPDATE INTO another table
• Insert, delete and update in ONE statement
• UPDATE SELECT a batch of records
• Inserting into a UNIQUE table

Happy coding!

— Mike

P.S: like what I’m doing? Follow me!

Join Medium with my referral link — Mike Huls

As a Medium member, a portion of your membership fee goes to writers you read, and you get full access to every story…