Examining Expensive Queries with SQL Server Activity Monitor

Managing a SQL Server instance can be a complex endeavor, but luckily, there are some valuable tools available that are built in to SQL Server. When used correctly, Systems Administrators can find the information they need and make sure that their instance is running correctly.

In my last blog, I gave a detailed overview of the 5 major sections of SQL Server Activity Monitor. In this second part of our ongoing series, I will go into more detail on the Recent Expensive Queries pane and talk a little about Query Execution Plans.

Troubleshooting Application Performance

Whenever I am troubleshooting slow application performance and looking at the SQL Server end of things, I always start with SQL Server Activity Monitor. After looking at the Overview pane, the next pane I move to is Recent Expensive Queries. This gives me a close to real-time look at any major queries being run against the databases of the SQL Server instance.

Once I have opened the Recent Expensive Queries pane, I watch the queries being run on the SQL Server instance using the default sort settings: which orders queries by CPU usage against all databases. This lets me see if there are any queries running on any of the databases that are using up a lot of CPU resources.

When looking at query data in the Recent Expensive Queries pane, we always want to watch for a minute or two in each sort setting to get an understanding of what is flowing through the system before moving on to the next sort setting. I try to do this during high usage times for the application or during times when users are reporting performance issues.

The screenshot below shows an example of the output of recent queries being run against a SQL Server instance using the default sort settings. If we were looking into a performance issue in this instance, we would most likely want to look into the highlighted query a little more.

After watching the Recent Expensive Queries pane using the default sort, I then normally sort by executions per minute (Executions/min) and logical reads per second (Logical Reads/sec) on all the databases. I do this by simply clicking on the column header of the column I want to sort by. I keep an eye out for any queries that seem to be using more resources then normal and investigate as needed. I’ll look at how to investigate these queries in a minute.

With an instance that has more than one user database, if I start seeing a large number of expensive queries running against a database or databases other than the one used by the application I am troubleshooting, I will note this, and then I will collect some data on the queries and the database they are being run on. If I don’t find any clear issues related to the code and database for the application I am working on, I will contact the administrators of the other high usage databases in the instance. Keep in mind that in a shared instance, if one database is using a lot of resources, this can impact other applications’ performance in a negative manner.

Once I have done this and feel that there is not an overall query load issue on the instance as a whole or that another database is not adversely impacting mine, then I run the same sort settings on only the database used by my application. This blocks out all the other things going on in the instance and shows me only the query load on the database of the application I am working with. I do this by selecting the database I am working on from the drop down menu at the top of the Database column. 

Let’s return to the query highlighted in the screenshot above. This query is running over 5 million times a minute on the database for my application, so it’s one I want to look into further. To do this, I select it and then right click on it. This will give me the option of editing the query text or viewing the execution plan for the query.

In this case, I want to view the execution plan for the query to see if there is anything I can do on the SQL Server end of things to improve performance. When viewing the execution plan, I see that the query is really two nested loops and all the heavy lifting is being done by index seeks on three tables:

When working with a third party application (in this case, a web based content management system), index seeks are one of the most efficient ways to retrieve data, and this execution plan is about the best we can hope for. That means that if I want to reduce the load on the database from this query, I am going to have look outside of SQL Server.

To help me get a better understanding of the query and where to go next, I will now look at the text of the query. I do this by going back to the Recent Expensive Queries pane and selecting the Edit Query Text option mentioned above. This allows me to read the SQL statement and figure out what the application is looking for:

From reading the text of the SQL statement, I see that the query is really just a request for data related to content in the system. This is made clear by the ‘WHERE’ clause of the SQL statement above, which states the content ID and the language version to be displayed, which in this case is ID 2750 and English (United States). On this project, I am working with a front end developer, so I will package up the information I have gained and send it to the development team with the recommendation to implement more content caching on the front end to reduce the number of requests the application makes to the database to display content.

Performance Improvement Recommendations

If there are more queries that seem to need my attention, I will repeat the above process for each until I feel that have a good understanding of all the expensive queries being run on the database on a regular basis. When viewing the execution plans for these queries, I will note any recommendations that SQL Server makes for improving performance and look into implementing them on a test copy of the application and database to see if they really will help improve performance.

It’s important to never implement these changes on the production database without fully testing them. When working with a relational database management system (RDBMS) like SQL Server, I always keep in mind that every index I add to improve read performance has a negative impact on write performance. Before implementing any of these changes, I want to test them and make sure the benefits outweigh the costs.

We can see an example of the kind of recommendations SQL Server might make by using the sample database AdventureWorks2012. The query below will return the names of bike shops and the ID of the sales person for each of these shops:

I can show the execution plan for the query by clicking on the Include Actual Execution Plan icon in the tool bar:

When I run this query and show the execution plan, SQL Server tells me about a missing index that will improve the performance of the query:

If I right click on the missing index statement and select Missing Index Details, SQL Server will open a new tab with more information about the recommend new index and the create statement for this index:

By using the Recent Expensive Queries pane of SQL Server Activity Monitor I can see a close to real-time display of what’s happing in my SQL Server instance. The option to edit query text let me read the SQL statements being run on the databases, and I can dig more into what queries are doing and their impact on the system by looking at their execution plans. I can even get recommendations on missing indexing that may help improve the performance of the queries being run.

In the next part in the series we will go over Dynamic Management Views and how they can help us understand what SQL Server is doing.

If you would like to setup your own copy of the AdventureWorks2012 samples database for testing, I recommend following the instructions here: http://blog.sqlauthority.com/2012/03/15/sql-server-install-samples-database-adventure-works-for-sql-server-2012/

For more on SQL Server Execution Plans: https://technet.microsoft.com/en-us/library/ms178071%28v=sql.105%29.aspx

If you have any questions or tips of your own about SQL Server Activity Monitor and how to discover and address any issues with expensive queries, please feel free to share them in the comments below. If you want to know more about how Diagram can help you with hosting your SQL Server instances, please contact us. Please stay tuned for the next installment in this blog series!