SQL Server 2012实施与管理实战指南摘要 - 第 13 章

第13章 任务调度与cpu问题 572 

13.1.               SQL Server独特的任务调度算法 573 

·               虽然SQL Server是Windows的一个应用Process，也接受Windows的调度，但它主动接管了内存管理和开发了一套任务调度机制，以适应SQL Server高并发的需要。

·               SQL在Windows的基础上又开发出了一套自己的SQLOS,包括以下部分，其中任务调度管理和内存管理是两大核心：

o   任务调度管理子系统

o   内存管理

o   错误，异常处理机制

o   死锁侦测和解决机制

o   运行第三方代码（dll，extended SP等）机制

·               为什么SQL Server要研发自己的任务调度管理子系统

o        Windows任务调度管理的特点

ü      多线程抢占式操作系统（Preemptive Multi-Threaded Opertating System）

ü      操作系统决定根据各个线程的Priority和Quantum（时间片）决定哪个线程运行。时间片用完或另一个有更高等级的线程在等待CPU，当前正在运行的线程就要被迫让出CPU资源。

ü      在Windows操作系统中，以线程（thread）为任务的调度单位。类似的，内存管理则是以进程（Process）为单位。

ü      无法保证一个线程不会被Windows打断。

o        由Windows来调度管理SQL Server应用的困难

ü      早期的SQLSERVER曾经依赖Windows的线程调度。但随着硬件水平和并发用户的增多，这种方法缺点越发明显。

ü      一个用户的连接或线程在其周期中，大部分时间是空闲的，维护这些闲置的线程成本高。

ü      在一个高并发的应用中，并发的线程数远远大于CPU数，这样势必造成很多线程切换（context switch），这是个Kernel Process，开销较大。

ü      Windows中断线程时，根本不在乎它在中断点时的运行状态。

o        由SQL Server自己来调度管理线程的优点

ü      空闲状态的连接不占线程资源。

ü      对于每一个CPU，SQL内部会有一个调度（scheduler）,由这个scheduler决定在某个时间点，到底是哪个SQL线程去运行。所以在Windows层面，每个CPU最多只会对应一个处于运行状态的线程。大大降低Windows层面的上下文切换context switch。

ü      实践证明：很多有着1000~2000个并发用户的SQL，线程数也只需要一两百个。SQL完成的批处理量每秒钟可以达到10,000 - 20,000个。

13.1.1.                    sqlos的任务调度算法 575 

·               sqlos几个重要的概念

o        Scheduler

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

ü      所谓逻辑CPU，就是SQL从Windows层面上看到的CPU数目，如果是一个双核的CPU，那么一个物理CPU在SQL看来就是两个逻辑CPU。如果系统还使用了超线程Hyper-threaded，那对SQL来讲就是4个逻辑CPU。

ü      SQL Server 上，每一个CPU通常会对应一个Scheduler。如果有4个CPU的话，那么通常就会有4个User Scheduler。

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

                                       各种CPU和SQLSERVER版本组合自动配置的最大工作线程数

CPU数	32-bit	64-bit
<=4	256	512
8	288	576
16	352	704
32	480	960

ü      但在同一个时间点，只能有一个拥有scheduler的worker处于运行状态，其它worker都必须处于等待状态。这样能降低每个逻辑CPU上的处于正在运行状态的线程数目，降低context switch，提供可扩展性。

ü      scheduler是SQL的一个逻辑概念，它不与物理CPU相绑定。也就是说，一个scheduler可以被Windows安排一会儿在这个CPU上，一会儿在那个CPU上。但是，如果在sp_configure里设置了CPU affinity mask，那么scheduler就会固定在某个特定的CPU上。

o        Worker

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

ü      每个worker会固定代表一个线程（或纤程），并且和一个scheduler相绑定。如果Scheduler是固定在某个CPU上的（通过设置CPU affinity mask），那么worker也会固定在某个CPU上。

ü      每个scheduler有worker的上限值（见上表），并且可以根据SQL工作负荷创建或释放worker。如下：

ü      每次worker都会去运行一个完整的任务（task）。在任务做完之前不会退出scheduler，只有在当前有新任务要运行，但没有空闲的worker并且当前Scheduler上Worker数目没有超过最大Worker数目时，才会创建新的worker。

ü      某个worker空闲超过15分钟，scheduler可能会删除这个worker，以及其对应的线程。当SQL遇到内存压力的时，也会大量删除处于空闲状态的worker，以节省multi-page内存开销。

o        Task

ü      在worker上运行的最小任务单元。最简单的task就是一个简单batch。

ü      例如，客户发过来下面的请求：

SELECT @@SERVERNAME

GO

SELECT GETDATE()

GO

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

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

o        Yielding

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

ü      每个scheduler都会有一个runnable列表，所有等待CPU运行的worker都会在这个列表里排队，以先进先出（FIFO）的算法，等待SQL分配给它scheduler运行。

ü      SQL定义了很多yieding的规则，约束一个task在scheduler运行的时间。如果task比较复杂，不能很快完成，会保证task在合适的时间点做yieding，不至于占用scheduler太多时间。

Ø       当worker每次要去读数据页的时候，SQL会检查这个worker已经在scheduler上运行了多久，如果已经超过4ms，就做yieding

Ø       每做64KB的结果集排序，就会做一次yieding

Ø       在做语句编译compile的过程中（这个过程比较占CPU资源），经常会有yieding

Ø       如果客户端不能及时把结果集取走，worker就会做yieding

Ø       一个batch里的每一句话做完，都会做一次yieding

ü      正常来讲，哪怕一个task要做很久，它使用的worker是会经常做yieding的，不会长时间占用CPU不放。如果在一个scheduler上同时有很多worker要运行，SQL通过worker自动yieding的方式调度并发运行。这个比Windows用上下文切换context switch这麽粗鲁不分青红皂白地打断更有效。

·               SQLOS的任务调度

o        了解了Connection, Batch, Task, Worker, Scheduler, CPU这些概念后，那么，它们之间的关系到底是怎么样呢？

如上图所示，左边是很多连接，每个连接有一个相应的SPID，只要用户没有登出，或者没有timeout,这个始终是存在的。标准设置下，对于用户连接数目，是没有限制的。

在每一个连接里，我们可能会有很多batch，在一个连接里，batch都是按顺序的。只有一个batch执行完了，才会执行下面一个batch。因为有很多连接，所以从SQL Server层面上看，同时会有很多个batch。

SQL Server会做优化，每一个batch，可能会分解成多个task以支持如并行查询。这样，在SQL层面上来看，同时会有很多个TASK。

SQL Server 上，每一个CPU通常会对应一个Scheduler, 有几个额外的系统的Scheduler，只是用来执行一些系统任务。对用户来讲，我们只需要关心User Scheduler就可以了。

每个Scheduler上，可以有多个worker对应。Worker是真正的执行单元，Scheduler（对CPU的封装）是执行的地方。Worker的总数受max worker thread限制。每一个worker在创建的时候，自己需要申请2M内存空间。如果max worker thread为1024，并且那些worker全部创建的话，至少需要2G空间。所以太多的worker，会占用很多系统资源。

·               How a task is managed on a scheduler?

·               跟踪运作的流程

--步骤一：执行下面的脚本，创建一个测试数据库和测试数据表

CREATE DATABASE TEST

GO

USE TEST

GO

CREATE TABLE TEST ( ID int, name nvarchar(50) )

INSERT  INTO TEST

VALUES  ( 1, 'aaa' )

--步骤二：执行下面的语句，注意，我们这里并没有commit transaction.

BEGIN TRAN

UPDATE  TEST

SET     name = 'bbb'

WHERE   [ID] = 1

--步骤三：打开另外一个窗口，执行下面的语句，我们会看到，下面的查询会一直在执行，因为我们前面的一个transaction并没有关闭。

--从查询窗口，我们可以看到，下面语句执行的SPID为54

--SELECT * FROM TEST

--步骤四：查看连接。从下面的查询来看，我们的连接对应的SPID是54，被block住了。

SELECT  *

FROM    sys.sysprocesses

WHERE   spid = 54

--步骤五：查看batch - SQL Profiler。

-- 我们查看SQL Profiler, 看到我们的Batch是SELECT * FROM TEST

--步骤六：查看TASK - sys.dm_os_tasks

--用下面的DMV, 我们可以看到，针对SESSION_ID=55的，只有一个task. (地址为0x0000000265B42188),

--而针对该TASK的worker地址为: 0x0000000277264160。同时我们也可以看到该worker运行在Scheduler 1上面。

SELECT  task_address ,

        task_state ,

        scheduler_id ,

        session_id ,

        worker_address

FROM    sys.dm_os_tasks

WHERE   session_id = 54

--步骤七：查看WORKER - sys.dm_os_workers

--从下面的查询可以知道，这个WORKER已经执行了1726 task了。这个worker相应的Scheduler地址是0x0000000277264160

SELECT  state ,

        last_wait_type ,

        tasks_processed_count ,

        task_address ,

        scheduler_address ,

        *

FROM    sys.dm_os_workers

WHERE   worker_address = 0x0000000277264160

--步骤八：查看SCHEDULER - sys.dm_os_schedulers

--从下面的查询可以得知，Scheduler_address (00x0000000277120040) 相应的CPU_ID是0。

--在我们的系统上，有4个CPU, 编号分别为0， 1， 2， 3. 但是有7个SCHEDULER, 其中3个是SYSTEM SCHEDULER,

--4个是USER SCHEDULER。在每个SCHEDULER上，有相应的WORKER数目。因为WORKER是根据需要而创建的，

--所以，在每个SCHEDULER上，目前WORKER数目很少。而且其中有些WORKER还处于SLEEPING状态。

SELECT  scheduler_address ,

        scheduler_id ,

        cpu_id ,

        status ,

        current_tasks_count ,

        current_workers_count ,

        active_workers_count

FROM    sys.dm_os_schedulers

13.1.2.                    任务调度健康监测及常见问题 580 

·               通过这样的方法，SQL erver保证在一个时间点，每个CPU上最多只有一个Task在运行，从而减少了Context Switch的需求。同时Task Yielding又保证SQL Server任务的高并发运行。一般不会出现一个Task长时间地占用CPU资源而堵塞其它Task运行的现象。这个算法的缺点是当SQL SERVER代码质量有问题或者系统资源出问题，如果某个task意外运行很长时间都没有做yieding，那么它就会长时间地占用CPU。所以，这个算法对SQL SERVER代码质量要求比较高。

·               当Scheduler对任务调度有问题时: ErrorLog + Mini-Dump

·               17883 -某个scheduler疑似有问题。某个task运行了超过60秒钟都没有做过yieding，SQL就会打印出一个17883错误。

o        如果usermode的时间很长：很有可能当前线程所运行的代码进入了一个循环，很长时间都出不来。这种问题在SQL代码里不应该出现。如果出现，常常是怀疑是SQL代码哪里写得不够优化。建议升级SQL到最新的服务包版本，以避免一切已知的问题。

o        如果kernelmode的时间很长：说明当前线程主要都是在运行操作系统管理的核心态功能。如果要找到问题的根本原因，可能要作kernel mode的debug。怀疑的方向是某个驱动程序，或者是操作系统本身。建议升级操作系统的补丁包和有关驱动的版本。

o        如果usermode和kernelmode的时间都不高：线程一般是正在等某个API返回，例如：waitforsingleobject，sleep,writefile,readfile。这些函数按道理应该很快返回，所以SQL就没有设计在这里做yieding。但是当时却因为某种未知原因而长时间地没有返回。其中I/O问题导致的writefile和readfile长时间不返回，是17883的一个最常见原因。这时候伴随系统I/O问题，DBA要检查一下各个磁盘的吞吐量是否正常。

o        如果系统空闲率system idle%和进程使用率process utilization%都很低：很可能是因为由于SQL进程以外的其它应用或操作系统本身产生了CPU100%的现象，使得SQL拿不到CPU资源去运行线程，进而导致task没有及时做完。这个要观察性能监视器和CPU有关的计数器的值就能确认。

·               17884和17888 -所有scheduler都疑似有问题

o        如果SQL发现每个scheduler都没有thread能够有进展，就会报告17884错误

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

o        常见的17884/17888错误产生原因：

ü      所有scheduler都遇到了17883错误

ü      所有的worker都被某个关键资源阻塞

ü      所有的worker都在运行一个很长时间才能返回的语句

o        17884/17888错误比17883影响大。很多情况下，17883错误是由于磁盘响应太慢导致的。磁盘瓶颈消失，17883问题也会自动消失，SQL任务调度会自动恢复正常。

13.1.3.                    一个内存转储文件的分析调试过程 583 – 利用微软的调试器WinDbg去分析Dump里面的有关线程的Call Stack去了解为什么会出现Non-yielding。常见的Non-yielding报错的 Call Stack例子：

·               磁盘子系统出现故障 – 一个磁盘写入操作很长时间都没有得到返回

o        数据库文件使用了NTFS的压缩

o        一个Filter Driver工作不正常（一般是杀毒软件等）

o        系统有严重的内存压力

o        IO通道存在问题

·               SQL Server加载的一个dll想显示对话框，但SQL Server是一个服务器程序，没有User Interaction，所以没有人能关闭这个对话框，这个线程永远僵死在那儿。

·               杀毒软件的原因 -杀毒软件有一个钩子（Hook）挂在进SQL的进程里面，而杀毒软件不会知道SQL Server需要Yielding，因此如果杀毒软件与SQL Server不兼容，就一直挂在那儿，出现non-yeilding的现象。

13.1.4.                    案例分析 588

·               除了分析在Dump文件中的Call Stack，还可以通过观察每个Scheduler的状态以及每个Thread的等待状态来找出问题的可能原因。

--在SQL上运行下面的指令，了解scheduler和用户连接所使用的thread的情况

  DBCC SQLPERF(umsstats) --(ums = User-Mode Scheduling)

 SELECT * FROM sys.sysprocesses WHERE kpid<>0 -- kpid是线程ID

  --DMVs

 SELECT * FROM sys.dm_os_schedulers

 SELECT * FROM sys.dm_os_workers

 SELECT * FROM sys.dm_os_threads

 SELECT * FROM sys.dm_os_tasks

 SELECT * FROM sys.dm_os_waiting_tasks

 SELECT * FROM sys.dm_os_ring_buffers

·               案例分析

o        由于系统繁忙，用SQL Trace不合适。而是使用了收集性能监视器日志+每隔15秒循环一次的TSQL脚本查询了以上的DMVs（要使用sqlcmd运行，将结果输出到文本文件里）

13.2.               sql server cpu 100%问题 592

·               SQL Server对CPU的使用，相对于内存和磁盘的使用，还是比较少的。如果一台SQL服务器的CPU使用率大部分时间超过60~70%，就已经算是比较高的

·               SQL Server主要在下面的操作中使用CPU资源

o        编译和重编译

o        排序sort和聚合计算aggregation

o        表连接操作join

·               和CPU有关的设置比较有限，主要都在sp_configure下

o        Priority boost - 提升SQLSERVER的优先级。但是这个设置是不推荐的，因为会打乱Windows正常的进程调度。

o        Affinity mask - 为了防止SQL使用掉所有CPU资源，可以设置affinity mask，让SQL只使用其中几个CPU。或者当DBA怀疑SQL的scheduler经常在不同的CPU上切换，进而影响性能时，也可以使用affinity mask，让SQL的每个scheduler固定在CPU上，看看是不是对整体性能有帮助。、

o        Lightweight poolin - 使用Windows纤程（轻型池）。默认是关闭的。如果开启了，SQL会在使用线程（thread）的时候使用纤程（fiber）。纤程调度技术能够降低系统的Context Switch数目。但SQL是个context switch不严重的应用（相对于它的工作负荷）。所以在实际应用中，这个设置一般很少推荐使用

o        Max degree of parallelism - 定义SQL最多使用多少个线程来并行执行一条指令。当SQL发现一条指令比较复杂时，会决定用多个线程并行执行，从而提高整体响应时间。但运行多个线程需要多个CPU，别的用户发过来的指令可能会受到影响，甚至可能会拿不到CPU执行。所以对于并发度要求高，每个用户都要求有及时响应的OLTP系统，一般会建议设置每个指令都只用一个线程执行，从而保证SQL在任何时间点，都有多个CPU可以响应多个请求。对于并发用户少的，经常有复杂查询的（例如：数据仓库），可以把max degree of parallelism设置为CPU的数量值。例如：SQL服务器有16个CPU，就设置成16。如果也要考虑并发用户数，可以设置小一点，例如：8，4 等

o        Cost threshold of parallelism -当SQL发现一条指令比较复杂时，会决定用多个线程并行执行。那SQL Server根据什么标准开始并行执行呢？就是Cost threshold of parallelism的值！SQL在做编译的时候，同时会计算候选执行计划的cost，总是先做非并行的执行计划。当它发现这个执行计划的值将大于cost threshold of parallelism的设定值，SQL就会改用并行执行计划。所以如果提高Cost threshold of parallelism的值，SQL会更不容易选择并行执行，从而有更好的并发度。它的默认值是5，一般很少去修改它。

o        Max worker threads 最大工作线程数 - 默认值是0, 允许SQL在启动时自动配置工作线程数。对于大多数系统而言，该设置为最佳设置，一般很少去修改它

·               Troubleshooting CPU 100%

o        与内存的Database Page区域压力、Disk I/O的Data Paging问题一样，CPU 100%的问题主要还是它的工作负荷导致。解决问题的最好方法是找到谁造成了CPU的大量使用，从程序设计或数据库设计的角度来解决它。通过SQL Server的设置，能动的手脚比较少。

o        首先确定服务器CPU使用率到底是多少，其中多少是SQL贡献的。如果SQL的CPU使用率不是很高，而是其它应用导致的，那也不用再检查SQL了。建议使用以下的性能监视器。

ü      检查整个服务器的CPU使用情况，可以使用下面这些计数器：

Ø       Processor：%Processor Time: 该计数器可以监视CPU执行的非闲置线程所用的时间。持续80%到90%的状态可能表明需要升级CPU或需要增加更多的处理器。对于多处理器系统，应该为每个处理器监视一个该计数器的独立实例，代表了在一个特定处理器上的处理器时间之和。若要确定所有处理器的平均时间，请使用System: %Total Processor Time计数器。

Ø       Processor：%Privileged Time（Kernel Mode）：对应于处理器执行Microsoft Windows内核命令（如处理SQL Server I/O请求）所用时间百分比。如果Physical Disk计数器的值很高时，该计数器的值也一直很高，则考虑安装速度更快或效率更高的磁盘子系统

Ø       Processor：%User Time（User Mode）：对应于处理器执行用户进程（例如 SQL Server）所用时间的百分比。

Ø       System：Processor queue length：对应于等待处理器时间的线程数。当一个进程的线程需要的处理器循环数超过可获得的循环数时，就产生了处理器瓶颈。如果有很多进程在争用处理器时间，可能需要安装一个速度更快的处理器。如果使用的是多处理器系统，则可以增加一个处理器。

Ø       Context switches/sec：指计算机上的所有处理器全都从一个线程转换到另一个线程的综合速率。当正在运行的线程自动放弃处理器时出现上下文转换，由一个有更高优先就绪的线程占先或在用户模式和特权 (内核) 模式之间转换以使用执行或分系统服务。如果此计数器的数值较大，则表明锁定竞争很激烈，或者线程在用户和内核模式之间频繁切换。 

ü      检查每个进程的CPU使用情况，可以使用下面这些计数器：
(1)Process：%Processor Time
(2)Process：%Privileged Time（Kernel Mode）
(3)Process：%User Time（User Mode）

o        从errorlog中确定SQL是否工作正常，看有没有17883/17884之类的问题发生。有没有访越界Access violation之类的严重问题发生。

o        找出CPU 100%的时候SQL里正在运行的最耗CPU资源的语句，对它们进行优化

ü      SQL Trace –尽管 SQL系统的CPU使用率已经很高了，再开SQL Trace有负面影响，但只有SQL Trace可以找出这些Trouble Maker语句。分析Trace文件时要注重CPU，而不是Reads，Writes.

ü      DMVs – Accumulative，有一定参考价值

--SQL2005 DMV: top 50 queries utilziing CPU since startup

SELECT

    highest_cpu_queries.*,

    highest_cpu_queries.total_worker_time,

   　DB_NAME(q.dbid) AS dbname,

    q.[text] AS qtext

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

--find the top 25 recomplied usps

SELECT TOP 25

    sql_text.text AS sqltext,

    sql_handle AS sqlhandle,

    plan_generation_num AS plangenerationnum,

    execution_count AS execcount,

    DB_NAME(dbid) AS dbname,

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

ü      SSMS Reports – 同上

ü      ReadTrace也可用

ü      找到问题语句后，分析CPU是花在编译上还是执行上，从而有针对性地优化数据库和语句。

ü      降低系统负载或升级硬件 - 如果修改程序和数据库设计不可行，那么解决方向就是把系统一部分负载移到其它服务器上或者升级硬件

13.3.               OLTP和Data Warehouse系统差别及常用性能阈值 597 

13.3.1.                    OLTP系统 597 

·               OLTP系统特点是应用有大量的并发程度较高的小事物，包括select,　insert,　update,　delete。这些操作事务时间不会很长，但是要求返回时间很块。

·               数据库设计规则：

o        避免经常运行语句超过4个表做join，可以考虑降低数据库设计范式级别，增加一些冗余字段，用空间换数据库效率。哪些语句会经常运行呢？

--Return the most popular top 100 queries

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

o        避免经常更新的表有超过3个索引 ，索引太多会影响更新效率。哪些表会被经常更新？

--Find the top 100 indexes frequently updated

--From databaseid, objectid, indexid和partitionnumber, determine whhich index on which table

SELECT TOP 100 DB_NAME (database_id),*

FROM sys.dm_db_index_operational_stats (NULL, NULL, NULL, NULL)

ORDER BY leaf_insert_count+leaf_delete_count+leaf_update_count DESC

o        语句做大量I/O表扫描或者Range Scan说明语句缺少合适索引。可从2方面了解语句有没有做做大量I/O  表扫描

ü      看SQL Server: Access Methods – Full Scans/sec and Range Scans/sec的值大不大

ü      Run the query below:

--return the top 50 queries with the most I/O and their execution plans

SELECT TOP 50

        total_logical_reads / execution_count AS avg_logical_read,

        total_logical_writes / execution_count AS avg_logical_write,

        total_physical_reads / execution_count AS avg_phys_read,

        execution_count AS execcount,

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

        sql_text.text AS 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

o        避免定义没有用的索引，凭空增加SQL的维护负担,查查哪些 indexes not in sys.dm_db_index_usage_stats

·               CPU规则（性能计数器值）

o        Signal Waits% - 指令等待CPU资源的时间占总时间的百分比，如果超过25%，说明CPU资源紧张。运行以下Query，查看Signal Waits%。

SELECT CONVERT(NUMERIC(5,4),SUM(signal_wait_time_ms)/SUM(wait_time_ms)) AS [signal_wait_%]

FROM sys.dm_os_wait_stats

o        执行计划重用率－建议阈值是９０％。对于OLTP系统的核心语句，必须有大于95%的执行计划重用率。可以通过SQL Server：SQL Statistics下面的几个计数器，大致算出执行计划的重用率。

ü      执行计划重用率= (Batch request/sec – Initial Compilation/sec)/Batch requests/sec， 其中Initial compilation/sec = SQL Compilation/sec – SQL Re-compilation/sec

o        并行运行的cxpacket等待状态 －并行意味着SQL在处理一句代价很大的语句，要不就是没有合适的索引，要不就是筛选条件没能够筛选掉足够的记录，使得语句要返回大量的结果。建议小于５％。

--Calculate % of expacket/wait

DECLARE @cxpacket BIGINT

DECLARE @sumwait BIGINT

SELECT @cxpacket=wait_time_ms

FROM sys.dm_os_wait_stats

WHERE wait_type='cxpacket'

SELECT @sumwait=SUM(wait_time_ms)

FROM sys.dm_os_wait_stats

SELECT CONVERT(NUMERIC(5,4),@cxpacket/@sumwait)

·               内存规则（性能计数器值）

o        Ｐage life expectancy（建议＞５分钟，不该经常下降50%）－　如果数据页不能长时间缓存在内存里，势必会影响性能，同时也说明某些语句没有合适的索引。

ü      SQL Server Memory Manager: Buffer Manager,

ü      SQLServer: Buffer Nodes

o        Ｍemory grants pending － 等待内存授予的用户数目，如果大于1，一定有内存压力

ü      Check SQL Server Memory Manager: Memory Grants Pending

o        Sql cache hit ratio－这个值不能长时间（例如：60秒钟）小于90%。否则，常常意味着内存有压力

ü       Check SQLServer: Plan Cache

·               I/O规则（性能计数器值）

o        Ａverage disk sec/read (建议<２０ｍｓ)－ 在没有I/O压力的情况下，读操作应该在4~8ms以内完成

ü      Check Physical Disk

o        Ａverage disk sec/write(建议<２０ｍｓ)  -  对于像日志文件这样的连续写，应该在1ms内完成

ü      Check Physical Disk

o        Big IO Table Scan或者Big IO Range Scan (建议<=1)  , 如有，说明  语句缺少合适的索引

ü      Check SQLServer: Access Method: Full Scans/sec and Range Scans/sec。 If they are high, adding proper indexes.

o        排在前两位的等待状态不能有下面几个。这些等待状态意味着有I/O等待

ü      asynch_io_completion,           

ü      io_completion,

ü      logmgr

ü      writelog

ü      pageiolatch_x

SELECT TOP 2 wait_type 

FROM sys.dm_os_wait_stats 

ORDER BY wait_time_ms DESC

·               阻塞规则（性能计数器值）- 阻塞问题在OLTP系统里危害巨大，是要严格避免的

o        阻塞发生频率 （建议<2%）

--查询当前数据库上所有用户表格在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 * 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 * 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) AS s,

               sys.indexes AS 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

o        阻塞事件报告 (建议<30s) - 在SQL Trace里自动报告超过30秒钟的阻塞语句

ü      Check sp_configure ‘blocked process threshold’

o        平均阻塞时间(建议<100ms)  - 见上面Query       

o        排在前两位的等待状态以这样开头LCK_M_?? - 说明系统经常有阻塞

SELECT TOP 2 wait_type 

FROM sys.dm_os_wait_stats 

ORDER BY wait_time_ms DESC

o        经常有死锁 (每个小时不超过5个 ) -死锁往往伴随着阻塞同时发生

ü      打开trace flag 1204或者在SQL Trace里跟踪相关事件     

·               网络传输规则（性能计数器值）

o        网络有延时，或应用太频繁地和数据库交互  - 检查Network Interface,确保Output Queue Length<=1

o        网络带宽用尽 - 由于网络太忙，有packet在传输中丢失 - check Network Interface，确保没有以下现象发生：Packets Outbound Discarded, Packets Outbound Errors, Packets Received Discarded, Packets Received Errors。

·               优化小结

o        对于经常update  insert  delete的表，在设计时要选择最小数量的索引

o        可以通过提高执行计划重用和降低join的数目降低CPU使用率

o        可以通过优化索引设计，降低join数目和提高页面在内存里的缓存生命周期，缓解I/O瓶颈

o        如果Page life expectancy不会突然下降的话，说明内存的database page部分没有瓶颈

o        可以通过优化索引和缩短事务大小来减少阻塞

13.3.2.                    data warehouse系统 604 

·               数据库设计规则

o        对于经常要运行的查询，它们要做的排序或RID lookup操作可以用covered indexes来优化 。由于终端用户只做查询工作。所以可以建立比较多的索引。查找经常要运行的查询， 见上面那个return the most popular top 100 queries的脚本。

o        尽可能少的碎片，最好小于25% - 可用重建索引的方式严格控制碎片比率

USE master --改为你要扫描索引碎片的那个数据库

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

o        由于会有一些很复杂的查询，全表扫描是难免的，但是要注意不要缺少重要的索引 - 缺少索引会大大降低查询的性能

--Find the possible missing indexes in the current database

SELECT

    d.*,

    s.avg_total_user_cost AS avgtotalusercost,

    s.avg_user_impact AS avguserimpact,

    s.last_user_seek AS lastuserseek,

    s.unique_compiles AS uniquecompile

FROM sys.dm_db_missing_index_groups g

INNER JOIN sys.dm_db_missing_index_group_stats s

    ON s.group_handle = g.index_group_handle

INNER JOIN sys.dm_db_missing_index_details d

    ON g.index_handle = d.index_handle

WHERE s.group_handle = g.index_group_handle

AND d.index_handle = g.index_handle

ORDER BY s.avg_user_impact DESC

--推荐建立索引的字段

 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

o        要避免没有用的索引 - 没有用的索引会凭空增加SQLSERVER的维护负担。同OLTP.

·               CPU规则（性能计数器值）

Signal waits   指令等待CPU资源的时间占总时间的百分比，如果超过25%，说明CPU资源紧张。同 OLTP。

o        执行计划重用率－小于25％（在OLTP中，建议>90%）。数据仓库系统里用户发过来的指令量比OLTP要少很多，但是每一句都会复杂很多，要做多得多的I/O动作，所以保证使用最贴切的执行计划比避免compile要重要得多。执行计划重用率的计算同 OLTP.

o        并行执行计划应该被广泛使用，cxpacket应该是最常见的等待状态 - 并行执行计划对主要运行复杂查询的数据仓库系统比较合适。如果不是这个等待状态最多，要不就是查询还不够复杂，不用并行就已经能达到良好的速度，要不就是系统还有其它瓶颈。% of expacket/wait的算法见OLTP部分。

·               内存规则（性能计数器值）

o        Page life expectancy（不该经常下降50%）－　由于查询会访问一些历史数据，很难保证SQL能够将所有要访问的数据都缓存在内存里，所以在数据仓库里，时不时有一些database paging的动作是难免的。在这方面的要求比OLTP系统要低得多。但是如果Page life expectancy经常地下降，说明内存很缺乏，整体性能会受影响，还是要检查一下是不是可以通过索引来优化。同OLTP。

ü      SQL Server Memory Manager: Buffer Manager,

o        Memory grants pending － 等待内存授予的用户数目，如果大于1，一定有内存压力。同OLTP。

ü      Check SQL Server Memory Manager: Memory Grants Pending

·               I/O规则（性能计数器值）- 这部分同OLTP。

o        Average disk sec/read (建议<２０ｍｓ)－ 在没有I/O压力的情况下，读操作应该在4~8ms以内完成

ü      Check Physical Disk

o        Average disk sec/write(建议<２０ｍｓ)  -  对于像日志文件这样的连续写，应该在1ms内完成

ü      Check Physical Disk

o        Big IO Table Scan或者Big IO Range Scan (建议<=1)  , 如有，说明  语句缺少合适的索引

ü      Check SQLServer: Access Method: Full Scans/sec and Range Scans/sec。 If they are high, adding proper indexes.

o        排在前两位的等待状态不能有下面几个。这些等待状态意味着有I/O等待

ü      asynch_io_completion,           

ü      io_completion,

ü      logmgr

ü      writelog

ü      pageiolatch_x

SELECT TOP 2 wait_type 

FROM sys.dm_os_wait_stats 

ORDER BY wait_time_ms DESC

·               阻塞规则（性能计数器值）- 基本与OLTP一样，但可以使用Row Versioning.

o        阻塞发生频率 （建议<2%）- 同OLTP

--查询当前数据库上所有用户表格在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 * 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 * 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) AS s,

               sys.indexes AS 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

o        阻塞事件报告 (建议<30s) - 在SQL Trace里自动报告超过30秒钟的阻塞语句

ü      Check sp_configure ‘blocked process threshold’

o        平均阻塞时间(建议<100ms)  - 见上面Query       

o        排在前两位的等待状态以这样开头LCK_M_?? - 说明系统经常有阻塞。可以考虑使用Row Versioning。

SELECT TOP 2 wait_type 

FROM sys.dm_os_wait_stats 

ORDER BY wait_time_ms DESC

·               优化小结 - 和OLTP系统相反，数据仓库系统里指令量比较少，并发度低，以查询为主，但是每条指令很复杂。

o        数据仓库里的数据库的表可以建立多一些索引

o        宁可多做一些recompile，少重用它人的执行计划

o        对于复杂的查询，执行时间通常会远大于compile时间，而执行时间的长短和执行计划的优劣密切相关。因此，每次执行都做一次编译，确保SQLSERVER能够选择最好的执行计划常常是合算的

o        如果有很大结果集的排序，可以考虑加一个索引来避免

o        对每个SQL认为缺少的索引，都应该加以分析，看看应该怎麽解决

o        如果大的扫描是难以避免的，那么数据在磁盘上连续存放对性能会极有帮助。同时要用reindex的方法把碎片降低到最小限度

o        通常情况下，并发执行对数据仓库里的语句会有帮助。

13.4.               小结 609 

·        Rule 1 – Never make a conclusion based on one or several independent counters. You need to think from many perspectives in a related way.

·        Rule 2 – The conclusion you drew need to explain both the abnormal incidences and the normal behaviors in SQL Server.

·        Rule 3 – Start with the Windows system. If there is bottleneck on the resource(s), is it mainly caused by SQL Server, or by Windows/other programs?

·        Rule 4 – If there is a bottleneck in SQL Server, first find out which resource SQL Server is waiting for and the wait type (e.g., run sysprocesses or sys.dm_exec_requests, and sys.dm_os_wait_stats)

·        Rule 5 – Memory often is a trouble maker for SQL Server performance. If there is a problem, you need to further locate the bottleneck – in database pages, stolen memory, or multi-pages? For instance, if Page Life Expectancy does not drop sharply, it means there is no bottleneck in database pages.

·        Rule 6 – If no problems on memory, then examine disk I/O to see if the disk is under pressure (e.g., Physical Disk: % Disk Time, Physical Disk: Avg. Disk Queue Length, Avg. Disk sec/Read，Avg. Disk sec / Write，Avg. Disk sec/Transfer etc.)

·        Rule 7 – If no bottleneck on memory and disk I/O, and no locks, examine CPU. If there is a bottleneck, determine if it is caused by the workload or hardware.

·        Rule 8 – If there is a bottleneck on I/O or CPU, determine if it is caused by memory.

·        Rule 9 – Consider to adjust SQL Server parameters through sp_configure etc to improve the SQL Server performance, although less likely.

·        Rule 10 – Examine the running workload in SQL Server, and find out the DMLs or actions causing the problems. Communicate with the applications developers for the troublesome DMLs if necessary.