-- 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