How to check the SQL Server CPU bottleneck

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