常见的高CPU利用率出现几个原因：
- Missing Index
- 统计信息过时
- 非SARG查询
- Implicit Conversions
- Parameter Sniffing
- Non-parameter Ad-hoc Query
- 非必要并行查询
--1.Check the Architecture's Type is or not NUMA (non-uniform memory access)
select
scheduler_id,
cpu_id,
parent_node_id,
status
from sys.dm_os_schedulers;
--2. The memory size of memory clerks. Checking the Buffer Pool Memory(MEMORYCLERK_SQLBUFFERPOOL) size and SQL Queuery Plan(MEMORYCLERK_SQLQUERYPLAN) is very important for tuning the performance.
select
type,
memory_node_id,
virtual_memory_committed_kb,
virtual_memory_reserved_kb,
awe_allocated_kb
from sys.dm_os_memory_clerks
order by virtual_memory_reserved_kb desc;
--3. Check the Size of Current Cache Plan.
select
objtype ,
COUNT(*) AS 'Number of Plans' ,
SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS 'Plan Cache Size (MB)'
FROM sys.dm_exec_cached_plans
Group by objtype;
--4. Check the Type of CPU of SQL Server
select
cpu_count as 'Logical CUP Count',
hyperthread_ratio as 'Hyperthread Radio',
cpu_count /hyperthread_ratio as 'Physical CPU Count',
physical_memory_in_bytes/1048576 as 'Physical Memory (MB)'
from sys.dm_os_sys_info
--5.Check the IO of Datafile or Logfiles.
SELECT DB_NAME(DB_ID()) AS [Database Name] ,
[file_id] ,
num_of_reads ,
num_of_writes ,
num_of_bytes_read ,
num_of_bytes_written ,
CAST(100. * num_of_reads / ( num_of_reads + num_of_writes ) AS DECIMAL(10,
1)) AS [# Reads Pct] ,
CAST(100. * num_of_writes / ( num_of_reads + num_of_writes ) AS DECIMAL(10,
1)) AS [# Write Pct] ,
CAST(100. * num_of_bytes_read / ( num_of_bytes_read
+ num_of_bytes_written ) AS DECIMAL(10,
1)) AS [Read Bytes Pct] ,
CAST(100. * num_of_bytes_written / ( num_of_bytes_read
+ num_of_bytes_written ) AS DECIMAL(10,
1)) AS [Written Bytes Pct]
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL);
--6. Check the top 10 longest waits
SELECT TOP ( 10 )
wait_type ,
waiting_tasks_count ,
( wait_time_ms - signal_wait_time_ms ) AS resource_wait_time ,
max_wait_time_ms ,
CASE waiting_tasks_count
WHEN 0 THEN 0
ELSE wait_time_ms / waiting_tasks_count
END AS avg_wait_time
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%' -- È¥³ý²»Ïà¹ØµÄµÈ´ýÀàÐÍ
AND wait_type NOT LIKE 'XE%'
AND wait_type NOT IN -- È¥³ýϵͳÀàÐÍ
( 'KSOURCE_WAKEUP', 'BROKER_TASK_STOP', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER',
'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE',
'DBMIRROR_EVENTS_QUEUE', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',
'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE',
'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS',
'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH' )
ORDER BY wait_time_ms DESC
--7.Check the parallelism Query
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/SQL Server/2004/07/showplan')
SELECT query_plan AS CompleteQueryPlan ,
n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText ,
n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)')
AS StatementOptimizationLevel ,
n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)')
AS StatementSubTreeCost ,
n.query('.') AS ParallelSubTreeXML ,
ecp.usecounts ,
ecp.size_in_bytes
FROM sys.dm_exec_cached_plans AS ecp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp CROSS APPLY query_plan.nodes
('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')
AS qn ( n )
WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1