作者：黄钊吉
出版社：机械工业出版社
格式: AZW3, DOCX, EPUB, MOBI, PDF, TXT
SQL Server性能优化与管理的艺术试读：
前言
为什么要写这本书大学时我就已经开始接触SQL Server了，而真正将它用于工作也有6个年头了。不管别人对它的评价如何，我依旧深爱着它。工作之后，接触到的SQL Server性能问题越来越多，也让我越来越着迷。后来我成为了专职的DBA，对性能问题的探索也从兴趣变成了工作职责。
这几年来一直在学习SQL Server，看了很多有关书籍，可真遇到性能问题的时候，还是一下子就蒙了，望着书架上那几十本书，不知该从哪里找类似的问题。其实很多人都有类似的经历，在看到CPU利用率很高、内存占了很多、整个SQL Server很慢时，常常不知道该怎么着手找到根源并处理。这也就成了我写这本书的一个初衷，我希望给出一个方法论，并给出一些后续学习的“大纲”，使读者运用书中的知识，去处理工作中遇到的绝大部分性能问题。
关系数据库发展到今天，各个关系数据库管理系统之间的差异已经很小，只是实现方式可能有所不同。基于这种情况，对于DBA来说，更重要的不是如何操作，而是掌握一个方法论和思路，有了方法论和思路，不管是对Oracle还是MySQL，或者其他数据库管理系统，都可以实施优化，解决性能问题。找到问题的根源，处理问题，然后再检查、再处理，不管是数据库性能优化还是其他行业、领域的问题侦测，这个流程都是可以直接套上去或者稍作修改即可使用的。这也是本书的一个特色，不仅告诉读者SQL Server相关知识，还提供了基本方法和思路。
SQL Server是世界级企业数据库领域的拳头产品，想要透彻了解它的每一个细节几乎不可能，但是我们即使不知道源代码，也可以很好地利用SQL Server来实现业务需求。我基于个人的工作经历进行写作，并且参考了大量国内外资料，以实用为目的来介绍知识。书中涉及的知识点较多，其中一些展开足以独立成书。所以，这里更多的是给出一个“提纲”，让读者知道“有这个知识点”，如果读者有兴趣深入了解，可以去学习相关的专门资料。读者对象
作为一本非入门书籍，本书假定读者已经有了一定的SQL Server基础和经验。本书主要面向的读者群体如下。
·初中级SQL Server DBA
·中高级SQL Server相关程序员
·使用SQL Server的项目负责人
·SQL Server爱好者
·对关系数据库性能优化有兴趣的读者
·有志成为DBA、数据库设计、数据库架构的IT从业人员如何阅读本书
本书分为以下3部分。
第一部分是概述部分，介绍一些与性能相关的概念。
第二部分是与SQL Server性能相关的一些理论知识，为后续的使用打下基础。
第三部分主要是工具的使用，在了解了相关知识之后，就要学会如何去用。作为一直以界面友好著称的微软产品，SQL Server同样提供了很多快速定位问题甚至直接指出问题的工具，供开发人员使用。
如果你是一名有少许经验的初学者，建议按顺序学习本书。如果你已经有一定的基础，可以从第二部分开始学习。另外，本书也可以作为开发人员外理工作中常见问题的一个“手册”。勘误和支持
由于作者的水平有限，编写的时间也很紧迫，书中难免会出现一些错误或者不准确的地方，恳请读者批评指正。如读者发现有错误或者有疑问，欢迎发邮件到本人邮箱：huangzhaoji@hotmail.com，或者在本人的微博上留言，微博地址：http://weibo.com/u/3187132617。书中的一些重要脚本或者篇幅比较长的脚本将发布在机械工业出版社的华章公司网站（www.hzbook.com）上供读者下载，很期待能够听到读者们的真挚反馈。致谢
我首先要感谢我的家人，特别是我的未婚妻李琳玲。写书占用了我几乎所有的休息时间，陪家人的时间基本上变成了“0”。但是他们一直理解我，并在工作和生活起居上为我打理一切。他们的辛勤付出为这本书的撰写创造了良好的环境。
然后我要感谢机械工业出版社给了本书出版的机会，特别感谢机械工业出版社华章公司的编辑杨绣国老师，感谢她在成书过程中对我的全力支持。也因为出书，逼自己学习大量的知识，使自己能在短时间内，不管是在知识上还是在写作水平上有了明显的提高。
感谢SQL Server圈子中的很多高手，特别是MVP宋沄剑、张骞、林勇桦、王成辉、陈畅亮、蔡传雄，还有某电商DBA侯亚俊，某公司的CIO矫正，以及这个仓促写就的名单之外的更多朋友们，感谢你们长期的支持和帮助。
谨以此书，献给我最亲爱的家人，以及众多热爱SQL Server的朋友们！
SQL Server MVP黄钊吉第一部分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所示。图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.4 T-SQL代码
T-SQL是操作数据库的工具之一。大部分的性能问题都是编码引起的，所以编写高效、可维护的代码，对性能的影响不可估量。在面对大数据集时，选用面向集合的T-SQL操作数据库比使用面向过程的游标处理，效率上也高出很多个数量级。另外，除了性能上的考虑，也要考虑T-SQL编码规范，如果拥有编码良好的T-SQL，即使出现性能问题，也能很快地定位。本人经常要对一些两三千行没有注释甚至注释是错误的存储过程代码进行优化，这种工作非常辛苦。
T-SQL如何写才好？没有绝对的答案，需要经验和知识。在本书中会看到，一些简洁的查询并没有想象中那么高效。不过还是有一些建议可用，比如尽量减少需要处理的数据量，以及使用SARG写法等。
1.4.5 硬件资源
万丈高楼平地起，硬件就是数据库的“地”，地不稳，高楼也别想建得稳。硬件资源不足或资源得不到合理的利用，都将影响数据库的性能。相关的内容将在第4章做详细介绍。
如果应用程序运行在与工作负荷不匹配的硬件上，即使设计得再好，也不能提高什么性能。比如，一个100TB的库运行在只有2GB的32位Windows Server上，不出问题才怪。
同时，设计问题也可能反过来导致硬件瓶颈。如果资源充足但是没有合理利用，也会表现出硬件问题，而简单地升级硬件只是一个权宜之计。
可以说，性能问题的表现就是资源问题。
1.4.6 SQL 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个数与工作线程的关系
每个工作进程在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-1 性能优化步骤2.2 定义问题
在出现问题时，有了冷静的态度之后，关键就是要定位问题了。这里假定你已经可以区分是否存在性能问题。
如何定位问题？答案就是通过当前现象和日常、临时收集的数据进行分析。这种收集是通过大量工具收集并汇总来实现的。常见的定位工具如下。
·性能监视器，在第11章介绍。
·动态管理对象（DMO，SQL Server 2005才出现），贯穿全书。
·SQL Profiler/SQL Trace，在第12章介绍。
·PowerShell，在第12章介绍。
·Extended Events，在第13章介绍。
其他工具将在第14章介绍。可能在本书编写的过程还会出现部分工具而本书未介绍到，如果读者想要进一步了解，请参考相关资料和书籍。
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_timeFROM 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.1 SQL Server查询体系
图3-1是一张被广泛使用的图，这个图展示的是一个用户从发出请求到得到SQL 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。而且，由于本机使用了共享内存协议，所以不需要做任何配置就可以连上。这个协议仅用于检查连接问题。
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_descriptorsGROUP BY DB_NAME(database_id) , database_idORDER 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_descriptorsWHERE is_modified = 1GROUP 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.1 SQL Server组件
先看看整个SQL Server查询生命周期中涉及的组件，如图3-2所示是抽象后的整个组件。图3-2 SQL 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所示。图3-3 协议层相关执行过程
4．命令分析器
这个组件的任务是处理与T-SQL语言相关的事件。首先检查语法，如果发现有语法错误，就返回给客户端。如果没发现错误，下一步会产生执行计划或检查是否存在一个计划缓存。执行计划包含了执行一个查询所需的内部代码。前面提到过，计划缓存存在于Buffer Pool（缓冲池）中，如果在这一步找到已缓存的计划，就从计划缓存中读取，并传输给查询执行器执行。
在第8章中会提到，产生一个执行计划会消耗很多时间和资源，所以SQL Server会对一些特定查询的优化结果（也就是执行计划）进行缓存，以便重用（每个语句都有一个对应的sql_handle的hash值，用于标识这个查询，通过这个值可以得知这个查询和缓存中的查询是否是同一个或者是否可参数化），从而减少优化开销。而用于存放执行计划的地方就叫做计划缓存（Plan cache），这部分是缓冲池的一部分。如果存在对应的执行计划，就会重用，如果不存在，查询会被传送到查询优化器进行优化，如图3-4所示。图3-4 命令分析器执行过程
5．查询优化器
这个组件是SQL Server乃至所有关系数据库管理系统的核心。但是这部分的源码未公开，所以无法深入了解（在第5章有简要介绍）。SQL Server是基于开销的优化，通过一定的算法，分析特定的查询，产生多个（也有可能只有一个）候选执行计划，然后对比每个计划的开销，选出开销最小的计划作为最终执行计划。通常来说，开销最小的就是最佳的执行计划，但是也有可能由于某些因素的影响，使得优化器无法合理、准确地产生高效的执行计划，这部分在第5章
试读结束[说明：试读内容隐藏了图片]