Finding Top 10 Queries That Use CPU in MS SQL Server
This essay will present a method of identifying most commonly executed CPU intensive SQL queries that are 10 in numberServer. It is able to do this via the use of Dynamic Management Views (DMVs) such as sys.dm_exec_query_plan_stats.We can read query plan using, sys.dm_exec_query_plan, sys.dm_exec_sql_text and we can read the sql text using sys.dm_exec_sql_text.statistics, the implementation of plans and SQL text to determine the operations that would require resources. This troubleshooting and optimization of performance monitoring necessitates information.
In order to analyze the performance and isolate operations that require a lot of resources, one might often need to findqueries that are most CPU-consuming. A helpful Dynamic Management The SQL Server offers SQL Server offers Classifiable extension It is helpful to have several extension features of SQL Server classifiable internally extendable It is useful that several extension offerings of the SQL Server be internally extendableView (DMV) named sys.dm_exec_query_plan_stats, it includes the information about the performance.cpu, memory, and I/O usage information on queries that are in the cache.Using this DMV we can join it with sys.dm_exec_query_plan and sys.dm_exec_sql_text.view the statistics of the query as well as the execution plan and the SQL text of the query too.This enables us in troubleshooting costly queries.
The following is a script that gives Top 10 CPU-intensive queries that are in the cache:
- SELECT TOP 10
- qs.total_worker_time AS TotalCPU,
- qs.execution_count AS ExecutionCount,
- qs.total_worker_time / qs.execution_count AS AvgCPUPerExecution,
- qs.total_elapsed_time / qs.execution_count AS AvgElapsedTime,
- qs.total_elapsed_time AS TotalElapsedTime,
- qt.text AS QueryText,
- qp.query_plan AS QueryPlan
- FROM
- sys.dm_exec_query_stats AS qs
- CROSS APPLY
- sys.dm_exec_sql_text(qs.sql_handle) AS qt
- CROSS APPLY
- sys.dm_exec_query_plan(qs.plan_handle) AS qp
- ORDER BY
- qs.total_worker_time DESC;
Exposition of the Script:
1. SELECT TOP 10: This statement restricts the result to top 10 SQL calls that utilize the CPU.
2. qs.total_worker_time AS TotalCPU: It picks the aggregate CPU time (in microseconds)
devoured by the question. total_worker_time is defined as the sum of CPU time.
was spent in performing the query.
3. qs.execution_count AS ExecutionCount: It will select the number of times the query is executed.
been executed.
4. qs.total_worker_time / qs.execution_count AS AvgCPUPerExecution: It computes
the mean CPU time on executing the query. This can be useful to comprehend the
Unit cost of CPU.
5. qs.execution_count average qs.total_elapsed_time AS AvgElapsedTime: This obtains:
mean time in seconds spent executing; the query. Wait time is not excluded in elapsed time.
This measurement can also be used to determine the queries that are stranded with resources.
6. qs.total_elapsed_time AS TotalElapsedTime: This chooses the total elapsed time (in
The query used almost 3 microseconds.
7. querytext AS QueryText: This returns the plain data of the SQL of the query. This enables you to
look to see what the query is doing.
8. qp.query_plan AS QueryPlan: It chooses the query execution plan in XML
format. The execution plan gives in-depth details on the way SQL Server is
running the query and the operators that will be employed, the estimated costs and the data
access methods.
9. FROM sys.dm_exec_query_stats AS qs: This denotes the main DMV this should be based on
for query statistics, view sys.dm_exec_query_stats contains aggregated performance.
cache-query-plan statistics.
10. CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt: This matches with the
The sys.dm_exec_query_stats DMV and the sys.dm_exec_sql_text DMV can be used together with extended stored procedures like the stored procedure below to output information into a table.
sql_handle. sql_handle deals with a unique text of the SQL of the query. CROSS
using APPLY is a necessity since, sys.dm_exec_sql_text is a table-valued function.
11. CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) AS qp: This joins the
The sys.dm_exec_query_stats DMV and the sys.dm_exec_query_plan DMV in conjunction with one another
plan_handle. The plan_handle is a query plan cached identifier.
CROSS APPLY is applied on the basis of the fact that sys.dm_exec_query_plan is a table-valued function.
12. ORDER BY qs.total_worker_time desc: Order by in reverse order by the qs. total_worker_time
total CPU time hence the queries which have used the most CPU appear at the top.
at the top of its list.
1. SELECT TOP 10: This statement restricts the result to top 10 SQL calls that utilize the CPU.
2. qs.total_worker_time AS TotalCPU: It picks the aggregate CPU time (in microseconds)
devoured by the question. total_worker_time is defined as the sum of CPU time.
was spent in performing the query.
3. qs.execution_count AS ExecutionCount: It will select the number of times the query is executed.
been executed.
4. qs.total_worker_time / qs.execution_count AS AvgCPUPerExecution: It computes
the mean CPU time on executing the query. This can be useful to comprehend the
Unit cost of CPU.
5. qs.execution_count average qs.total_elapsed_time AS AvgElapsedTime: This obtains:
mean time in seconds spent executing; the query. Wait time is not excluded in elapsed time.
This measurement can also be used to determine the queries that are stranded with resources.
6. qs.total_elapsed_time AS TotalElapsedTime: This chooses the total elapsed time (in
The query used almost 3 microseconds.
7. querytext AS QueryText: This returns the plain data of the SQL of the query. This enables you to
look to see what the query is doing.
8. qp.query_plan AS QueryPlan: It chooses the query execution plan in XML
format. The execution plan gives in-depth details on the way SQL Server is
running the query and the operators that will be employed, the estimated costs and the data
access methods.
9. FROM sys.dm_exec_query_stats AS qs: This denotes the main DMV this should be based on
for query statistics, view sys.dm_exec_query_stats contains aggregated performance.
cache-query-plan statistics.
10. CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt: This matches with the
The sys.dm_exec_query_stats DMV and the sys.dm_exec_sql_text DMV can be used together with extended stored procedures like the stored procedure below to output information into a table.
sql_handle. sql_handle deals with a unique text of the SQL of the query. CROSS
using APPLY is a necessity since, sys.dm_exec_sql_text is a table-valued function.
11. CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) AS qp: This joins the
The sys.dm_exec_query_stats DMV and the sys.dm_exec_query_plan DMV in conjunction with one another
plan_handle. The plan_handle is a query plan cached identifier.
CROSS APPLY is applied on the basis of the fact that sys.dm_exec_query_plan is a table-valued function.
12. ORDER BY qs.total_worker_time desc: Order by in reverse order by the qs. total_worker_time
total CPU time hence the queries which have used the most CPU appear at the top.
at the top of its list.
How to Use the Script:
1. Run the Script: Run this script into SQL Server Management Studio (SSMS) or any other program.SQL Server client.2. Interpret the Results: Scrutinize the result to determine the most popular queriesTotalCPU values.3. Research costly queries: Examine all of the most CPU-intensive queries:the QueryText and the QueryPlan to get a feel of what the query is up to and how it isbeing executed.4. Queries Optimization: On the basis of analysis, the optimization of the following queries would be taken into consideration:techniques.
Considerations:
• Cache Dynamics: The outcome is an indication of the traffic in the present cache. The cache isdynamic hence the outcomes will change with time.• Server Load: The average system load may influence the demand on the CPU of the queriesthe server.• Permissions: In order to run this script, you require the VIEW SERVER STATE permission.
Conclusion:
This script is useful for DBAs and developers who want to quickly identify and optimize the most CPU-intensive queries in SQL Server. Regular use of this report can help improve database performance and reduce system load.
Post Comment
Your email address will not be published. Required fields are marked *