How To Find Most Expensive Queries in SQL Server

One of the first things that we look at to improve performance in systems that have performance problems is the ones that finding queries that use CPU and Disk most. You can identify the queries that use the CPU most and make the necessary improvements.

Top 50 CPU Consuming Queries in SQL Server

The query will return a result set as follows.

All of the values ​​returned as a result of this query are taken from the statistics collected from the moment the sql server service was last started. So if your SQL Server service has been restarted 1 minute ago, you cannot perform a very efficient analysis.

In the query section, shows the parameterized state of the query.

Average CPU Time (in milliseconds) gives the average run time of the query, in milliseconds.

In general, we decide which queries we will improve based on the Average CPU Time (milliseconds). We understand that if the average CPU Time (in milliseconds) of a query that needs to be completed in a few milliseconds is 2 seconds, we need to improve this query.

Sometimes, when we improve a query that has an average CPU Time of 200 milliseconds, we can see that the server’s CPU usage is reduced. In such cases, the query’s Execution Count is usually too much, and if the query’s average CPU Time (milliseconds) is reduced to a few milliseconds, it may have a cumulative impact on the CPU.

Top 50 Disk IO Consuming Queries in SQL Server

In the following query, you can find it sorted by total logical read. You can sort by another value.

You may want to read the following articles.

How To Find Missing Indexes in SQL Server

How To Identify Long Running Queries Using Extended Events“,

How To Identify Long Running Queries Using SQL Server Profiler“,

Running SQL Server With High Performance(High Performance)“,

Index Concept and Performance Effect on SQL Server“,

Statistics Concept and Performance Effect on SQL Server