Get a Report of Your Server’s Top 10 Worst Queries

SQL Server

Problem: your database server’s performance stinks like Limburger cheese.

Photo courtesy of
Photo courtesy of

Solution: find the queries that are making that smell.

Hiding in Plain Sight

Your database server knows exactly which queries are performing the worst. When queries are executed, the execution plans – and associated execution statistics – are stored in memory (with a few exceptions). This information is stored in the plan cache, and you can access it by querying a couple of DMVs.

We even have a script on our website that can help you with that.

One way you can get this information is to run this query in SSMS. But what if you wanted it on a regular schedule, like every Monday morning, so you knew what queries hit your server the hardest in the last week? What if you wanted developers to have access to this information, without letting them run wild in SSMS?

Write a report!

Building a Basic Table Report

The most basic way to display information in SQL Server Reporting Services (SSRS) is by using a table. I’m going to go into Report Designer, add a new data source to connect to my instance, use a slightly modified version of the above query to return the top 10 queries by total CPU, and add a table to the report.

Here’s my design:

top 10 1

And here’s a preview of it:

top 10 2

It’s functional, but it could be better. Some of the things I’d like to see in this report:

  • The time the service started. The plan cache is only good from the time of the last restart (or the last time the plan cache was cleared, but I hope you’re not doing that on a regular basis).
  • Formatting. Some of the numbers are hard to read, and it’s ugly. No one wants to look at ugly data.
  • Measurements. Is that CPU in milliseconds? How about the duration?
  • Can I get a link to the execution plan?

Improving the Report

I’m going to add a header to the report to display the last restart date. I add a new dataset which queries sys.dm_os_sys_info for the last start time. I add a header to the report, and add a textbox. I build an expression to display the last start time.

top 10 3

The report looks sharper already!

top 10 4

To display the rest of the information, I’m going to use the list control – I think it’s one of the most under-used features of SSRS. A list lets you add multiple report elements – text boxes, tables, images – to one space and arrange them as you wish. The list starts with two textboxes, one for the number of executions and one for the last execution date. Since I’m combining text and data, I use expressions.

Then I add a table to display the CPU, reads, and duration. I make sure I add appropriate labels for the measurements. I also format each of the results as a number so the numbers have commas in them.

Then, I add another textbox, display the query text, and format it with a fixed-width font so it’s easier to read.

Here’s the design:

top 10 5

Here is what the report looks like when run:

top 10 6

That’s a lot easier to read!

I’ve knocked everything off my list except the execution plan. I’m going to add another text box and add the plan.

top 10 7

That is not readable. Here’s the issue: the plan is stored as XML, and SSRS can’t natively display XML. I could leave this here for a user to copy and paste into SSMS or SQL Sentry Plan Explorer, but that’s a lot of work. I decided to take it out of the final version of my report.

The last step is to deploy this to Report Manager. After I deploy it, users that have permissions to the folder it’s deployed to can view it – without having to go into SSMS or coming to me for the information. They can also subscribe to it to receive a copy on a regular basis.

top 10 8

The next step to be taken: tune those queries!

SSRS can be used to solve a variety of issues – it isn’t solely the realm of analysts that need to know the sales from last quarter or what products are the most profitable.

Previous Post
Performance Tuning kCura Relativity
Next Post
How Much is Offline During an Index Rebuild?

5 Comments. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.