Tools to Improve Slow SQL Queries in SQL Server

20th August 2019

It can be frustrating when the cause of your slow web application is down to the queries it makes to the database. This can be down to many reasons, including:

  • Computer hardware
  • Large volumes of traffic querying the database simultaneously
  • Too many joins with other tables
  • Querying against a table that has lots of records

There are many things that you can do to improve database performance, but you first need to know which queries are guilty for your website's sluggish performance.

Thankfully, SQL Server Management Studio (SSMS) has a number of free tools to let you do just that.

SQL Server Profiler

This is a great tool that you can use to see what queries are being performed against your database. To access it in SSMS, you can go to the top menu and navigate to Tools -> SQL Server Profiler.

This tool allows you to connect to any SQL Server, and trace any activity on that server. You can set up different filter properties depending on your requirements. For example, if you are only looking out for queries that are related to a certain table, or looking for a query that takes longer than 10 seconds, that can be done.

SQL Server Profiler Trace Properties - Edit Filter

Once you have set up your requirements, you can run the profiler and it will report all the activity based on your requirements.

SQL Server Profiler Log

The information that the profiler will log includes the application name, the amount of CPU that the task used to perform and how long it took for the query to run. As a result of this, you can look at the CPU and query time to help improve the performance. Consequently, the higher these two are, the slower your web application is going to be. That will give you a starting point as to the area you need to address.

Execution Plan

The execution plan can be attached to a query in SSMS. By using the top menu and navigating to Query -> Include Actual Execution Plan, it will show a diagram of the work your query has to do to get it's results.

By opening a new query window and writing a simple SELECT statement, we will see what happens:

SQL Server Query Execution Plan

From the diagram above, we have done a query with an inner join against two tables. The execution plan shows the work the database has to do to get it's results. The above example shows that the work done to try and match the primary and foreign keys to complete the join.

Activity Monitor

If you wish to monitor current tasks and processes, this is great. This can be found by right clicking on the database you have connected to and selecting Activity Monitor.

SQL Server Activity Monitor

Are you having troubles with a slow SQL server in general? Well this tool will help you show what processes are currently being run on the server. It will also tell you how long they've been running for and what is their current state.

About these Tools

There are many paid tools out there that will help you get to the bottom of your web application's sluggish performance. But, from my experience, the connection between a web app and database is high on the offending list.

Before you shell out money on tools, it's worth taking a look at SQL Server's free tools. They have the potential to sort out the performance of your web app without paying anything!

About the author

David Grace

David Grace

Senior .NET web developer | ASP.NET Core | C# | Software developer

Free .NET videos

  • Do you want to watch free videos featuring .NET 7 new features?
  • How about what's new in C# 11?
  • Or a recap on the SOLID principles?
Watch our .NET videos