How to find CPU intensive queries

CPU usage is one of the most important metrics for databases. We want to know how much resources are being used by each process. Unfortunately it’s easy to miss because you don’t see it directly on the server itself. But there’s a way to check it – sys.dm_exec... DMV.

The sys.dm_exec(N)_query_stats DMV stores information about cached query plans. If you run a query, it collects data about that query and stores it in the database. You can use this information to identify long running queries.

This DMV keeps some useful statistic info about the queries. All the values are stored into three fields Total, Last and Avg. These values represent the total number of times that the query was executed, the last execution time, and the average amount of time spent executing the query. The Avg value represents the average duration of all executions.

You can also get more detailed information about the query plan cache using the following SQL statement:

SELECT * FROM sys.dm_exec_cached_plans;

There are two types of plans: Compiled Plans and Cached Plans. A compiled plan is created when you create a new query or modify an existing one. It contains the actual query text and parameters. A cached plan is a copy of the compiled plan which has been saved in the plan cache. This type of plan is reused if the same query is run again within a short period of time.

If you have a lot of queries with similar patterns (for example, they all select from the same table), then you should consider creating a view containing these queries. Views are very efficient because they only contain the necessary data needed to execute the query. They do not store any additional information such as statistics.

If your application uses views extensively, then you may need to add indexes to them. Indexes improve performance by allowing the database engine to quickly locate records based on specific criteria. For example, if you have a view that selects from a large table, adding an index to the view will allow the database engine to quickly retrieve rows matching the conditions specified in the WHERE clause.

In addition to improving performance, indexes help prevent certain kinds of errors. For instance, if you try to insert a record into a table without specifying a primary key, the database engine must search through every row in the table to determine which row matches the inserted record. An index allows the database engine to quickly look up the correct row.

Indexes are especially helpful for tables that are frequently updated. When updating a table, the database engine needs to read the entire table before making changes. However, if you have an index on the table, the database engine can skip over the first few rows of the table and start looking at the indexed column instead. This speeds up updates significantly.