第一部分
SQL Server 性能优化概述
本部分包含两章，主要介绍何为性能及相关的一些术语，因为只有了解什么是性能，才能开始进一步的学习。如果读者对此已经有了一定的基础和经验，可以跳过这部分，从第3章开始看起。但是基于连贯性，建议读者抽空看看这部分的内容。
第1章
性 能 概 述
在工作、论坛、博客及一些QQ群中，很多人总会问：怎样进行SQL Server性能优化？这个问题很难回答，一个千余人参与、发展了十几年的产品，其所涉及的性能优化并不是三言两语就可以说清楚的。想要熟练掌握该技能，需要系统地了解相关知识，而本书的主要目的就是帮助读者全面深入地把握知识结构。书中会告诉读者从哪里着手，优化的前提有哪些。但是，本书不是写给那些没有任何基础，连一个相对简单的查询语句都不会，或者连SSMS（SQL Server Management Studio）都不知道怎么打开的人看的。如果你是这样的人，建议你先看其他入门书籍。
本章首先会对全书进行一个简单概述，帮助读者了解一些与性能相关的知识。因为在处理性能问题之前，我们首先要知道，什么是性能问题。
1.1何为性能
想要进行性能优化，首先必须要了解性能问题，也就是说，最起码要对性能问题有一个较为明确的定义。试想，你生病了，去看医生，一到医院，你就对医生说：医生，我很不舒服，赶紧给我开药。医生连你哪里不舒服、怎么不舒服都不知道，凭什么开药呢？所以我们首先要知道面临的是什么问题，才能找到相应的对策。
作为DBA，经常会被程序员、公司管理层问道：为什么数据库运行那么慢？如果你进一步询问他们慢到什么程度，有什么表征时，得到的答案往往又很模糊，可能只有慢或者卡。
所以在处理性能问题时，首先要对其有一个清晰的定义，不然会浪费很多时间去查找问题的所在。但是作为非专业人员，的确很难清晰定义所有的问题。为此，不妨来对性能问题下一个非官方的定义：
在现有资源没有达到最大吞吐量的前提下，若系统（包括操作系统、数据库管理系统、应用程序等）不能满足合理的预期表现，则可以定义为有性能问题。
注意上面的限定词——合理。你不应该对所有的应用和操作都赋予很高的期望，比如，对于OLAP系统，它的某些操作往往需要大量时间和资源（比如ETL），你不要期望它总是能在几秒内完成。当然，如果时间过长，也是可以定义为存在性能问题的。
另外，可以考虑一些非正式的定义，比如资源耗费明显过多、运行速度的下降超过规划速度等。
总之，在处理性能问题之前，尽可能给出清晰准确的定义，可以提高问题的解决效率。要分清什么是性能问题，什么不是性能问题（比如权限、某些硬件故障、某些程序bug），因为不同的问题对应的解决方案往往是不同的，所以首先应该对性能问题给出定义。
1.2性能指标
定义性能问题时往往可以有很多指标，其中最常见、最重要的指标有3个：响应时间、吞吐量、可扩展性。响应时间这个指标其实很明显，一个查询运行得快，性能问题通常很少，但如果某个查询的运行时间明显过长，那就说明可能有一定的性能问题了，需要引起注意。终端用户基本上只会关心他/她的请求是否能足够快地得到响应，所以他们的“性能”汇报往往只是告诉你它很“慢”，而这个“慢”其实指的就是响应时间。吞吐量可以理解为网络、设备、端口、虚电路或其他设备单位时间内成功地传送数据的数量，也可以理解为资源的使用情况。比如磁盘，每秒的吞吐量越大，传输的数据就越多，SQL Server在向磁盘读写数据时延时就越短。可扩展性表示在遇到性能问题时，是否可以通过简单的增加资源的方法来解决问题。
对于性能指标，并没有一个固定值或者建议值，通常在要定义工作负荷（也叫做性能基线，将在后面章节中介绍）之后，通过监控及对比来把握。对于性能问题或者管理问题，常规的做法是先进行监控，然后分析监控数据，再根据分析结果进行处理，最后再次监控，一直如此循环往复，直到满意为止。
1.3性能目标
打个比方，笔者比较崇尚中医的治疗方法，他们所采用的“望闻问切”诊断方法对准确掌握病人的病情很有帮助。记得有人说过，DBA很多方面就像中医，在面对性能问题的时候，使用这种中医的诊断思维去处理会事半功倍。其实就是通过多方面检测，找到性能问题的根源和一些潜在风险。中医的一个思想是通过调理人体自身机能去抵抗外部的入侵，旨在把人体调整到一个“平衡”状态，而不是像西医那样直接杀死染病细胞或者病毒。个人认为，优化性能的目的也是把系统调整到平衡状态，要把事情做到极致，但是不要极端。比如，不要花几个小时的时间，去尝试优化一个已经在1～5s内能得到结果的查询。
. 对于SQL Server的优化，一个比较通用的目标就是：尽可能最小化每个SQL语句（或者请求）的响应时间并增加系统的吞吐量，通过减少网络延时、优化磁盘I/O吞吐量以及减少CPU的处理时间来最大化整个数据库服务器的伸缩性，使系统能够协调运作。
简而言之，性能优化的目标就是通过一系列的手段，使系统能够协调、平衡地运作，合理地响应外部及内部请求，实现资源利用的最大化。
1.4影响性能的常见因素
在面对性能问题的时候，我们的眼光和思路不能只局限在SQL Server中，因为很多因素都有可能导致出现性能问题。常见的影响性能的因素主要包括以下几方面。
应用程序的体系结构
应用程序设计
事务和隔离级别
T-SQL代码
硬件资源
SQL Server配置
1.4.1应用程序的体系结构
这部分内容有专门的程序优化书籍，本书不会讲述太多。应用程序的体系结构如果不合理，将会导致数据传输的速度和开销增大，特别是网络层面和并发数。很多年前，应用程序三层架构已经风行全球，典型的三层架构如图1-1所示。
这三个层次是一个整体，任何一部分出现性能瓶颈都将导致系统性能下降，而且会被用户明显地感觉到。
第一层是客户端，它可以是一个网页或者其他应用程序。这部分的问题常表现为网络带宽和客户端机器性能不足（比如网页程序上加载一个庞大的数据集）。
第二层为中间层或者应用层，主要处理事务逻辑。除此以外，它还可以起到杠杆作用，向应用程序提供更好的性能和扩展性，比如通过缓存常见用户请求结果以最小化重复计算、通过在这层使用多个服务器以分摊工作负荷、通过共享一个SQL Server连接以最小化会话数等。SQL Server的每个连接大概需要50KB的存储空间，所以减少不必要的连接可以减轻资源需求方面的压力。
第三层是数据层，主要处理应用程序发出的数据请求，比如存储、查找、操作数据等。本书的主要研究对象就是这一层。
1.4.2应用程序设计
笔者见过很多性能问题，实际上都不是SQL Server的问题，比如从前端导入数据时，部分开发人员在导入的过程中使用前端语言过多地处理数据逻辑，把一些数据库强项放到前端处理，往往就导致应用程序端的内存溢出等问题。
还有一种比较常见的情况，就是前端程序用游标去读取大数据量的结果集，这一方面会导致速度变慢，另外一方面会使得数据集占用内存过久，影响其他请求的性能。在撰写本书过程中笔者还遇到过一种情况：编程语言是Java和dorado，由于展示方式的错误，导致登录应用程序之后一直没有响应，后来发现是首页的树结构问题，树结构菜单展开后会增加SQL服务器的压力。针对问题修改之后运行恢复正常。
应用程序的设计对性能也非常重要，如何处理事务的逻辑、工作流是否合理，以及安全性、会话管理和缓存机制等都对性能和扩展性有很重要的影响。请记住，应用程序和SQL Server组合出来的系统是一个整体，不能单独讨论，但是本书不是百科全书，所以只能针对一些特定的部分进行讨论。除了前端程序，还有数据库的设计，这部分也是很专业的知识点，但它超出了本书范围。
1.4.3事务和隔离级别
这部分是最常见的性能问题的根源之一，将在第9章中展开描述。任何成熟的数据库管理系统都应该支持多并发及保证数据一致性，SQL Server通过事务及锁来实现这方面的需求。与SQL Server交互的应用程序需要用到一个或多个事务，这些事务可以显式或者隐式地启动。作为应用程序设计者，首先应该注意最小化阻塞问题，隔离级别为应用程序提供了一个选择，是选择一致性还是并发性？一致性越强，并发性就越差。另外，需要控制事务运行的时间，这个时间决定了独占锁的生命周期，比如有些锁会一直持续到事务结束才被释放。
1.4.4T-SQL代码
T-SQL是操作数据库的工具之一。大部分的性能问题都是编码引起的，所以编写高效、可维护的代码，对性能的影响不可估量。在面对大数据集时，选用面向集合的T-SQL操作数据库比使用面向过程的游标处理，效率上也高出很多个数量级。另外，除了性能上的考虑，也要考虑T-SQL编码规范，如果拥有编码良好的T-SQL，即使出现性能问题，也能很快地定位。本人经常要对一些两三千行没有注释甚至注释是错误的存储过程代码进行优化，这种工作非常辛苦。
T-SQL如何写才好？没有绝对的答案，需要经验和知识。在本书中会看到，一些简洁的查询并没有想象中那么高效。不过还是有一些建议可用，比如尽量减少需要处理的数据量，以及使用SARG写法等。
1.4.5硬件资源
万丈高楼平地起，硬件就是数据库的“地”，地不稳，高楼也别想建得稳。硬件资源不足或资源得不到合理的利用，都将影响数据库的性能。相关的内容将在第4章做详细介绍。
如果应用程序运行在与工作负荷不匹配的硬件上，即使设计得再好，也不能提高什么性能。比如，一个100TB的库运行在只有2GB的32位Windows Server 上，不出问题才怪。
同时，设计问题也可能反过来导致硬件瓶颈。如果资源充足但是没有合理利用，也会表现出硬件问题，而简单地升级硬件只是一个权宜之计。
可以说，性能问题的表现就是资源问题。
1.4.6SQL Server配置
SQL Server 和其他Windows应用类似，都是类似“傻瓜式”的，很多配置已经默认化，并且在大部分情况下是最优配置。但是既然微软给你“选择”的机会，那证明它在特定情况下还是需要进行调整的。所以合理的配置会对性能有很重要的帮助，相反，不合理的配置则会严重影响性能。在适当的情境下，适当地改变配置可以使其发挥最大性能。这里简要介绍一下CPU和内存相关的一些配置。
1．CPU相关配置
CPU相关配置可以用于控制被SQL Server利用的CPU数量、最大并行度及工作线程数量等。这些配置包含关联掩码、轻量缓冲池、最大工作线程及最大并行度。
（1）关联掩码
控制CPU到SQL Server进程的映射，默认情况下SQL Server会使用服务器所有可用的处理器。通常不建议修改它，但是下面几种情况下可能需要使用。
1）同一台服务器上运行着多个应用程序，并且Windows处于高负荷状态下，可能会将线程转移到不同的CPU中。这时可通过使用关联掩码，将SQL Server的每个调度绑定到各自的CPU上，通过消除线程的迁移，减少因为上下文切换导致的性能问题。
2）控制一台SQL Server工作时所用到的CPU数量可以通过这个参数来设置，特别是当一个服务器上运行多个SQL Server实例时。
（2）轻量缓冲池
当该选项被启用时，SQL Server可以利用Windows纤程进行运作，并把工作进程映射到Windows的线程或者纤程上。纤程和线程类似，但是其开销更低。如果服务器花费了大量时间在内核模式和上下文切换上，开启这个选项会带来一定的好处，但是这个选项也可能导致性能下降，所以需要进行测试。另外，开启这个选项，会使得CLR整合不被支持。
（3）最大工作线程数
工作线程在SQL Server中就是执行用户请求的线程，一个工作线程会一直绑定在一个批处理上，直到处理完成。所以工作线程的最大值也限制了并发数，默认情况下CPU个数与工作线程的关系如表1-1所示。
表1-1逻辑CPU个数与工作线程的关系
逻辑CPU数（个） 32位操作系统（个） 64位操作系统（个）
≤?4 256 512
8 288 576
16 352 704
32 480 960
每个工作进程在32位的OS上使用512KB内存，在X64上使用2MB的内存，在IA64上则使用4MB的内存。为了保证有空余内存，SQL Server一开始只会打开少量的工作线程，根据实际运行情况来增加或者减少线程数。一般在以下两种情况下需要考虑修改。
1）如果知道应用程序只会使用少量工作进程，可以配置一个较小的值，使SQL Server不用为工作线程的最大数目保留内存。
2）如果有一些需要运行很久的批处理（包括等待、锁定），所需的工作进程数量可能会超过默认值。
（4）最大并行度
可以控制用来执行并行查询的进程或者内核的最大数值。这部分在第8章“执行计划”中会有介绍。
2．内存相关配置
内存配置用于控制SQL Server消耗的内存量。通常我们关心的配置有下面几项。
（1）服务器最大最小内存
从性能角度来说，这个配置最重要，特别是在32位OS中，很多人把这个配置误解为物理内存，但是它仅表示缓冲池（Buffer Pool）的内存。任何现代数据库管理系统都严重依赖于内存，这也是很多人会指责SQL Server占用那么多内存的理由之一。对于这种指责，只能说不占用内存的SQL Server（或者其他数据库管理系统）不是好的数据库管理系统。对于这个配置，在64位系统中，推荐设置一个合适的上限，以便为OS或者其他应用预留内存，在后面章节中会有设置建议。
（2）AWE Enabled
在32位的OS中，SQL Server只能使用2GB的虚拟内存。如果已经在boot.ini文件加入了3GB参数，重启计算机后可以使用3GB的虚拟内存。如果物理内存超过4GB，并启用了这个选项，SQL Server可以最高使用64GB内存。
使用AWE需要考虑以下几个问题。
1）尽管允许SQL Server从缓冲池获得最高64GB的内存，但是执行计划、Join、锁和其他机制所使用的内存仍然控制在2GB以内。
2）AWE映射的内存不可分页，并且运行在同一台服务器上的其他应用可能导致内存耗尽。从SQL Server 2005开始，AWE内存可以被动态释放，但是仍然不可调出。
3）在64位的OS中这个选项无法使用，如果SQL Server进程已经被授予内存中的加锁页，缓冲池也会锁住这些页，使得页不能调出。
4）对于内存问题，最好的解决方法就是更换成64位系统。
1.5小结
本章的主要目的是给读者关于SQL Server性能问题的一个概览，让大家明白什么是性能问题及影响性能问题的因素。性能问题种类繁多，但是我们常见的性能问题大部分都能找到原因，而且对SQL Server的性能侦测也有一定的方法可借鉴。在面对性能问题时，首先要对其有清晰的定义，并有足够的基础知识去分析并尝试各种方案，最后评估效果。这些将在后续的章节介绍。
如果你对性能问题感兴趣，首先请摆正心态，因为绝大部分问题都有解决方法，但是如果你心态不好，特别是出现问题就害怕、紧张，即使简单的问题都会被复杂化，最终浪费时间、精力，甚至还是无法解决。所以优化不仅仅需要提高知识水平，还需要锻炼心理素质及其他各方面的素质。
第2章
初 探 优 化
在第1章中解释了什么是性能问题，以及当出现性能问题的时候，我们应该如何面对。本章则将通过一些图示来展示优化性能的方法，让读者有一个大概的了解，也作为后续章节的一个“目录”。
抛开所需的知识水平不谈，对于性能问题，一般有两种解决方式，第一种笔者称为碰运气，在这种方式下，可以随便乱试，如改索引、加硬件等，说不定会有效果，不过后果自负；另外一种是使用一定的方法论和步骤，进行有步骤的优化。本书使用后一种方式。
对于方法和步骤又分两种情况，第一种是知道哪里有问题，比如一个系统运行速度还能接受，但是一查询某个报表就慢得不行，甚至超时，那么99%这个问题就出在该报表相关的程序上（可能是存储过程的问题，也可能是.Net上的问题），如果是数据库层面的问题，抓住执行计划来分析，基本上就可以解决。相关内容将在第5章、第7章、第8章中重点介绍。第二种情况是不知道哪里有问题，只知道系统变慢，这时候就要进入一个优化流程，本书重点放在这种情况下。
2.1优化论
这个标题有点投机取巧，没有深入研究理论知识之前，其实也谈不上“论”。所以，这里只是给出一些常规的优化步骤和手段，希望通过这些流程性的展示，让读者能够自己动手处理大部分常见的性能问题，从而达到本书的学习目的。
首先，在面对性能问题的时候，要足够冷静，不要马上去网上搜，“期望”快速解决问题。只有冷静下来分析问题，才能做出最好的分析和判断，从而快速解决问题。笔者见过有的DBA，在面对性能问题和其他服务器问题的时候，手忙脚乱，一下子蒙了，方向也没有了，局外人一看就知道的问题，他们竟然忽略了。所以，第一步是让自己冷静下来。
在你冷静下来之后，就开始进入分析阶段。通常情况下，我们会遇到两类性能问题。
1）某个功能很慢或者突然变慢，比如某个存储过程、查询等。
2）整个系统都很慢。
面对第一种情况，由于对象比较明确，所以处理起来相对轻松。大部分情况下，你只需要研究执行计划（将在第5章和第8章详细介绍）就可以解决绝大部分问题。这种情况下通过改写查询、改变表结构等操作，通常可以得到明显的效果。对于一些极端情况，就需要考虑更多的方面了，而不能仅从执行计划去分析。
如果面对的是第二种情况，由于对象不明确，因此让人无从下手，此时需要通过多种手段来寻找瓶颈，然后再进行处理。这个过程的难点就是定位瓶颈，由于性能是一个系统级别的事情，所以很多现象都是有关联的，有时甚至会造成误导，比如CPU使用率高，可能是由内存不足、语句不够优化、缺失索引等导致的，如果单纯定位到CPU存在压力，就要求升级CPU，是不合理的做法。面对这类问题，可以通过如性能计数器（第11章）、SQL Profiler/SQL Trace，甚至是SQL Server 2008出现的Extended Events等工具来收集性能方面的数据，或分析等待状态（第7章），然后进行全面、系统的汇总分析，最后定位瓶颈。根据经验来看，大部分这种类型的性能问题其瓶颈集中于低效的语句、不合理的设计、资源配置不合理及没有进行适当的维护等方面。
除了上述两种情况以外，还存在一些特例，本书也会适时对此进行一些介绍。
下面来看看图2-1。这个图展示了常规的性能优化步骤，适合上面的两种情况及部分极端情况。如果通过图中展示的步骤处理之后，还不能优化甚至还不能定位瓶颈，那么就只有两种可能了：要么只有微软才知道（毕竟外人不能知道太多内幕），要么就是无解的问题（经过微软的统计，在性能优化过程中，存在4%的可能性是运气因素，希望我们大家都不要遇到这4%）。
2.2定义问题
在出现问题时，有了冷静的态度之后，关键就是要定位问题了。这里假定你已经可以区分是否存在性能问题。
如何定位问题？答案就是通过当前现象和日常、临时收集的数据进行分析。这种收集是通过大量工具收集并汇总来实现的。常见的定位工具如下。
性能监视器，在第11章介绍。
动态管理对象（DMO，SQL Server 2005才出现），贯穿全书。
SQL Profiler/SQL Trace，在第12章介绍。
PowerShell，在第12章介绍。
Extended Events，在第13章介绍。
其他工具将在第14章介绍。可能在本书编写的过程还会出现部分工具而本书未介绍到，如果读者想要进一步了解，请参考相关资料和书籍。
图2-1性能优化步骤
2.2.1使用工具找到性能瓶颈
可以借助上面介绍的工具，收集数据，并找出异常。比如使用性能监视器监控内存方面的指标，如果发现某个指标值明显超过警戒线，这就需要引起注意，检查其产生的原因了；或者通过DMO，查询等待状态，检查系统正在等待什么资源，通过等待信息一步一步分析问题。
需要注意的是，表象有误导性，不应该通过一两个指标来定位性能问题的根源。当然，这会需要有一定的知识和经验了，但是SQL Server已经问世十几年，大量的问题已经在网上有记载，所以多学习多积累，问题还是不难解决的。
2.2.2通过性能数据进行分类
性能问题有很多种，包含资源配置或使用问题、设计问题、编码问题、管理问题等。每种问题的处理手段和侧重点都不同，所以需要进行分类，然后再做处理。本书将会介绍在获取到性能数据之后，如何对数据进行分类。
这里需要特别提醒的是，如果处理完一个性能问题之后就以为万事大吉，不做好分类、总结甚至一些处理预案，下一次还是有可能再次出现问题的。所以强烈建议，在处理现有的性能问题时，要对问题进行深度分析，找到根源，尽可能避免重复出现，这样才能做到最终的性能优化。同时，在处理完问题后，把问题归档，即使面对的是一些无法避免的问题，有了解决方法的归档和分类，也可以减少侦测性能瓶颈并搜索解决方法的花费时间和降低难度。
性能问题的主要优化步骤如下。
分析实例级别的等待
组合等待和队列
确定方向，然后确定优化方案
细化到数据库、文件级别
细化到进程级别
优化索引/查询
1．分析实例级别的等待
优化方法论的第一步通常是从实例级别找出是哪些等待类型占用了大部分的等待时间。从SQL Server 2005开始，可以使用DMV（sys.dm_os_wait_stats）来实现，如果是SQL Server 2000，要通过DBCC SQLPERF(WAITSTATS)命令来实现。等待信息是很好的问题切入点，而其他工具可能返回的信息过多，容易产生误导。这部分将在第7章中详细介绍。通过等待信息，可以发现比如锁、闩锁、IO、事务日志、内存等对象相关的等待。但是需要提醒的是，大部分的资源问题（比如CPU、内存、IO）都可能是因为设计不合理、编码低效引起的，并不一定是资源真的有问题。
2．组合等待和队列
一旦找到实例级别最高的几个等待类型，就可以把研究面缩小。接下来就是组合等待类型和队列找出有问题的资源了，这一步主要使用性能监视器，跟踪比如I/O队列、缓存命中率、内存等相关计数器，也可以使用SQL Server 2005开始提供的sys.dm_os_performance_counters这个DMV来查看常用的计数器。对于其他有用但是暂时未提供的计数器，还是需要使用性能监视器来监控。这一步在第11章介绍。
3．确定方向，然后确定方案
通过前面的步骤得到准确的信息之后，就要根据这些信息定好优化的方向（比如索引、查询、设计等），然后确定方案。但是如果你发现存在的是一些资源问题，或者阻塞、编译重编译等问题，那么采取的方案就不一样了。
4．细化到数据库、文件级别
到这一步就要细化到数据库、文件级别了，找到哪个库占了比较大的开销，通常是用户数据库引起的，但也可能是系统库的问题。除了库，还要找文件类型，比如是数据文件还是日志文件，文件类型的不同决定了方案的不同。SQL Server 2005开始提供的sys.dm_io_virtual_file_stats函数，可以返回文件相关的I/O信息。如果是SQL Server 2000，可以使用::fn_virtualfilestats函数。
如果是日志文件问题，检查是否和数据文件有I/O争用，因为两者的读写行为是不一样的，日志文件是顺序读写，数据文件是随机读写。如果是TempDB问题，要考虑是否代码使用临时对象过多，配置有无失当等，这部分在第10章介绍。
5．细化到进程级别
把问题集中到一个库上时，可以进一步细化到进程级别，也就是找到需要优化的进程，比如存储过程、批处理等。这里可以使用一些DMV、Profiler（第11章介绍）等工具来查找，比如查找一些查询是否返回了大量的数据（这里指不必要的数据）、运行时间长的查询、CPU、IO高的查询等。
6．优化索引/查询
找到“元凶”之后，就进入优化查询的步骤。首先是改进代码，特别是改写一些明显有性能问题的写法，比如可以用CTE来替代的一些游标查询，适当添加高效的where条件，避免非SARG写法等，使其能从写法上尽可能符合高效利用索引的要求。但是事情总不会那么如意，比如一些二次开发的程序或者一些设计有问题的表结构，会导致你的代码已经没有改写的空间。如果你认为代码无法改进，可能需要通过修改索引甚至是修改设计来实现，比如一个添加一个覆盖索引，把表进行垂直拆分或者水平拆分等。这部分是本书的重点，将在第6章介绍。
2.3根据性能数据分析问题
在收集好数据之后，需要做的就是对数据进行分析。如果不知道哪里出了问题，可能需要分析的数据会很多，新版的SQL Server和Windows都提供了很多工具来优化这些过程，比如第11章中介绍的PAL工具，可以把从性能监视器得到的数据通过一些表格展示，而不需要用户额外再手动去转换、制图。当然，工具不是万能的。
大部分的性能问题集中在低效的编码、不合理的设计及配置上面，当你面对庞大的性能数据时，如果一时间不知道从何处开始，可以尝试先把精力集中在这些地方。比如可以使用如下语句先查找逻辑读取最高的查询。
SELECT TOP (25)
P.name AS [SP Name],
Deps.total_logical_reads AS [TotalLogicalReads],
deps.total_logical_reads / deps.execution_count AS [AvgLogicalReads],
deps.execution_count,
ISNULL (deps.execution_count / DATEDIFF (Second, deps.cached_time, GETDATE ()), 0) AS [Calls/Second],
deps.total_elapsed_time,
deps.total_elapsed_time / deps.execution_count AS [avg_elapsed_time],
deps.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS deps ON p. [Object_id] = deps. [Object_id]
WHERE deps. Database_id = DB_ID ()
ORDER BY deps.total_logical_reads DESC;
然后找出这些查询的执行计划，再进行分析优化，这部分在第8章详细介绍。
2.4验证处理手段及部署
当你分析完问题并有了一定的方案后，就需要进行验证了。如果是在生产环境下，除非非常紧急，否则不应该把你的想法和处理手段在未测试的情况下直接部署到上面。作为“测试”或者解决方案，更好的方法是把生产库备份还原到一个测试服务器上，模拟真实环境，越真实越好，然后进行你的解决方案部署。由于你的应对方案可能是不正确的，所以部署完之后，需要再次验证，然后再分析，迭代地进行这些步骤，直到问题明显得到改进为止。
对于验证环节，可能真的只有在生产环境下才能重现，这时候是可以进行验证的，不过尽可能使用一些手段来做好备份（库、表、代码等），以免产生更严重的问题。举个例子，对于执行计划的分析，可能你已经有了一个更好的执行计划，但是为了不影响系统的正常运行，可以使用第8章中介绍的计划向导来检验执行计划及进行索引修改。或者对于一个存储过程的修改，可以把里面涉及的表、对象等复制一个副本出来，然后用修改了存储过程名之后的测试过程进行验证。
简单来说，直接修改原有环境是最后的手段，应该尽可能地做好回滚的方案，以便真正解决问题。
2.5问题归档
这部分其实不属于性能优化的范畴，但是作为数据库管理员，或者有能力进行数据库管理、优化的其他职位的人员（甚至其他岗位的人员）来说，归档一些重要资料是基本素质。抛开数据库管理方面的问题不谈，对于性能优化，假设你第一次处理一个性能有问题的查询，那么你可能需要分析执行计划、调整索引，而如何获取执行计划、索引的相关信息，这就需要自己研究相关内容然后再编写代码了。或者到网上找一些已有的脚本及工具，如果很幸运这个问题能通过这些脚本和工具得到解决，你就应该把更多的精力放在这类问题的处理流程及资料归档上，甚至让其自动化，以便再次遇见同类问题时，可以直接拿来用，或者修改一下就可以用。
除了归档步骤，还需要分析问题的根源，究竟是因为新发布的程序中存在一些性能问题导致的，还是本来就存在，只是不严重，所以没发现而已。还可能是因为最近做了配置改动等操作，只有找到根源，才能避免再次发生。
个人建议，读者应该建立一个问题收集文档，工具不限，把问题分类并记录现象、原因及解决方法，经验就是这样累积出来的。
2.6小结
这章的目的在于介绍一些优化方法论、优化工具的知识。因为每个部分涉及的知识面都太广，所以掌握核心架构和知识才是根本。后面的章节将逐一详细介绍涉及性能方面的相关信息。
第二部分
SQL Server性能优化理论知识
正所谓工欲善其事，必先利其器。如果不懂基础知识，那么即使得到了性能监控数据，也不知道怎么用。本部分共包含8章内容，涉及SQL Server体系结构，硬件资源，查询优化器，索引及统计信息，等待，执行计划，锁、阻塞和死锁，以及TempDB等内容。
这8章都是SQL Server的核心部分，当然，一个成熟的数据库管理软件不可能仅仅包括这么几部分，只是对于性能而言，这几部分的知识至关重要。由于篇幅有限，本书将采取以点带面的方式，帮助读者掌握每个知识点的核心内容。
另外，对于一些特殊情景下才用到的知识点，由于使用机会不多，所以不会做过多的介绍和演示，但会尽量给出一些参考资料。
第3章
体 系 结 构
本章将介绍一下SQL Server的体系，为后续的性能优化做铺垫。只有了解SQL Server的体系结构后，才知道如何去下手，不然方法论也只是空谈。
提
醒 本章会使用很多英文单词，因为SQL Server毕竟是美国软件公司的产品，即使汉化程度很高，很多系统信息查询结果还是有英文单词，比如sp_who这些，它返回的结果还是英文的，这些部分是不会被汉化的。为了方便读者以后的文献查找、结果集的分析，所以书上尽量同时给出中英文的术语。
3.1SQL Server查询体系
图3-1是一张被广泛使用的图，这个图展示的是一个用户从发出请求到得到SQL Server响应的流程，即查询生命周期。
在整个SQL Server运行周期中，无论哪一步出现性能瓶颈，都将使SQL Server表现出性能问题。下面简要介绍一下其中涉及的各个组件。
1．协议层
在用户发起对数据库的请求后，该请求通过网络接口（SQL Server Network Interface， SNI）传到协议层中。SNI是客户端和服务器之间的连接，对于目前已发布的SQL Server版本，有以下4种网络协议。
1）共享内存（Shared Memory）。它是默认开启的网络协议，也是最快、最简单的协议，但是有一定的局限，只能在SQL Server所在的本机使用。这也是为什么经常能够在本机连接到SQL Server，但是外部机器连不上的原因之一，如果外部访问所需的协议没有开启，外部访问就不能连接SQL Server。而且，由于本机使用了共享内存协议，所以不需要做任何配置就可以连上。这个协议仅用于检查连接问题。
图3-1SQL Server 查询生命周期
2）TCP/IP。这是在互联网上最常用的协议，可让外部访问通过IP地址和端口号（默认1433）来访问SQL Server。
3）命名管道（Named Piped）。它与TCP/IP类似，不过仅限于局域网访问。该协议默认使用445端口，所以如果使用这种协议，需要确保这个端口的可用性。
4）VIA。这几乎是个没有用的协议，并且SQL Server出现的很多问题都是由于开启了这个服务导致的，可能在后续版本中将会移除（实际上在SQL Server 2012已经找不到VIA的影子了），所以不建议用户使用。
不管使用哪种协议，一旦连接接通，SNI会创建一个安全的TDS端点，用于接收和发送请求。
2．表格数据流端点
表格数据流端点（Tabular Data Stream Endpoints）起源于Sybase，现在是微软专属的网络协议。一个网络协议一旦连接成功，就会创建一个对应的TDS端点，用于在客户端与服务器之间互传信息。
对于这一步，性能问题可能会出现在把请求的内容转换成SQL Server能处理的格式的过程中，如果数据量大，转换开销就高，不管是从外部传入SQL Server还是从SQL Server输出到外部，都是如此。
3．命令解析器
在TDS把请求转换成SQL Server内部可识别的格式后，首先会传到命令解析器（Command Parser）处，这个组件会检查传入的T-SQL语法是否符合规定，如果语法不符合要求，就会报错并通过TDS把错误信息传回客户端，最后结束过程。如果语法符合要求，就会生成查询树传入查询优化器中。
命令解析器同时也会检查是否已经存在缓存了的执行计划，如果存在，就重用；如果不存在，就让查询优化器生成执行计划。
4．查询优化器
该组件是SQL Server的核心部分，这部分将在第5章中详细讲述。优化器的作用是对特定的请求进行一系列的优化，使其尽可能高效地对数据进行操作。最终选择最低开销的候选计划生成预估执行计划，并传入查询执行器执行查询。
5．查询执行器
正如其名，查询执行器是执行查询的组件，但是实际上并不真正执行，而是通过OLE DB协议访问并使用一系列的指令操作存储引擎进行查询的执行操作。存储引擎才是实际的查询执行者。
6．数据访问方法
数据访问方法是提供数据和索引的存储、查询及修改的代码集合。它包含了所有操作数据的代码，但是并不由它自己去实际操作数据，而是提交请求到缓冲管理器。
7．缓冲管理器
缓冲（Buffer）管理器是管理Buffer Pool的组件。Buffer Pool是SQL Server内存的主要部分，它管理内存中的数据，并把适当的数据作为输出传输给（数据访问方法）。如果所需数据不在于内存中，缓冲管理器需要从磁盘读取对应的数据页到内存的数据缓存，然后把所需的数据返回给数据访问方法。这个过程中可能会出现一种等待状态PageIOLatch，关于等待状态将在第7章介绍。
8．数据缓存
数据缓存（Data Cache）是Buffer Pool中的最大部分，也可以说是SQL Server中占用内存最多的部分。它用于存放从磁盘加载到内存中的数据，以便后续操作使用。
对于当前数据缓存中每个数据库的缓存大小情况，可以使用一个DMV来查询——sys.dm_os_buffer_descriptors，代码清单3-1如下。
代码清单3-1查看每个数据库缓存大小
SELECT COUNT(*) * 8 / 1024 AS 'Cached Size (MB)' ,
CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE DB_NAME(database_id)
END AS 'Database'
FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id) , database_id
ORDER BY 'Cached Size (MB)' DESC
缓存中的数据并不是一直存放的，当数据很久没被使用，或者SQL Server感觉到有内存压力时，就会释放一部分数据缓存。在该过程中会调用一个叫Lazy Writer的进程，这个进程会把脏页（从磁盘加载到内存后有改动过的数据页）写入磁盘，并释放这部分的空间。如果读者对实例中每个库的脏页数量有兴趣，可以用下面的代码查看：
--SQL Server当前脏页数
SELECT DB_NAME(database_id) AS 'Database' ,
COUNT(page_id) AS 'Dirty Pages(KB)'
FROM sys.dm_os_buffer_descriptors
WHERE is_modified = 1
GROUP BY DB_NAME(database_id)
ORDER BY COUNT(page_id) DESC
如果想知道SQL Server保留数据缓存中的数据时间，可以查看性能监视器（第11章）中的MSSQL$<实例>:Buffer Manager\Page Life Expectancy计数器（PLE）的值。这个值也是判断是否有内存压力的指标之一，微软的建议值是不小于300秒。但是需要说明的是，微软的建议值多存在一定的误导性，比如这里就是一个：300秒是基于4GB内存的32位系统得出来的。对于拥有32GB内存的64位操作系统来说，国外的专家给出的建议是PLE的值不少于2400秒。
9．事务管理器
事务管理器主要包含两部分：锁管理器和日志管理器。
1）锁管理器通过使用锁机制来保证数据的并发性和隔离性，这部分在第9章介绍。
2）日志管理器通过预写入日志机制，保证事务的ACID特性（见下节）。
当需要修改数据时，数据访问方法会先到事务管理器中通过对事务日志进行操作，把请求传到Buffer 管理器中完成，然后返回给数据访问方法并通过事务管理器写入日志文件中。
SQL Server是一个复杂的系统，完全了解它的结构几乎不可能，但是每一步出现问题都将对稳定使用和性能带来比较大的影响。
3.2数据库事务
事务是对数据库操作的工作单元，这个单元可以是一个SELECT语句，也可以是包含多个SELECT、UPDATE、DELETE、INSERT等操作的命令集合。这个集合被认为是工作单元，SQL Server的很多特性都基于这个工作单元，也就是事务。
3.2.1事务特性
关系数据库包含4大核心事务特性：原子性、一致性、隔离性和持久性，简称ACID特性。下面分别介绍这4部分。
1．原子性
原子性（Atomicity）意味着一个事务内的所有操作必须全部完成或者全部回滚。典型的例子是在ATM机上操作时，要么取钱、存钱或转账成功，要么等于从来都没做过任何操作。如果转账成功但是对方未收到款项，势必造成很大问题。
2．一致性
一致性（Consistency）要求整个事务在运行的前后数据库的状态必须是一致的，比如库存不能为负值、性别字段的值要么为“男”要么为“女”等。为了确保事务的一致性，事务不能打破数据定义中的一致性约束。
3．隔离性
隔离性（Isolation）保证在同一时间中，一个事务的运行不能被另外一个事务所影响。但是SQL Server实际上是允许这个操作的。
4．持久性
持久性（Durability）意味着事务一旦成功提交，将永久存储到服务器的文件系统中（一般来说是磁盘文件），即使系统在中途崩溃，所发生的效果都不会丢失，这个会通过日志来保证。
3.2.2事务类型
SQL Server有4种事务类型，但是从根源来说，只有两种，一种是显式，一种是隐式，区别在于创建和提交的方式。隐式事务由SQL Server自己去开启和提交/回滚，并且在内部保证ACID特性；显式事务则以BEGIN TRAN/TRANSACTION开始，以COMMIT TRAN/TRANSACTION或者ROLLBAK TRAN结束。
提交事务意味着永久地把事务的影响记录到数据库中，回滚意味着把发生的所有操作去除，等于没有做过，不过日志其实还是会记录这个操作。从实践经验来说，建议使用显式事务。
3.3查询的生命周期
3.3.1SQL Server组件
先看看整个SQL Server查询生命周期中涉及的组件，如图3-2所示是抽象后的整个组件。
图3-2SQL Server组件
在图3-2中，关系引擎和存储引擎是SQLServer的两大组件。
1）关系引擎，也叫查询处理器，因为它的主要作用是优化和执行查询。关系引擎中包含的命令解析器用于检查语法和转换查询树，而查询优化器则用于优化查询，还有一个查询执行器负责执行查询。
2）存储引擎，负责管理所有数据及涉及的I/O。其中所包含的数据访问方法用于处理对行、索引、页、行版本、空间分配等的I/O请求，缓冲区管理器用于管理SQL Server的主要内存消耗组件——Buffer Pool，事务管理器将通过锁来管理数据及维持事务的ACID属性。
3.3.2缓冲池
缓冲池的英文是Buffer Pool，在数据库性能及管理领域中属于相当重要的一个组件，也是SQL Server中内存消耗最大的组件。缓冲池包含了SQL Server的所有缓存（cache），如计划缓存和数据缓存等。
3.3.3简单的SELECT 查询过程
通常对SQL Server的数据操作主要集中在查询和修改，其中，修改操作包含增删改等，而查询操作的出现频率，即使在修改操作相对频繁的系统中，也还是远高于修改操作。实际上，带有WHERE、FROM子句的增删改操作都是先进行查询再进行修改的。下面是一个简单的SELECT查询的运行过程。可以简单认为它只是：SELECT * FROM 一个表。
1．SNI
一个SELECT查询从客户端传入SQL Server，首先到达的地方是SQL Server 的网络接口，这个接口是一个建立在客户端和服务器之间的网络连接的协议层（SNI），包含了一系列用于连接数据库引擎和SQL Server Native Client（SNAC）的APIs。SNI替代了原有的SQL Server 2000中的网络库和MDAC（Microsoft Data Access Components），这部分需要在客户端和服务器上进行配置。在3.1节中曾提到，SQL Server总共有4种协议，这里对其中最常用的两种进行说明。
1）TCP/IP：最为广泛使用的协议，通过这种协议，可以使用特定的IP地址和端口连接SQL Server。默认情况下SQL Server的端口号是1433，而SQL Browser服务会使用UDP端口1434来查找命名实例。
2）Named Pipes：命名管道，为了局域网访问而开发的一个协议，在广域网中的速度不如TCP/IP。配置命名管道服务需要启动SQL Server 配置管理器，然后创建一个SQL Server别名，用于命名管道协议。命名管道使用TCP端口445来访问，所以使用这种协议需要确保这个端口的可连接性，特别是在防火墙配置中。
在整个SQL Server与外界交互的过程中，不管使用哪种网络连接协议，一旦连接建立，SNI就会创建一个安全的TDS端点到服务器上，用于接收和发送数据。
2．表格数据流端点
TDS现在是微软的专利，用于与数据库服务器交互。一旦建立连接，就会在服务器上创建一个TDS。SELECT语句会以TDS消息形式传输到SQL Server上。另外任何协议创建的端点都会有一个专用管理员连接（DAC）。
3．协议层
SQL Server的协议层在接收到TDS包后，就对其进行解压并分析包里面包含了什么请求。另外协议层也负责把SQL Server返回给客户端的数据打包成TDS包，通过SNI传输到客户端。当TDS包解压并分析成一个SQL命令后，就传入下一个组件——命令分析器中，如图3-3所示。
4．命令分析器
这个组件的任务是处理与T-SQL语言相关的事件。首先检查语法，如果发现有语法错误，就返回给客户端。如果没发现错误，下一步会产生执行计划或检查是否存在一个计划缓存。执行计划包含了执行一个查询所需的内部代码。前面提到过，计划缓存存在于Buffer Pool（缓冲池）中，如果在这一步找到已缓存的计划，就从计划缓存中读取，并传输给查询执行器执行。
图3-3协议层相关执行过程
在第8章中会提到，产生一个执行计划会消耗很多时间和资源，所以SQL Server会对一些特定查询的优化结果（也就是执行计划）进行缓存，以便重用（每个语句都有一个对应的sql_handle的hash值，用于标识这个查询，通过这个值可以得知这个查询和缓存中的查询是否是同一个或者是否可参数化），从而减少优化开销。而用于存放执行计划的地方就叫做计划缓存（Plan cache），这部分是缓冲池的一部分。如果存在对应的执行计划，就会重用，如果不存在，查询会被传送到查询优化器进行优化，如图3-4所示。
图3-4命令分析器执行过程
5．查询优化器
这个组件是SQL Server乃至所有关系数据库管理系统的核心。但是这部分的源码未公开，所以无法深入了解（在第5章有简要介绍）。SQL Server是基于开销的优化，通过一定的算法，分析特定的查询，产生多个（也有可能只有一个）候选执行计划，然后对比每个计划的开销，选出开销最小的计划作为最终执行计划。通常来说，开销最小的就是最佳的执行计划，但是也有可能由于某些因素的影响，使得优化器无法合理、准确地产生高效的执行计划，这部分在第5章“查询优化器”中详细介绍。
优化过程总共分3个阶段（在第5章还会介绍）。
1）阶段0——这个阶段不考虑并行操作，并且把关联全部当成Nested Loop来优化。如果计划的开销小于0.2，优化过程就会停止，并产生叫作TP（transaction processing）的执行计划。
2）阶段1——使用一定的规则，产生一定量的候选计划，当开销小于0.1，就会停止这个阶段的优化，然后产生叫作quick plans的执行计划。
3）阶段2——这是优化的最后一个阶段，会使用所有可能的方式去产生候选执行计划。
注
意 SQL Server中的开销并不能简单地转换成以秒为单位的时间或者其他衡量标准，它仅是一个用于执行计划的衡量值，每个操作符都有一个基线开销，如果这个操作符需要运行多次，会使用这个基线开销乘以总共影响行数，最终得到预估操作符的开销。由于开销并不十分依赖于硬件，所以执行计划的开销情况相对稳定，但是开销和SQL Server的版本差异有关系。
以聚集索引扫描为例，在这个操作中，假设聚集索引的第一行的开销是0.0001234（这个值只是举例，并不是真正的值），聚集索引其他行的开销是0.0000011。再假设表中有100行，那么这个操作的预估开销就是：0.0001234+(100-1)×0.0000011 。当然，我们无法得知真正的数据，所以不用过于在意这个值是多少。
6．查询执行器
这个组件的工作很明显，就是执行查询，它通过与存储引擎进行交互来操作数据。查询执行器把执行计划通过OLE DB接口传给数据访问方法（Access Method），如图3-5所示。
7．数据访问方法
数据访问方法是一个关于数据和索引，以及如何操作这些对象的代码集合。它包含了所有操作数据的代码，但是它不实际执行这些代码，而是传送给缓冲区管理（Buffer Manager）。
8．缓冲区管理
缓冲区管理用于管理缓冲池，如果查询需要从一个页中读取数据，缓冲区管理会在缓冲池（Buffer Pool）的数据缓存（data cache）中检查是否存在这些数据。如果存在，就把结果返回给数据访问方法；如果不存在，缓冲区管理就需要把数据页从磁盘读入数据缓存中，然后把结果返回给访问方法。
图3-5查询执行器的执行过程
除了重启或清空内存之后，数据必须经过这个步骤把数据加载到数据缓存之外，大部分数据库运行时间内应该保证最常用的数据能尽可能久地保存在数据缓存中，避免频繁地从磁盘加载数据到内存中。这个过程可能产生PAGEIOLATCH等待，对应的时间代表数据页从磁盘读入内存所需的时间。
9．数据缓存
数据缓存通常是占用缓冲池中最大的部分，所以也是内存中消耗最大的部分。可以使用sys.dm_os_buffer_descriptors这个DMV来检查当前每个数据库占用了多少数据缓存，代码如下：
SELECT COUNT(*) * 8 / 1024 AS 'Cached Size (MB)' ,
CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE DB_NAME(database_id)
END AS 'Database'
FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id) , database_id
ORDER BY 'Cached Size (MB)' DESC
数据驻留在内存的时间由最近使用策略（least recently used，LRU）决定。每个数据页都会存放最近两次被使用的时间，每次对数据缓存进行检查时，都会检查这两个时间，如果发现这两个时间已经超过SQL Server该部分的阈值（具体数据未公开），而此时SQL Server需要释放内存，那么就会选择这些数据作为优先释放的对象。
下面来简单总结一下一个T-SQL查询语句在SQL Server的生命周期。
1）通过特定的SNI（比如TCP/IP），建立一个TDS，用于连接服务器和客户端，并接受SELECT命令和返回该命令的对应结果。
2）SQL Server上的SNI解压TDS包，读取SELECT语句，然后传入命令解析器进行命令解析。
3）命令解析器从计划缓存中检查是否有可用的执行计划供传入的SELECT语句重用。如果没有，就通过查询优化器分析并产生经过优化的执行计划。
4）如果查询足够简单，优化器会产生一个简单的执行计划（Trivial plan）。如果查询比较复杂，优化器会经过3个阶段对查询进行优化。
5）在执行计划产生之后，会传入查询执行器，然后传入存储引擎中的数据访问方法。
6）数据访问方法通过缓冲管理器操作数据缓存中的数据，用于完成查询。
7）最后将结果按原路一级一级地返回，直到返回给客户端。
具体过程如图3-6所示。
图3-6T-SQL查询过程
3.4执行模型
这部分会在第7章中详细介绍。当一个应用程序（包括SSMS）成功与SQL Server建立联系之后，会有一个会话ID（session_id）与这个连接相关联，可以通过查询sys.dm_exec_sessions这个DMV来获取当前所有已授权的会话列表。当一个会话发出一个请求时，SQL Server会把这个请求拆分一个或多个任务（tasks），然后关联对应个数的工作者线程（worker thread），每个线程都会有如下3个状态。
1）running。一个处理器在某个时刻只能做一件事情，当一个线程正在一个处理器上运行时，这个线程的状态就为running。
2）suspended。如果在SQL Server的一个线程中进行协同操作的调度（Scheduler，后面提到）需要运行起来，而此时SQL Server没有足够的资源，那么线程会放弃当前占有的处理器，变成挂起（suspended）状态，等待这个协同操作运行结束。这种状态在SQL Server中也叫等待（wait）。
3）runnable。如果一个线程已经完成等待，但是还没有轮到它运行，就会变成runnable状态，代表已经准备好被执行。这种叫信号等待（signal wait）。
如果当前SQL Server已经没有可用的工作者线程，并且最大工作线程数还没达到，那么会分配一个新的工作线程。如果最大线程已经达到，那么这个任务会变成等待状态，这个等待类型叫作threadpool，直到有可用的线程为止。默认的最大线程数是基于CPU体系和逻辑核心数的（这在第2章已经介绍过）。下面是逻辑CPU格式在不同位数的操作系统中能支持的最大工作者线程数。
1）对于32位操作系统：
总可用逻辑CPU<=4时，最大工作者线程=256。
总可用逻辑CPU>4时，最大工作者线程=256+（（逻辑CPU-4）×8）。
2）对于64位操作系统：
总可用逻辑CPU<=4时，最大工作者线程=512。
总可用逻辑CPU>4时，最大工作者线程=512+（（逻辑CPU-4）×16）。
还有一个比较简单的方法可用于检查当前系统的最大线程数。比如执行下面的脚本：
SELECT max_workers_count
FROM sys.dm_os_sys_info
通常出现threadpool类型的等待意味着当前有大量并行执行计划，或者遇到了CPU瓶颈，但是不管是什么情况，都需要经常检查这部分的数据，以确保有足够的线程可用。对于每个工作线程，在64位系统中都要消耗2MB的内存，在32位系统需要消耗0.5MB的内存，所以SQL Server只会在需要的时候才创建工作线程。这部分的信息可以通过sys.dm_os_workers这个DMV来查看。
SELECT COUNT(*)
FROM sys.dm_os_workers
调度在SQL Server中显示为Scheduler。每个线程会与一个调度关联，SQL Server上可用的调度数量等于SQL Server上可用的逻辑CPU数量加上一个额外的专用管理员连接（DAC）。这部分的信息可以从sys.dm_os_schedulers这个DMV中查询。会话、任务、线程、调度和逻辑CPU之间的关系如图3-7所示。
由于Windows需要支持多种甚至所有应用软件，所以不会对上面的应用做出什么优先级排名，而SQL Server本质上和Office甚至画图软件一样，都只是Windows上的一个应用，所以SQL Server不会有多大的特权。因此SQL Server在协同操作过程中可能会被暴力地剥夺处理权，形成等待状态，这部分在第7章详细介绍。
3.5SQLOS
SQLOS最早是出现在SQL Server 2005中，它是一个底层的SQL Server的“专用操作系统”，用于管理调度、I/O争用、内存管理和其他资源协调等工作。这个组件是承接SQL Server和Windows的中间层，具有相当重要的作用。不过可惜微软对其依旧保密，我们只能从有限的资料中获取一些信息，这部分的信息可以从以sys.dm_os_开头的DMV中
查看。
1）Sys.dm_os_schedulers：每个调度对应一行，一个逻辑CPU对应一个用户调度，并且展示每个调度的负载和健康情况。
2）Sys.dm_os_waiting_tasks：返回每个正在等待资源的任务。
3）Sys.dm_os_memory_clerks：在SQL Server中，memory clerks用于分配内存。SQL Server有自己的memory clerk，这个DMV显示所有内存clerk的情况和每个clerk占用多少内存，这部分内容将在下节介绍。
3.6SQL Server 内存
内存、硬盘、CPU是数据库系统甚至整个操作系统的核心资源，三者缺一不可。内存可能是最重要的资源，因为内存的问题常常会引发CPU和磁盘问题，所以在通常的故障侦测过程中，检查内存是首要的任务（CPU和磁盘将在第4章介绍）。关于内存相关知识，单独出一本书都不为过，这里将挑选重点和常见问题来进行介绍。
SQL Server运行过程主要集中在内存中，因此下面分两个部分进行介绍，第一部分先了解Windows中的内存管理，因为内存资源首先是由操作系统来管理的，SQL Server的内存申请必须得到操作系统的同意，这样才可以得到资源。我们常说的内存实际上分为物理内存（Physical Memory/RAM）和虚拟内存（Virtual Memory）。第二部分是介绍SQL Server内部的内存结构，以及如何侦测内存问题。
3.6.1物理内存和虚拟内存
事实上，本节涉及的内容已经超出了数据库范畴，但因为SQL Server运行在Windows上，如果不清楚Windows是如何管理及使用物理内存和虚拟内存，在深入研究的过程中就会出现瓶颈。下面主要介绍Windows管理内存和应用系统（包括SQL Server）的运行情况。
1．物理内存
在Windows中，通过任务管理器可以看到有多少物理内存，而任务管理器也是通常用于查看当前资源情况的第一个工具。内存，实际上指的是RAM，但是也包含系统页文件（system page file）。RAM也叫主存或者系统内存，它能直接被CPU访问。RAM是能用于存储的最快的资源，但是易丢失，重启计算机之后其中的信息将会丢失。另外，内存的高价格也意味着不能滥用它。
内存的性能指标主要是GB/s和ns（纳秒），前者是吞吐量，后者是响应时间。磁盘的性能指标是MB/s和μs（微秒）。两者的差距是千倍。
2．虚拟内存
如果所有的应用都运行在物理内存中，那么物理内存将会很容易变成瓶颈，因为所有的进程都必须共享相同的地址范围。另外物理内存不能随便增加，所以需要高效使用资源。
Windows会授予每个进程一个虚拟地址空间（Virtual address space，VAS），通过VAS建立起应用程序和物理内存之间的桥梁。两个进程可以使用相同的VAS，VAS的大小取决于CPU的架构，64位系统可以使用0～16EB的空间，不过厂商通常会限制到16TB。其中8TB为内核模式（Kernel Mode），8TB为用户模式（User Mode）。换句话来说，SQL Server可以访问8TB的VAS。近年来，32位系统已经逐渐退出市场，所以本书主要关注64位系统。顺便说一下，32位系统仅支持4GB的VAS，它分为2GB的内核模式和2GB的用户模式。
3．虚拟内存管理器
虚拟内存管理器（Virtual Memory Manager，VMM），是Windows的一部分，负责管理所有物理内存，并在系统中所有需要内存的进程之间共享。VMM的工作就是在进程需要操作内存（读写）时，提供VAS。VMM通过管理每个进程的虚拟内存来实现该项工作。
为了实现管理，VMM会在必要时从VAS中回收物理内存。将驻留在内存中的数据存储到页面文件中，这样保证数据不会丢失。当进程再次需要内存时，VMM会从页面文件中查找数据，并在一块空闲内存中写入这部分数据，然后将新页面映射到需要操作的VAS中。如果这个过程发生延时或中断，就会成为页面错误，可以通过监控SQL Server实例的Process: Page Faults/sec计数器来查看是否存在过度的页面切换。
如果系统有足够的RAM，VMM只需要传输内存并在进程结束后清理内存；如果没有足够的RAM，VMM就需要使用页面文件来存储数据了，这会带来一定的额外开销。
4．定义页面文件大小
页面文件是磁盘文件。计算机使用页面文件来增加虚拟内存的物理存储地址。也就是说，当正在运行的进程使用的内存超过了可用的RAM，操作系统会使用一个或多个VAS把数据移动到物理磁盘的页面文件中，从而释放内存，用于其他用途。前面曾提到过，页面文件有一定的性能问题，所以应尽可能地保证SQL Server只使用物理内存。在默认情况下，Windows会维护相当于RAM1.5倍大小的页面文件。这个页面文件的主要目的是提高数据从磁盘移到内存中的效率。如果担心过大的页面文件会影响性能，可以考虑把页面文件设为可用RAM的50%。
另外，有时候SQL Server和操作系统可能没有很好地共享可用物理内存，这时可能会看到有关虚拟内存不足的系统警告。对于这种情况，最佳方法是增加RAM、重新配置SQL Server的内存，或者增加页面文件的大小。
5．NUMA
NUMA指的是非一致性内存访问（non-uniform memory access），另外一种系统体系结构是SMP（对称多处理器），两者的主要区别是将内存连到哪里以及如何在系统总线上排列处理器。在SMP中，内存通过一条共享总线对称地连接到所有处理器；而在NUMA中，每组处理器有自己的“本地”内存池。这个优点是只要所需的数据存在于本地内存池中，每个处理器就不需要访问太远的内存，但是如果数据不在本地，则开销会略高于SMP。所以在使用NUMA时，应该尽量保证数据存放在本地内存中。
如果CPU插槽比较充足，可以使用每4个插槽一组，作为一个NUMA节点。这个数量是可以调整的，但是随着节点数增多，数据存放在本地内存的几率就会减小，而且管理节点的开销也会随之增大。SQL Server会在启动时创建自己的内部节点，用于映射NUMA节点。
6．SQL Server NUMA CPU 配置
可以使用sys.dm_os_schedulers来直接查看SQL Server的NUMA配置。例如下面的查询在笔者计算机上的结果如图3-8所示。
SELECT scheduler_id ,
cpu_id ,
parent_node_id ,
status
FROM sys.dm_os_schedulers
图3-8中这个结果代表笔者计算机上有4个逻辑CPU（cpu_id），但是没有做NUMA（parent_node_id只有0和64），而64是DAC专用的，每个调度都会有一个。
3.6.2SQL Server 内存
SQL Server的内存管理一共分3级，最底层是内存节点，是SQL Server内存的最低级分配对象；第二级是Memory clerks，前面提到过，它是用于访问内存节点和缓存存储的，主要用于缓存；顶级节点是内存对象，只有clerks可以访问内存节点并分配内存，所以所有需要分配内存的组件都会创建自己的memory clerks。
1．内存节点
内存节点直接映射到NUMA节点，可以通过sys.dm_os_memory_nodes来查看服务器的使用情况。如果没有做NUMA，那么memory_node_id为0。
每个内存节点有自己的memory clerks和缓存，它们平均分布在所有节点中。SQL Server的所有内存使用量就是所有节点的总和。
2．memory clerks和 Buffer Pool
memory clerks是用于内存缓存的一种机制，Buffer Pool是SQL Server内存中的最大消耗者。
（1）memory clerks
当一个对象需要分配内存时，都是通过memory clerks来实现的，SQL Server的Buffer Pool有自己的clerks（MEMORYCLERK_SQLBUFFERPOOL），而执行计划也有自己的clerks（MEMORYCLERK_SQLQUERYPLAN），这两类clerks对内存问题的侦测很有帮助，可以通过Sys.dm_os_memory_clerks这个DMV来查看相关详细信息。
SELECT [type] ,
memory_node_id ,
--page_size_bytes,--2012之前字段
--pages_kb ,--2012字段
virtual_memory_reserved_kb ,
virtual_memory_committed_kb ,
awe_allocated_kb
FROM sys.dm_os_memory_clerks
ORDER BY virtual_memory_reserved_kb DESC;
（2）Buffer Pool
在Buffer Pool中，包含和管理SQL Server的数据缓存，可以从sys.dm_os_buffer_descriptors中查询相关信息，代码清单3-1中就用这个DMV查询数据库的缓存情况。
监控SQL Server的Buffer Pool是检查内存压力时非常重要的手段。通常可以检查下面的性能计数器：
1） MSSQL$<instance >:Memory Manager\Total Server Memory (KB)：Buffer Pool的大小。
2）MSSQL$<instance >:Memory Manager\Target Server Memory (KB)：表示空闲的Buffer Pool大小。Total和Target的值应该尽可能相同，如果Total明显小于Target，可能意味着出现了内存压力，需要更深入地研究。
3）MSSQL$<instance >:Buffer Manager\Page Life Expectancy：代表着一个数据存在于Buffer Pool的时间。这个值越长越好，最低时间应该设置为300s。
（3）执行计划缓存（Plan Cache）
生成执行计划是花时间较多且资源开销较大的操作，所以合理地缓存执行计划是明智的选择。可以通过DMV sys.dm_exec_cached_plans来查询计划缓存的信息。比如下面的脚本可以查看当前已缓存的计划数及所占的MB数。
SELECT 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
3.6.3内存问题诊断
遇到内存问题时，首先要检查SQL Server的内存使用情况，可以使用性能监视器或者DMV来查看。通过性能监视器可以监控下面的计数器。
Granted Workspace Memory (KB)，当前查询正在使用的总内存大小。
Maximum Workspace Memory (KB)，SQL Server标记预留给某个查询的总内存大小。
Memory Grants Pending，正在队列中的内存grants数量。
Memory Grants Outstanding，正在使用的内存grants数量。
这里说明几个术语。
Workspace：指在查询过程中，进行hash和排序操作时，临时存储数据的结果集所用的内存。如果在执行计划中看到出现了hash或者sort操作符，那么就表示会使用这部分的内存完成处理。
Memory Grants：已经分配给查询的那部分内存。可以用过sys.dm_exec_query_memory_grants查看。
另外，RESOURCE_SEMAPHORE等待状态是针对memory grants的，所以如果在sys.dm_os_wait_stats这个DMV中看到这个等待类型存在很久，并且处于前几位，可能需要检查内存是否足够快。如果内存授予（memory grants）太久，会导致查询超时，可以使用SQL trace或者执行计划查看是否存在hash 或者sort warning这些信息。
3.6.4优化SQL Server内存配置
1．最小和最大服务器内存
这两个配置用于控制SQL Server可用内存的大小。对于最小内存，在SQL Server服务启动时，不会马上达到这个设置值，而是仅使用最小的需求内存，然后按需增长，一旦增长到最小内存设置值时，SQL Server将不会再释放内存。最大内存用于设置内存使用的上限，可以使用SSMS或者sp_configure来配置。需要提醒的是，这里的“最大内存”实际上指的是Buffer Pool，在64位系统中，如果没有控制好内存而导致空闲的物理内存不足，会引起Windows削减SQL Server的工作集。
如何计算合适的最大内存？可以参考以下信息。
（1）监控SQL Server的最大内存使用情况
可以通过性能监视器的MSSQL$<instance>:Memory Manager\Total Server Memory (KB)计数器来监控SQL Server总的Buffer Pool使用情况。如果SQL Server所需的物理内存超过了现有的可用数量，这个值就会降低，而在释放内存后，这个值则会升高。可以在一开始把这个值设置得低一点，然后通过监控来适当地进行调整。
（2）SQL Server潜在的最大内存使用
在考虑潜在使用时，很重要的一点是对连接服务器和外部存储过程的调用，在后期开发中，这部分内容可能会非计划地加入。一般来说，每个线程会使用0.5MB（32位）或者2MB（64位）的内存，还要保证大概有512MB可以用于这部分的使用。
另外，一些大型企业可能会使用第三方备份软件、杀毒软件等，这些也会影响SQL Server的内存使用。最好预留1～3GB的内存给这些软件使用。
2．检查最大内存配置是否合理
在搭建新服务器时，该怎么去决定配置的最大服务器内存是多少呢？最直接的答案是：从低开始设置，然后进行周期性监控（开始时监控周期要比较短，以便尽快发现问题），并按需调整。或者使用性能计数器来监控，比如通过MSSQL$<instance>:Buffer Manager\Page Life Expectancy (PLE)和Memory\Available Mbytes来监控。
PLE计数器用于表示SQL Server的数据缓存在内存中的时间，在理想情况下该时间越长越好，这是内存压力指标之一。如果小于300s，就要检查指标Available Mbytes了。
Available Mbytes表示Windows上当前有多少物理内存没有被使用。国外专家建议的标准是最少保留100MB。当然不要把最低标准当作最低配置，应尽可能保证有GB级别的可用内存。
如果PLE很低，但是可用内存数很高，那么应该调高最大服务器内存，因为这样可以增加PLE的时间。相反，如果可用内存很低，但是PLE很高，那就需要降低最大内存配置来释放内存给Windows。下面是部分配置示例。
最大服务器内存30GB，服务器有32G RAM，PLE平均值为10000，可用内存为90MB，那么最少降低500MB最大服务器内存。
最大服务器内存46GB，服务器有50G RAM，PLE平均值为10，可用内存为1500MB，那么应提高最大服务器内存500～1000MB。
最大服务器内存60GB，服务器有60G RAM，PLE平均值为50，可用内存为20M，那么应降低100MB最大内存，或者增加更多的RAM（最直接的方法）。
3．锁定内存页
锁定内存页（Lock Pages in Memory，LPIM）是在Windows和SQL Server之间发生问题时的应急处理方案，在旧版本（比如Windows Server 2003及以前版本）中，效果不佳。如果在操作系统上没有充足的物理内存可用于支持其他请求，会强制回收某些应用的内存。这对SQL Server的内存分配是毁灭性打击。可能会在SQL Server的错误日志中看到类似的话。
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 1086400, committed (KB): 2160928, memory utilization: 50%.
从Windows Server 2008开始，这个现象有了明显的改进，但是还会出现。对于这类情况，可以把最大服务器内存调整到适当的大小，保证Windows有足够的内存来运行其他应用。另外也可以使用SQL Server来锁定内存页。
如果启用了LPIM，SQL Server的Buffer Pool页会被“锁定”，并且不允许操作系统强行收回。在页被锁定之后，这部分空间将不会算入可用内存中。但是只有SQL Server的Buffer Pool 分配的页才可以被锁定，操作系统依旧可以收回其他内存，包括SQL Server依赖的进程内存。
如何启用锁定内存可以参考http://support.microsoft.com/kb/2659143。
3.6.5优化Ad-Hoc 工作负载
执行计划生成后会存储在plan cache中，以便重用，如果计划缓存从来都没有被重用过，将会造成内存资源的浪费，这有可能是由于非参数化的Ad-hoc（即席查询）引起的。当执行代码时，会产生一个hash值，用于匹配计划缓存中的hash值，相同的hash值代表语句是相同的。如果执行一个存储过程，会按照存储过程名来创建hash值，如果在存储过程之外执行代码（Ad-hoc T-SQL），那么hash值会根据整个语句产生。你的代码有一点点字面上的改变，都会产生不同的hash值，导致计划无法重用。当有大量Ad-hoc执行时，会导致计划缓存的膨胀。
针对这类问题，可以考虑使用存储过程、函数或者参数化Ad-hoc，但是有时候的确没有办法，必须使用非参数化的Ad-hoc。从SQL Server 2008开始，引入了一个“针对即席工作负荷进行优化”的选项，如图3-9所示。
找到该选项的具体步骤是：右键实例，然后选择“属性”，再选择“高级”，之后把图3-9箭头处的False改成True。下面是针对该选项的官方解释：
图3-9修改针对即席工作负荷的优化选项
“针对即席工作负荷进行优化”选项用于提高包含许多一次性临时批处理的工作负荷计划缓存的效率。如果该选项设置为True，则数据库引擎将在首次编译批处理时在计划缓存中存储一个编译的小计划存根，而不是存储完全编译的计划。在这种情况下，不会让未重复使用的编译计划填充计划缓存，从而有助于缓解内存压力。
编译的计划存根使数据库引擎能够识别此临时批处理以前已经编译过，但只存储了编译计划存根，因此当再次调用（编译或执行）此批处理时，数据库引擎会对此批处理进行编译，从计划缓存中删除编译计划存根并将完全编译的计划添加到计划缓存中。
将“针对即席工作负荷进行优化”设置为 1 只会影响新计划，而已在计划缓存中的计划不受影响。
编译计划存根是 sys.dm_exec_cached_plans 目录视图显示的 cacheobjtype 之一。它具有唯一的 SQL 句柄和计划句柄。编译计划存根没有与其关联的执行计划，并且查询计划句柄不会返回 XML 显示计划。
可以用下面的脚本来查看缓存对象的对应内存数。
SELECT objtype AS 'Cached Object Type' ,
COUNT(*) AS 'Number of Plans' ,
SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS 'Plan Cache Size (MB)' ,
AVG(usecounts) AS 'Avg Use Count'
FROM sys.dm_exec_cached_plans
GROUP BY objtype
在笔者计算机上的结果如图3-10所示。
图3-10缓存对象的对应内存数
在没有开启上面选项的系统中，Ad-hoc通常是内存占用最多的部分。所以从SQL Server 2008开始，建议开启这个选项。
3.7小结
本章介绍了SQL Server运作的生命周期及一些重要的术语，作为后续介绍的铺垫。要重点注意图3-1，在不知道SQL Server何处出现问题时（主要性能方面的问题），这个图是很好的指导。可以根据数据的流动方向一步一步地进行侦测和优化。
![SQL Server 性能优化与管理的艺术[按需印刷]](http://images.china-pub.com/ebook3770001-3775000/3770535/zcover.jpg)
