1 SQL与CPU相关的操作:
A 过多的编译和重编译
B 效率低的查询计划
C 内部查询并行
D 过多的游标操作
E 冗长的SQL Server查询
2 判断CPU的性能系统计数器:
一 PROCESSOR
A PROCESSOR TIME%是否偏高 80%,如果高于80%可以认为是CPU瓶颈.
B PRIVILEGED TIME% 当值偏高时,而且磁盘输出值也偏高甚至随时增高,就要考虑是否是磁盘子系统性能不佳造成的PRIVILEGED TIME%偏高.
二 SYSTEM
A PROCESSOR QUEUE LENGTH 如果连续监视的程序中,这个值长时间在2以上,表示处理器有可能面临瓶颈(一个CPU).
3 判断CPU的性能DMV视图:
A 列出所有调度器并产生等待运行的任务数量
select scheduler_id,current_tasks_count,runnable_tasks_count
from
sys.dm_os_schedulers
where
scheduler_id < 255
B 最耗CPU的批或者过程高级别视图1
select top 50
sum(qs.total_worker_time) as total_cpu_time,sum(qs.execution_count) as total_execution_count,count(*) as number_of_statements,qs.plan_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc
最耗CPU的批或者过程高级别视图2(最多CPU时间的执行计划)
(1)
select Top 50
total_worker_time/1000 as [总耗CPU时间],
execution_count [执行次数],
qs.total_worker_time/qs.execution_count/1000 as [平均耗CPU时间],
substring(qt.text,qs.statement_start_offset/2+1,
(case when qs.statement_end_offset = -1
then datalengeh(qt.text)
else qs.statement_end_offset end -qs.statement_statement_start_offset)/2+1) as [使用CPU的语句],qt.text [完整语句],
qt.objectid,object_name(qt.objectid,qt.dbid) objecrName
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
order by total_worker_time
(2)
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
(3)
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 DESC
-------------------------------------------------------------
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
4 CPU在企业管理器里的相关设置:
A 处理器 对应每个CPU的设置状况应是:"IO相关"与"处理器相关"二者都不选,或二者选一.
B 最大工作线程 一般默认为0 由MSSQL来自行调节
默认的设定
32位机器
小于4个处理器 256
8个处理器 288
16个处理器 352
32个处理器 480
64位机器
小于4个处理器 512
8个处理器 576
16个处理器 704
32个处理器 960
C 线程和纤程
如果内核模式占用了整体性能很大的比例才需要开启纤程.可以通过
SYSTEM
CONTEXT SWITCH/SEC来判别
(在做调整前也要主意是不是内存不足引起的)
D 并行阀值与并行度的设置
并行阀值当超过并行阀值就会启用并行度.
? 对于使用超过八个处理器的服务器使用以上的配置： MAXDOP = 8。
? 服务器具有八个或更少处理器，使用以下配置 N 等于处理器数的位置： MAXDOP = 0 To N。
? 对于具有 NUMA 配置的服务器， MAXDOP 不应超过 CPU 分配给每个 NUMA 节点的数。
? 对于具有超线程已启用的服务器， MAXDOP 值不应超过物理处理器数的乘积。
可以通过计数器 SQL Server SQL Statistics Batch Requests/sec
(每秒收到的 Transact-SQL 命令批数。 这一统计信息受所有约束（如 I/O、用户数、高速缓存大小、请求的复杂程度等）影响。 批处理请求数值高意味着吞吐量很好。)
下一篇文章：http://www.windbi.com/showtopic-1911.aspx
