SQL Server 性能诊断（3）- CPU1. 概述 SQL Server为了提高其并发处理能力，在Windows进

1. 概述

SQL Server为了提高其并发处理能力，在Windows进程调度的基础上，又开发了一套自己的任务调度机制。SQL Server作为一个应用程序，为实现该机制，又抽象出了以下的一般由操作系统代为管理的功能：

任务调度管理子系统。
内存管理。
错误、异常处理机制。
死锁侦测和解决机制。
运行第三方代码（Dll、Extended SP等）机制。

这部分的管理职能组件叫做SQLOS（SQL Operating System）。内存管理和任务调度管理是SQLOS的两大核心内容。

既然Windows已经有了自己的一套任务调度机制，为什么SQL Server还要在开发一套？解答这个问题之前，先看一下Windows的任务调度机制。

Windows NT 4.0及以后版本，都是多线程抢占式操作系统（Preemptive Multi-Threaded Operating System）。在这种操作系统下，任务调度有以下特点。

“抢占式”意味着完全由操作系统决定哪个线程在什么时间运行，基于各个线程的优先级（Priority）和时间片（Quantum）。当一个线程时间片用完，或者有一个优先级更高的线程在等待CPU资源时，Windows会打断当前正在运行的线程，强迫其让出CPU资源。
以线程作为任务调度单位（类似的，在内存管理中Windows以进程作为内存管理单位）
任务调度完全由Windows控制。一般一个应用程序，无法保证其线程能够永远运行，不会被Windows打断（Interrupted）。
对于早期版本的Windows的线程间的同步，Windows没有提供类似读者-写者锁类似的高并发度（或者说轻量级）的同步机制，从而导致了最初的SQL Server开发人员为了获得高并发性，不得不重新设计了一种同步原语，这就是闩锁（Latch）。

对于SQL Server来讲，除了从DAC（Dedicated Administrator Connection）过来的连接，其他用户连接对SQL Server都是同等重要的，而这样的连接在同一个时间点，可能会有几百甚至上千。如果SQL Server完全依赖Windows的任务调度，最简单的方法就是：

每一个连接对应一个线程，如果某个任务需要并行执行（Parallel Execution），那么这个连接就要对应多个线程。
绝大部分线程在SQL Server里的优先级是一样的，真正在Windows里运行的优先级也是一样的。
由Windows以时间片的方式调度这些线程的执行。

这样的方法对于并发度不是太高的应用来讲，没什么大问题。但是对于SQL Server来讲，显然是有很大问题的。

用户连接生命周期的空闲时间：在用户连接的生命周期中，大部分时间处于空闲状态。连接在接收到结果集后需要客户端处理，处理完后才会发出下一条T-SQL指令。因此，连接之间通常会有很多空闲时间。如果每个连接占用一个线程，会导致SQL Server中有大量闲置线程，增加维护负担，影响系统可扩展性。
线程数量与CPU数量不匹配：对于繁忙的SQL Server，系统中同时运行的线程数通常多于CPU核心数。这会导致频繁的线程切换（Context Switch），而线程切换是一个高开销的操作。频繁的Context Switch会消耗大量资源，导致系统效率低下。
Windows线程调度机制的不足：Windows线程调度是基于优先级和时间片的，无法根据SQL Server的实际运行状态来优化调度。这可能导致不适当的中断点，影响SQL Server的线程执行效率。

SQLOS的任务调度机制特性如下：

只有要运行任务的连接才会被分配线程。处于空闲状态的连接，在SQL Server里会以一组数据结构表示，不会占用线程资源。这就会大大降低SQL Server进程需要的线程数目。
对于每一个CPU，SQL Server内部会有一个调度（Scheduler），由这个scheduler决定在某个时间点，到底是哪个SQL Server线程去运行。所以在Windows层面，每个CPU最多只会对应一个处于运行状态的线程。这样会大大降低Windows层面的Context Switch。

SQLOS重要术语及概念介绍：

Schedulers

对于每个逻辑CPU，SQL Server会有一个Scheduler与之对应，在SQL Server层面上代表CPU对象。只有拿到scheduler所有权的任务（Worker）才能在这个逻辑CPU上运行。

Scheduler的管理遵守以下规则：

每个scheduler上的最大Worker数目等于SQL Server的最大线程数除以scheduler的数目。

Workers Per Scheduler Limit = (Max Worker Threads / Online Schedulers)

在同一个时间点，只能有一个拥有Scheduler的Worker处于运行状态，其他Worker都必须处于等待状态。这样能够降低每个逻辑CPU上的处于正在运行状态的线程数目，降低Context Switch，提高可扩展性。
Scheduler是SQL Server的一个逻辑概念，它不与物理CPU相绑定。也就是说，一个scheduler可以被Windows安排一会儿在这个CPU上，一会儿在那个CPU上。但是，如果在sp_configure里设置了CPU affinity mask，那么scheduler就会固定在某个特定的CPU上。

稍微再解释一下，“每个 Scheduler 上的最大 Worker 数目等于 SQL Server 的最大线程数除以 Scheduler 的数目。” 这句话的意思是，SQL Server 在启动时会为每个 CPU 核心分配一个 Scheduler，或为每个处理器的逻辑核心分配一个 Scheduler。然后，系统会根据配置的最大线程数，将这些线程分配到不同的 Scheduler 上。假设 SQL Server 配置的最大线程数是 1024 个，且系统有 8 个 Scheduler（例如，8 核 CPU 或 8 个逻辑处理器），那么每个 Scheduler 上最多会有的 Worker 数目是：

也就是说，每个 Scheduler 最多可以调度 128 个 Worker 线程来执行任务。

Worker

每个Worker跟一个线程（或纤程Fiber）相对应，是SQL Server任务的执行单位。SQL Server不直接调度线程/纤程，而是调度Worker，使得SQL Server能够控制任务调度。

Worker的管理遵守以下规则:

每个Worker会固定代表一个线程（或纤程），并且和一个Scheduler相绑定。如果Scheduler是固定在某个CPU上的（通过设置CPU affinity mask），那么Worker也会固定在某个CPU上。
每个scheduler有Worker的上限值，并且可以根据SQL Server工作负荷创建或释放Worker。
每次Worker都会去运行一个完整的任务（Task）。在任务做完之前不会退出Scheduler只有在当前有新任务要运行，但没有空闲的Worker，并且当前Scheduler上的Worker数目没有超过最大Worker数目时，才会创建新的Worker。
某个Worker空闲超过15分钟，Scheduler可能会删除这个Worker，以及其对应的线程。当SQL Server遇到内存压力的时候，也会大量删除处于空闲状态的Worker，以节省Multi-page的内存开销。

各种CPU与SQL Server 2012版本组合自动配置的最大工作线程数

Task

在Worker上运行的最小任务单元。最简单的Task就是一个简单Batch。例如：

Select @@servername
Go
Select getdate()
Go

那么这两个Batch就分别是两个Task。SQL Server会先分配给第一个Batch（select @@version）一个Worker，将结果返回给客户端，再分配给第二个Batch （select getdate()）一个Worker。这两个Worker可能是不同的Worker，甚至在不同的scheduler上。

只要一个Task开始运行，它就不会从这个Worker上被移出。例如，如果一个SELECT语句被其他连接阻塞住，Worker就不能继续运行，只能进入等待状态。但是这个SELECT task不会将这个Worker释放，让它做其他任务。所以结果是，这个Worker所对应的线程会进入等待状态。

Yielding

SQLOS的任务调度算法的核心，就是所有在逻辑Scheduler上运行的Worker都是非抢占式的（non-preemptive）。Worker会始终在Scheduler上运行，直到它运行结束，或者主动将Scheduler让出给其他Worker为止。这个“让出”Scheduler的动作，我们叫Yielding。

每个Scheduler都会有一个Runnable列表，所有等待CPU运行的Worker都会在这个列表里排队，以先进先出的算法，等待SQL Server分配给它Scheduler运行。

SQL Server定义了很多Yielding的规则，约束一个Task在Scheduler运行的时间。如果Task比较复杂，不能很快做完，会保证Task在合适的时间点做Yielding，不至于占用Scheduler太多时间。常见的时间点是：

当worker每次要去读数据页的时候，SQL Server会检查这个Worker已经在Scheduler上运行了多久，如果已经超过了4 ms，就做Yielding。
每做64 KB的结果集的排序，就会做一次Yielding。
在做语句Compile的过程中（这个过程比较占CPU资源），经常会有Yielding。
如果客户端不能及时把结果集取走，Worker就会做Yielding。
一个Batch里的每一句话做完，都会做一次Yielding。

所以正常来讲，哪怕一个Task要做很久，它使用的Worker是会经常做Yielding的，不会长时间地占着CPU不放。如果在一个Scheduler上同时有很多Worker要运行，SQL Server通过Worker自动Yielding的方式调度并发运行。

在每个Scheduler里，会有若干个Worker，对应于每个线程。在客户端发过来请求之后，SQL Server会将其分解成一个或多个Task。根据每个Scheduler的繁忙程度，Task会被分配到某个Scheduler上面。如果Scheduler里有空闲的Worker，Task就会被分配到某个Worker上。如果没有，Scheduler会创建新的Worker，供Task使用。如果Scheduler里的Worker已经到了它的上限值，而它们都有Task要运行，那么新的Task只好进入等待Worker的状态。

查询当前每个Scheduler的状态

SELECT
    scheduler_id,
    cpu_id,
    parent_node_id,
    current_tasks_count,
    runnable_tasks_count,
    current_workers_count,
    active_workers_count,
    work_queue_count
  FROM sys.dm_os_schedulers;

sys.dm_os_schedulers的字段定义

非抢占虽然适用于SQL Server，但是也存在一些问题：

在非抢占式调度机制下，任务运行不会被系统强制中断，除非任务主动 Yielding 或完成。如果某任务运行时间过长且没有 Yielding，可能长时间占用 CPU，导致其他任务无法执行，从而影响系统性能。严重情况下，可能导致整个 SQL Server 无响应。
任务调度问题可能来自 SQL Server 代码质量、意外资源瓶颈或硬件故障等多种原因。需要高效机制及时发现并记录问题，以避免系统陷入更严重的性能或可用性问题。
检测到调度器异常时，SQL Server 生成的 Mini-dump 文件包含大量底层状态信息，分析这些信息对问题定位至关重要，但难度较高。Mini-dump 分析需要深入了解 SQL Server 内核设计，非专业人员难以有效解读。
即使问题较轻，单个任务长期占用 CPU 也会对调度器的任务分发效率造成影响，削弱 SQL Server 的整体性能表现。对高并发环境，调度器问题可能带来更显著的性能瓶颈或宕机风险。

可能存在17883/17884/17888错误，详情见错误日志分析。

2. 错误日志分析

17883/17884/17888，现象就是SQL Server hang住。

17883：
Process 51:0:0 (0xdbc) Worker 0x036BA0E8 appears to be non-yielding on Scheduler 1. Thread
creation time: 12764721496978. Approx Thread CPU Used: kernel 15 ms, user 171 ms. Process
Utilization 0%. System Idle 99%. Interval: 325602683 ms.
17884：
New queries assigned to process on Node 0 have not been picked up by a worker thread in
the last 60 seconds. Blocking or long-running queries can contribute to this condition,
and may degrade client response time. Use the "max worker threads" configuration option
to increase number of allowable threads, or optimize current running queries. SQL Process
Utilization: 0%. System Idle: 99%.
17888:
All schedulers on Node 1 appear deadlocked due to a large number of worker threads waiting
on LCK_M_S. Process Utilization 0%.

17883——某个scheduler疑似有问题，代表一个Task运行超过60秒都没有做Yeilding。从警告信息里报出的进程使用率（Process Utilization）、系统空闲率（System Idle）、Kernel和User模式时间的值，我们可以对问题的性质大概有个了解。

- 如果User mode的时间很长，很有可能当前线程所运行的代码进入了一个循环，很长时间都出不来。这种问题在SQL Server的代码里不应该出现。如果出现，一般怀疑是当前版本SQL Server的代码哪里写得不够优化。建议升级SQL Server到最新的服务包版本，以避免一切已知的问题。
- 如果Kernel mode时间很长，说明当前的线程主要都是在运行操作系统管理的核心态功能。如果要找到问题的根本原因，可能要作Kernel Mode的Debug。怀疑的方向是某个驱动程序或者操作系统本身。建议升级操作系统的补丁包和有关驱动的版本。
- 如果User mode和Kernel mode都不高，线程一般是正在等某个API的返回，例如WaitForSingleObject、Sleep、WriteFile和ReadFile。这些函数按道理应该很快返回，所以SQL Server就没有设计在这里做Yeilding。但是当时却因为某种未知原因而长时间地没有返回。其中I/O问题导致的WriteFile和ReadFile长时间不返回，是17883的一个最常见的原因。这时候往往伴随着系统I/O问题，管理员需要检查一下各个磁盘的吞吐量是否正常。
- 如果系统空闲率（System Idle%）和进程使用率（Process Utilization%）都很低，很可能是由于SQL Server进程以外的其他应用或操作系统本身产生了CPU 100%的现象，使得SQL Server拿不到CPU资源去运行线程，进而导致了Task没有及时做完。这个只要观察一下性能监视器里的和CPU有关的计数器的值，就能确认。

17884和17888——所有Schedulers都疑似有问题，Scheduler本身已经成为一个会被别人等待的资源。想象如下场景：

- 一个 SPID 开启事务后，在表上申请了 X 锁，但事务未提交，锁被持有，连接进入空闲状态，线程被释放。
- 大量用户尝试读取该表，因无法获取锁，相关任务被阻塞，线程进入等待状态。
- 随着请求增多，更多线程被占用，最终所有线程都被阻塞等待锁资源。
- 阻塞源头的 SPID 试图提交事务（Commit），但因没有可用线程执行提交操作，其任务也进入等待线程状态。
- 结果：SQL Server 进入死锁状态，大量线程被阻塞，而阻塞源头无法释放资源，导致系统停滞。

由于这种死锁资源不单单是锁资源，SQL Server传统的死锁监测机制无法起作用。在SQL Server的任务调度监测机制里，也会对这种情况进行检查。如果SQL Server发现每个scheduler上都没有thread能够有进展，就会报告17884错误。

New queries assigned to process on Node 0 have not been picked up by a worker thread in
the last 60 seconds. Blocking or long-running queries can contribute to this condition,
and may degrade client response time. Use the "max worker threads" configuration option
to increase number of allowable threads, or optimize current running queries. SQL Process
Utilization: 0%. System Idle: 99%.

如果SQL Server发现50%以上的Thread等待的都是类似的资源，例如锁、网络等，就会报告17888错误。

All schedulers on Node 1 appear deadlocked due to a large number of worker threads waiting
on LCK_M_S. Process Utilization 0%.

总结常见的17884/17888产生原因有：

所有的Scheduler都遇到了17883问题。
所有的Worker都被某个关键的资源所阻塞。
所有的Worker都在运行一个很长时间才能返回的语句。

3. CPU使用率100%分析

如果SQL Server错误日志里没有报告17883/17884这一类的错误，但是SQL Server的CPU很高，那一般都是因为工作负载太高导致的。使SQL Server服务器CPU升高的操作如下：

编译和重编译
排序（Sort）和聚合计算（Aggregation）
表格连接（Join）操作

配置相关：

Priority Boost
Affinity Mask
Lightweight Pooling
Max Degree of Parallelism
Cost Threshold of Parallelism
Max Worker Threads

处理CPU 100%问题的思路可如下。

确定服务器CPU使用率到底是多少，其中多少是SQL Server贡献的

检查服务器的CPU使用情况

Processor: % Processor Time
Processor: % Privileged Time (Kernel Mode)
Processor: % User Time (User Mode)
System: Processor queue length
Context switches/sec

检查每个进程的CPU使用情况

Process: % Processor time
Process: % Privileged time
Process: % User time

2. 确定当时SQL Server是否工作正常，看有没有17883/17884之类的问题发生，有没有访问越界（Access Violation）之类的严重问题发生 3. 找出CPU 100%的时候SQL Server里正在运行的最耗CPU资源的语句，对它们进行优化

1. SQL Trace
2. 动态性能视图

-- 使用CPU最多的前50
select
    highest_cpu_queries.*,q.dbid,
    q.objectid, q.number, q.encrypted, q.[text]
from
    (select top 50 qs.*
    from sys.dm_exec_query_stats qs
    order by qs.total_worker_time desc) as highest_cpu_queries
    cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc
go

-- 经常被重新编译的存储过程
select top 25 sql_text.text, sql_handle, plan_generation_num,  execution_count,
    dbid,  objectid
from sys.dm_exec_query_stats a
    cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where plan_generation_num >1
order by plan_generation_num desc
go

4. SSMS提供的性能报表 5. 降低系统负载，或者升级硬件

4. 常用分析SQL

4.1. OLTP

4.1.1. 数据库设计

规则 1：经常运行的语句超过 4 个表格做 Join

- 阈值：>4 张表
- 检查目标：Sys.dm_exec_sql_text、Sys.dm_exec_cached_plans
- 问题描述：如果经常运行的语句涉及多表 Join，建议优化数据库设计，降低数据范式级别，增加冗余字段，用空间换取数据库性能。

规则 2：经常更新的表格有超过 3 个索引

- 阈值：>3 个索引
- 检查目标：Sys.indexes、sys.dm_db_index_operational_stats
- 问题描述：索引过多会影响表的更新效率。

规则 3：语句会做大量 I/O（Table Scans 和 Range Scans）

- 阈值：>1
- 检查目标：
  a. 性能计数器：SQLServer:Access Methods - Full Scans/sec 和 Range Scans/sec 比较高
  b. Sys.dm_exec_query_stats 显示语句大量 I/O 操作
- 问题描述：语句缺少合适的索引，导致大量 I/O 操作。

规则 4：未被使用的索引

- 检查目标：所有未被使用的索引可在 Sys.dm_db_index_usage_stats DMV 中查出
- 问题描述：避免定义未使用的索引，减少 SQL Server 的维护负担。

SQL语句如下：

-- 返回最经常运行的100条语句
SELECT TOP 100
          cp.cacheobjtype
          ,cp.usecounts
          ,cp.size_in_bytes
          ,qs.statement_start_offset
          ,qs.statement_end_offset
          ,qt.dbid
          ,qt.objectid
          ,SUBSTRING(qt.text,qs.statement_start_offset/2,
              (case when qs.statement_end_offset = -1
              then len(convert(nvarchar(max), qt.text)) * 2
              else qs.statement_end_offset end -qs.statement_start_offset)/2)
          as statement
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
inner join sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
where cp.plan_handle=qs.plan_handle
and cp.usecounts>4
ORDER BY [dbid],[Usecounts] DESC

-- 返回最经常被修改的100个索引
-- 通过它们的Database_id、object_id、index_id和partition_number
-- 可以找到它们是哪个数据库上的哪个索引
SELECT top 100 *
FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL)
order by leaf_insert_count+leaf_delete_count+leaf_update_count desc
GO

-- 返回做I/O数目最多的50条语句及它们的执行计划
select top 50
    (total_logical_reads/execution_count) as avg_logical_reads,
    (total_logical_writes/execution_count) as avg_logical_writes,
    (total_physical_reads/execution_count) as avg_phys_reads,
Execution_count,
    statement_start_offset as stmt_start_offset, statement_end_offset as stmt_end_offset,
substring(sql_text.text, (statement_start_offset/2),
case
when (statement_end_offset -statement_start_offset)/2 <=0 then 64000
else (statement_end_offset -statement_start_offset)/2
end) as exec_statement,
sql_text.text,
plan_text.*
from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
cross apply sys.dm_exec_query_plan(plan_handle) as plan_text
order by
(total_logical_reads + total_logical_writes) /Execution_count Desc
go

-- 检查未被使用的索引
select * from Sys.dm_db_index_usage_stats;

4.1.2. CPU

规则 1：Signal Waits 超过 25%

- 阈值：>25%
- 检查目标：Sys.dm_os_wait_stats
- 问题描述：Signal Waits 表示等待 CPU 资源的时间占总等待时间的比例。如果超过 25%，说明 CPU 资源紧张。

规则 2：执行计划重用率低于 90%

- 阈值：<90%
- 检查目标：性能计数器 SQLServer:Statistics
- 问题描述：OLTP 系统的核心 SQL 语句，执行计划重用率必须高于 95%。重用率低会导致性能问题。

规则 3：并行运行的 Cxpacket 等待状态超过 5%

- 阈值：>5%
- 检查目标：Sys.dm_os_wait_stats
- 问题描述：并行执行意味着 SQL Server 需要处理较大的语句，通常是由于缺少索引或不合理的查询条件。对于 OLTP 系统，并行操作影响响应速度，因此不推荐。

SQL语句如下：

-- 计算signal wait占整wait时间的百分比
select convert(numeric(5,4),sum(signal_wait_time_ms)/sum(wait_time_ms))
from Sys.dm_os_wait_stats

-- 计算执行计划重用率
性能计数对象SQLServer:SQL Statistics下面有几个计数器，可以计算出大致的执行计划重用率。计算方法是：
Initial Compilations = SQL Compilations/sec - SQL Re-Compilations/sec
执行计划重用率= （Batch requests/sec - Initial Compilations/sec）/ Batch requests/sec

-- 计算'Cxpacket'占整wait时间的百分比
declare @Cxpacket bigint
declare @Sumwaits bigint
select @Cxpacket = wait_time_ms
from Sys.dm_os_wait_stats
where wait_type = 'Cxpacket'
select @Sumwaits = sum(wait_time_ms)
from Sys.dm_os_wait_stats
select convert(numeric(5,4),@Cxpacket/@Sumwaits)

4.1.3. 内存

规则 1：Page Life Expectancy 小于 300 秒

- 阈值：<300 sec
- 检查目标：SQLServer:Buffer Manager、SQLServer:Buffer Nodes
- 问题描述：OLTP 系统的操作较简单，不应频繁访问大量数据。如果数据页无法长时间缓存在内存里，说明可能存在索引不合理的问题，性能会受到影响。

规则 2：Page Life Expectancy 经常会下降 50%

- 阈值：下降 50%
- 检查目标：SQL Server Buffer Manager
- 问题描述：与规则 1 类似，Page Life Expectancy 的大幅下降说明内存压力过大，可能导致性能问题。

规则 3：Memory Grants Pending 大于 1

- 阈值：>1
- 检查目标：SQL Server Memory Manager
- 问题描述：等待内存分配的用户数大于 1 时，表明系统存在内存压力，需要进一步分析原因。

规则 4：SQL Cache Hit Ratio 小于 90%

- 阈值：<90%
- 检查目标：SQLServer:Plan Cache
- 问题描述：如果 SQL Cache 命中率持续低于 90%，通常说明系统内存压力较大，查询计划无法有效复用。

4.1.4. I/O

规则 1: 平均磁盘读取时间检查

- 性能计数器: Average Disk sec/Read
- 检查目标: 如果没有 I/O 压力的情况下，读操作应在 4-8ms 内完成。
- 问题描述: 当性能计数器 Physical Disk: Average Disk sec/Read 的值 >20ms 时，可能存在磁盘读取性能问题。

规则 2: 平均磁盘写入时间检查

- 性能计数器: Average Disk sec/Write
- 检查目标: 连续写入操作应在 1ms 内完成。
- 问题描述: 当性能计数器 Physical Disk: Average Disk sec/Write 的值 >20ms 时，日志文件等写入操作可能出现性能瓶颈。

规则 3: 表扫描检查

- 性能计数器: SQLServer:Access Methods - Table Scans Full Scans/sec 和 Range Scans/sec
- 检查目标: 检查是否缺少合适的索引。
- 问题描述: 当 Table Scans > 1 且 Full Scans/sec 和 Range Scans/sec 数值较高时，可能存在未优化的查询导致全表扫描。

规则 4: I/O 等待状态检查

- 检查目标: 排查 I/O 等待事件。
- 问题描述:
- 使用以下查询查看等待状态：

SELECT TOP 2 wait_type, wait_time_ms  
FROM sys.dm_os_wait_stats  
ORDER BY wait_time_ms DESC;

如果等待状态为 ASYNC_IO_COMPLETION, LOGMGR, WRITELOG, 或 PAGEIOLATCH_X，可能说明存在磁盘 I/O 等待问题。

4.1.5. 阻塞

规则 1: 阻塞发生频率检查

- 性能计数器: sys.dm_db_index_operational_stats
- 检查目标: 阻塞发生频率应低于 2% 。
- 问题描述: 如果阻塞发生频率 >2% ，需要重点关注阻塞问题。建议通过查询 sys.dm_db_index_operational_stats 。

规则 2: 阻塞事件报告

- 配置项: sp_configure 的 blocked process threshold
- 检查目标: 自动报告阻塞时间超过 30秒 的语句。
- 问题描述: 配置 blocked process threshold 为 30秒，通过 SQL Trace 捕获并分析超过阈值的阻塞事件。

规则 3: 平均阻塞时间检查

- 性能计数器: sys.dm_db_index_operational_stats
- 检查目标: 阻塞的平均持续时间应低于 100ms。
- 问题描述: 如果平均阻塞时间 >100ms，表明阻塞持续时间较长，可能对系统性能产生显著影响。建议通过查询 sys.dm_db_index_operational_stats 分析阻塞详情。

规则 4: 等待状态检查

- 检查目标: 排查阻塞相关的等待状态。
- 问题描述:
  使用以下查询查看等待状态：

SELECT TOP 2 wait_type, wait_time_ms  
FROM sys.dm_os_wait_stats  
ORDER BY wait_time_ms DESC;

如果等待状态以 LCK_M_ 开头，说明系统经常发生阻塞，需要进一步优化资源访问和锁管理。

规则 5: 死锁频率检查

- 检查目标: 每小时死锁事件不应超过 5 个。
- 问题描述: 如果死锁频繁发生（超过每小时 5 次），需要排查死锁的根本原因。

建议启用 Trace Flag 1204 或通过 SQL Trace 跟踪相关事件。
注意：死锁往往伴随着阻塞事件，需结合阻塞分析一同排查。

SQL语句如下：

-- 查询当前数据库上所有用户表格在Row lock上发生阻塞的频率
declare @dbid int
select @dbid = db_id()
Select dbid=database_id, objectname=object_name(s.object_id)
, indexname=i.name, i.index_id --, partition_number
, row_lock_count, row_lock_wait_count
, [block %]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2))
, row_lock_wait_in_ms
, [avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count)
as numeric(15,2))
from sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s,   sys.indexes i
where objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by row_lock_wait_count desc

4.1.6. 网络传输

规则 1: 网络延迟检查

- 性能计数器: Network Interface - Output Queue Length
- 检查目标: 网络输出队列长度应小于 2。
- 问题描述: 如果 Output Queue Length > 2，可能是由于网络延时过高，或者应用频繁与数据库服务器交互，导致网络流量超出承载能力。

规则 2: 网络数据包丢失检查

- 性能计数器:

- - Packets Outbound Discarded
  - Packets Outbound Errors
  - Packets Received Discarded
  - Packets Received Errors

- 检查目标: 所有丢包和错误计数器应为 0。
- 问题描述:

如果 Packets Discarded 或 Packets Errors 计数器出现非零值，可能是由于网络带宽用尽或传输过程中发生丢包。
需要检查网络是否过于繁忙或设备是否存在故障，以避免数据库交互受到影响。

以下是优化总结：

优化表索引设计
对于频繁发生 Insert、Update 和 Delete 操作的表，应尽量减少索引数量。这样可以减少索引的维护开销，提升 DML（数据操作语言）性能。
降低 CPU 使用率
通过提高执行计划的重用率，减少不必要的编译和重新生成计划的开销，同时降低 Join 的数量，简化查询逻辑，从而优化查询效率并减少 CPU 资源消耗。
缓解 I/O 瓶颈
优化索引设计，确保常用数据的快速访问路径，同时降低 Join 数目以减少大范围扫描或排序操作。提高页面在内存中的缓存生命周期（Page Life Expectancy），以减少磁盘 I/O 压力，提高系统响应速度。
内存压力评估
通过监控 Page Life Expectancy 指标，观察内存中 Database Pages 的稳定性。如果该指标未出现突然下降，说明内存部分未存在明显瓶颈，从而确保内存资源分配合理，避免频繁页面置换导致性能问题。
减少阻塞现象
通过优化索引设计减少锁争用，并缩小事务大小，尽可能减少事务的持锁时间，从而降低阻塞发生概率，提升并发性能。

4.2. OLAP

4.2.1. 数据库设计

规则 1: 数据库查询优化

- 性能计数器: Sys.dm_exec_sql_text 和 Sys.dm_exec_cached_plans
- 检查目标: 对于频繁运行的查询，尤其是数据仓库更新和周期性任务，应通过建立覆盖索引（covered indexes）来优化查询速度。
- 问题描述: 终端用户的查询负载与数据更新任务不同，因此可以通过增加合适的索引来提高查询性能，特别是对那些需要排序或执行 RID 查找的查询。

规则 2: 数据页面碎片管理

- 性能计数器: sys.dm_db_index_physical_stats
- 检查目标: 碎片率应保持在 <25% ，以减少读取相同数据所需的页面数，降低 I/O 负担并优化内存使用。
- 问题描述: 数据页面碎片过多会增加读取页面的数量，从而加重 I/O 负荷。应定期重建索引，控制碎片比率，以提高查询效率。

规则 3: 索引缺失检查

- 性能计数器: Sys.dm_db_missing_index_group_stats, Sys.dm_db_missing_index_groups, Sys.dm_db_missing_index_details
- 检查目标: 确保没有缺少关键索引，避免全表扫描的性能问题。
- 问题描述: 如果查询缺少必要的索引，可能导致全表扫描，显著降低查询性能。应定期检查缺少的索引，并添加合适的索引。

规则 4: 无效索引管理

- 性能计数器: Sys.dm_db_index_usage_stats
- 检查目标: 删除未使用的索引，减少 SQL Server 的维护负担。
- 问题描述: 未使用的索引会增加不必要的存储和维护开销，应定期检查并删除这些索引，以优化 SQL Server 性能。

常用SQL如下：

-- 返回最经常运行的100条语句
SELECT TOP 100
          cp.cacheobjtype
          ,cp.usecounts
          ,cp.size_in_bytes
          ,qs.statement_start_offset
          ,qs.statement_end_offset
          ,qt.dbid
          ,qt.objectid
          ,SUBSTRING(qt.text,qs.statement_start_offset/2,
              (case when qs.statement_end_offset = -1
              then len(convert(nvarchar(max), qt.text)) * 2
              else qs.statement_end_offset end -qs.statement_start_offset)/2)
          as statement
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
inner join sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
where cp.plan_handle=qs.plan_handle
and cp.usecounts>4
ORDER BY [dbid],[Usecounts] DESC

-- 返回当前数据库所有碎片率大于25%的索引
-- 运行本语句会扫描很多数据页面
-- 避免在系统负载比较高时运行
declare @dbid int
select @dbid = db_id()
SELECT * FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, NULL)
where avg_fragmentation_in_percent>25
order by avg_fragmentation_in_percent desc
GO

-- 当前数据库可能缺少的索引
select d.*
        , s.avg_total_user_cost
        , s.avg_user_impact
        , s.last_user_seek
        ,s.unique_compiles
from sys.dm_db_missing_index_group_stats s
        ,sys.dm_db_missing_index_groups g
        ,sys.dm_db_missing_index_details d
where s.group_handle = g.index_group_handle
and d.index_handle = g.index_handle
order by s.avg_user_impact desc
go
--- 推荐建索引的字段
declare @handle int
select @handle = d.index_handle
from sys.dm_db_missing_index_group_stats s
        ,sys.dm_db_missing_index_groups g
        ,sys.dm_db_missing_index_details d
where s.group_handle = g.index_group_handle
and d.index_handle = g.index_handle
select *
from sys.dm_db_missing_index_columns(@handle)
order by column_id

4.2.2. CPU

规则 1: CPU 资源检查

- 性能计数器: Sys.dm_os_wait_stats
- 检查目标: 如果 Signal Waits 占总运行时间的比例超过 25% ，说明 CPU 资源紧张。
- 问题描述: 在高负载的 OLTP 系统中，CPU 资源紧张可能导致指令等待时间过长，影响系统性能。如果超过 25%，需要考虑扩展 CPU 资源或优化查询。

规则 2: 执行计划重用避免过度

- 性能计数器: SQLServer:Statistics
- 检查目标: 避免执行计划重用过多，确保查询使用最合适的执行计划。
- 问题描述: 在数据仓库系统中，虽然指令量比 OLTP 系统少，但每条指令可能非常复杂。为了确保查询效率，应避免过度依赖执行计划重用，特别是在复杂查询的情况下。

规则 3: 并行执行计划检查

- 性能计数器: Sys.dm_os_wait_stats
- 检查目标: 如果 Cxpacket 等并行执行等待状态占比 <10% ，说明并行执行不适用，或者系统中存在其他瓶颈。
- 问题描述: 数据仓库系统通常需要并行执行计划来处理复杂查询。如果并行执行计划不常见，可能意味着查询不够复杂，或者系统存在其他性能瓶颈，导致并行执行计划未被充分利用。

查询SQL可以参考OLTP的CPU章节。

4.2.3. 内存

规则 1: 内存分配等待检查

- 性能计数器: Memory Grants Pending
- 检查目标: 如果 Memory Grants Pending 的值大于 1，说明存在内存分配等待。
- 问题描述: 当查询等待内存分配时，说明系统内存不足，可能存在内存压力。需要进一步检查内存资源或优化查询以减轻压力。

规则 2: 页面生命周期检查

- 性能计数器: Page Life Expectancy
- 检查目标: 如果 Page Life Expectancy 经常下降，说明内存资源不足。
- 问题描述: 在数据仓库系统中，由于访问大量历史数据，页面调度和内存使用波动较大。虽然要求不如 OLTP 系统严格，但如果页面生命周期经常下降，可能表明内存资源不足，影响整体性能。可以考虑通过优化索引来减少内存压力。

4.2.4. I/O

规则 1: 读操作延迟检查

- 性能计数器: Average Disk sec/read
- 检查目标: 如果读操作的延迟超过 20ms，应优化，目标为 4-8ms 内完成。
- 问题描述: 在没有 I/O 压力的情况下，读取操作应在 4-8ms 内完成。如果超过 20ms，则可能存在磁盘性能问题，需要优化磁盘或查询。

规则 2: 写操作延迟检查

- 性能计数器: Average Disk sec/write
- 检查目标: 对于日志文件等连续写操作，延迟应控制在 1ms 内。
- 问题描述: 写操作延迟过高（>20ms）通常表示磁盘性能不足，尤其是对于日志文件的连续写入操作。

规则 3: 索引缺失检查

- 性能计数器: SQLServer:Access Methods - Full Scans/sec 和 SQLServer:Access Methods - Range Scans/sec
- 检查目标: 如果全表扫描或范围扫描频率较高，可能缺少适当的索引。
- 问题描述: 高频的全表扫描或范围扫描通常表明查询没有使用有效的索引，需要优化索引设计以提高查询效率。

规则 4: I/O 等待状态检查

- 性能计数器: Sys.dm_os_wait_stats
- 检查目标: 如果 ASYNCH_IO_COMPLETION、IO_COMPLETION、LOGMGR、WRITELOG 或 PAGEIOLATCH_X 等等待状态占比高，说明系统存在 I/O 瓶颈。
- 问题描述: 这些等待状态指示 I/O 性能瓶颈，可能需要优化磁盘 I/O 或检查 I/O 相关配置，以提高系统的响应能力和吞吐量。

由于Data Warehouse里运行的语句难免要做Page Reads的动作，Data Warehouse系统的磁盘读会比OLTP系统要高很多，这也是难免的。但是只要是有I/O瓶颈，就会影响系统的性能。所以还是要检查，是否有优化的空间。

4.2.5. 阻塞

规则 1: 阻塞发生频率检查

- 性能计数器: sys.dm_db_index_operational_stats
- 检查目标: 如果阻塞发生频率大于 2% ，需要进一步优化。
- 问题描述: 如果在用户表格上发生频繁的锁等待和阻塞，可能需要优化查询、索引设计或考虑更改事务隔离级别以减少阻塞。

规则 2: 阻塞事件报告检查

- 性能计数器: Sp_configure
- 检查目标: 如果 SQL Trace 中报告的阻塞语句持续时间超过 30s，需要进行分析。
- 问题描述: 当 blocked process threshold 设为 30s，且阻塞事件超过此时间阈值时，表示系统存在较为严重的阻塞问题，应查看具体的阻塞源头并进行优化。

规则 3: 平均阻塞时间检查

- 性能计数器: sys.dm_db_index_operational_stats
- 检查目标: 如果平均阻塞时间大于 100ms，应优化数据库性能。
- 问题描述: 长时间的阻塞会影响系统性能，增加响应时间。需要检查查询、索引、锁策略等，确保系统流畅运行。

规则 4: 阻塞等待状态检查

- 性能计数器: Sys.dm_os_wait_stats
- 检查目标: 如果 LCK_M_?? 等锁等待状态占据前两位，说明系统经常发生阻塞。
- 问题描述: 频繁的锁等待可能导致性能瓶颈，可以考虑使用 Row Versioning 技术以减少写锁的争用，避免写阻塞对读取操作的影响。

和其他资源不同，阻塞对Data Warehouse系统的危害和对OLTP系统一样严重，一样要严格避免。唯一有点不一样的是，由于Data Warehouse系统的修改量在工作时间一般比较少，可以考虑使用Row Versioning技术，避免写阻塞读的情况。

Data Warehouse与OLTP系统的区别总结：

索引设计: Data Warehouse 系统的表格可以建立更多索引，因为数据在非工作时间修改较少，主要依赖批处理进行数据同步。
执行计划管理: 在 Data Warehouse 中，建议频繁进行 recompile，以确保每次执行时都能选择最佳执行计划，而不是过度依赖计划重用。
排序优化: 对于涉及大结果集排序的查询，可以考虑添加索引来优化排序操作，减少性能开销。
索引缺失分析: 对 SQL Server 认为缺少的索引，应进行分析并采取合适的措施补充，确保查询性能。
数据存储与碎片管理: 在需要大规模扫描的查询中，确保数据在磁盘上连续存储能提高性能。同时，定期通过重建索引减少碎片，保持数据读取效率。
并发执行优化: 对于复杂查询，适当的并发执行有助于提高查询效率，尤其是在 Data Warehouse 环境中。

5. 小结

到目前为止，我们集中讨论了SQL Server对内存、磁盘I/O和CPU的调度和使用方式，以及如何判断这三个最重要的系统资源上是否存在瓶颈。同时也介绍了OLTP和Data Warehouse这两大类数据库应用系统对资源的不同需求，以及判断资源是否存在瓶颈的一些基本原则。

最后总结整体性能分析思路：

系统资源整体检查

- 检查内存、CPU 和 I/O 资源的使用情况，判断是否存在瓶颈，以及资源带宽是否充分利用。
- 如果在系统级别发现资源瓶颈，需确认主要资源消耗是否来自 SQL Server，而非 Windows 或其他应用程序。

任务等待分析

- 检查 sys.dm_exec_requests 的等待状态，结合 sys.dm_os_wait_stats，分析 SQL Server 任务最容易等待的资源类型（具体方法参见第16章小结）。

内存使用检查

- 检查内存分配的三大块：Database Pages、Stolen Memory 和 Multi-Pages，确保内存使用没有问题。
- 内存资源直接决定 SQL Server 性能，是优化的首要目标。

磁盘 I/O 检查

- 确认磁盘子系统是否超负荷运转，以及磁盘响应速度是否理想。

CPU 瓶颈分析

- CPU 瓶颈通常只有在内存和 I/O 没有问题、也没有阻塞时才会出现。
- 如果出现 CPU 瓶颈，需要检查 SQL Server 的工作负荷是否正常，是否处于超负荷状态。

内存与 I/O、CPU 的关联验证

- 当 I/O 和 CPU 出现瓶颈时，需验证是否与内存瓶颈相关。例如，当 SQL Server 缺乏内存时，磁盘 I/O 通常会非常忙，但根本问题可能是内存不足，而非 I/O 问题。

配置检查

- 检查 SQL Server 的 CPU、内存和 I/O 配置是否符合推荐设置，评估是否可以通过调整配置来缓解瓶颈（此方法成功的可能性通常较小）。

工作负载分析

- 检查 SQL Server 中的运行负载，定位引发资源瓶颈的具体语句及其来源，优化资源消耗的重点操作。

参考文献：

《SQL Server 2012事实与管理实战指南》