How to check the SQL Server CPU bottleneck

Source: Internet
Author: User

-- One way to detect CPU pressure is to calculate the number of worker processes in the running state,
-- You can obtain this information by executing the following DMV query:

SELECT COUNT(*) AS workers_waiting_for_cpu,t2.scheduler_id
FROM sys.dm_os_workers AS t1, sys.dm_os_schedulers AS t2
WHERE t1.state='RUNNABLE' AND
t1.scheduler_address = t2.scheduler_address AND
t2.scheduler_id<255
GROUP BY t2.scheduler_id

-- You can also run the following query to obtain the time that a worker spends in a running state.

Select sum (signal_wait_time_ms) from SYS. dm_ OS _wait_stats

-- The following is a DMV query, which can be used to find the maximum CPU usage of 10 for each execution,
-- It also lists the SQL statement query plan and the number of times the plan is executed. If a query is high,
-- It can also be adopted if the execution frequency is small.

SELECT TOP 10 
total_worker_time/execution_count AS avg_cpu_cost, plan_handle,execution_count,
(SELECT SUBSTRING(text,statement_start_offset/2+1,
(CASE WHEN statement_end_offset=-1
THEN LEN(CONVERT(NVARCHAR(max),text))*2
ELSE statement_end_offset
END -statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)
) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC

-- The above DMV only displays the total statistics of the currently cached queries
-- In order to find the most frequently run query in the workload, You need to execute the following DMV query.

SELECT TOP 10 total_worker_time ,plan_handle,execution_count,
(SELECT SUBSTRING(text,statement_start_offset/2+1,
(CASE WHEN statement_end_offset=-1
THEN LEN(CONVERT(NVARCHAR(max),text))*2
ELSE statement_end_offset
END -statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)
) AS query_text
FROM sys.dm_exec_query_stats ORDER BY execution_count

-- The time spent by SQL Server in optimizing the query plan can be queried using the following dmv

SELECT * FROM sys.dm_exec_query_optimizer_info WHERE counter='optimizations' 
UNION
SELECT * FROM sys.dm_exec_query_optimizer_info WHERE counter='elapsed time'


SELECT TOP 10 plan_generation_num ,plan_handle,execution_count,
(SELECT SUBSTRING(text,statement_start_offset/2+1,
(CASE WHEN statement_end_offset=-1
THEN LEN(CONVERT(NVARCHAR(max),text))*2
ELSE statement_end_offset
END -statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)
) AS query_text
FROM sys.dm_exec_query_stats
WHERE plan_generation_num>1
ORDER BY execution_count

 
-- Check cache memory

 DBCC memorystatus 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/dz45693/archive/2010/01/27/5260697.aspx

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.