IT书讯 -- Inside Microsoft SQL Server 2005 Query Tuning and Optimization

如何提升 SQL Server 2005 的查询效能

 

无可避免的效能问题
效能调教对信息系统一直是个大问题,不管是一般大众日常接触的电信交换、门诊、金融 / 证券交易、售票 等公众系统,到企业内的电子邮件、 ERP CRM 、在线分析 等,系统多到不可胜数,其使用的 IT 技术五花八门,牵涉的领域知识庞大博杂。我们不得不戒慎恐惧,多方观察与测试研发中的系统,上线后,依然要详加记录与监控,如此才能防微杜渐,避免突如其来的效能问题拖垮系统。
整体而言,好的设计架构至为重要,让系统可以切割,以平行扩充 / 分散负载。而这需要设计者同时熟稔技术和领域知识,且曾做过相同的设计。由于系统庞大的复杂度,若没有之前的设计经验,则必定犯错。但事实上,我们十之八九会设计或撰写出不佳的模块。接受这在 IT 研发上是常态,勇于尝试并能立刻认错,愿意翻掉失当的部分,迅速改正而不要勉强在错误的设计上,以违章建筑的方式修补。系统才可以在一次次修正中,缔造出好的稳定性、效能与扩充性。
然而,不可避免地,系统各环节的技术各自演变,前端界面、中间应用层和后端基础服务服务器的技术不断创新,企业与承包商的开发团队持续地走马换将,在系统中留下了大量的黑盒子。当你尝试翻开一个个黑盒子时,前人曾下过的巨大工夫成了浓密的迷宫,让你只想关上盒子。在盒子外,透过各种追踪工具检验盒子的 I/O 。这把立体的应用程序盒子碎成一句句的 SQL 语法,一个个网络封包,没了整体观,仅能靠 80/20 定律来抓耗掉 80% 资源的 20% 语法或动作。最不幸的是找不到关键性的瓶颈,又无法分散负载 / 平行运算, Scale Up/Out 都失效。那只有再进到盒子内,从各细节中,一点一滴抠出效能来
效能调教需要知识、经验、耐心、细心、优质团队
就曾参与过的诸多效能调教案子所累积的经验,我所得到的综合结论是:要做好效能调教需要知识、经验、耐心、细心、优质团队。当系统效能不好时,决不要妄加猜测瓶颈点 [1] ,一定要善用各种软件工具让数据说话,这需要团队合作,因为没有人能包山包海地了解各种技术。
若想要做好效能调教,团队成员平常需在自己的岗位上,累积广泛且深入的知识和技术。因为在多数的大型系统中,其效能瓶颈点都不会显而易见,且不只一个瓶颈点。所有的表象都是各元素互相影响后的综合行为,你需要小心翼翼地抽丝剥茧,找出系统的最大瓶颈点,而不可骤下定论。要能深入探究问题,找出问题核心,并建立测试模型证明该问题是最大瓶颈点。让问题清晰明白后,就算自己无解,还能求教于他人。
在此介绍数据库的专业人员一本好书:Inside Microsoft SQL Server 2005 Query Tuning and Optimization,Kalen Delaney 等着,Microsoft Press 出版。 等了好久,终于等到 Inside SQL Server 2005 系列的第四本,也是最后一本书。其内容保有该系列一贯的深度,延续了重要概念都由参与研发该功能的微软人自己写,并延请有使用 SQL Server 十数年经验,并从事教学、顾问工作的人执笔广泛内容之章节 [2]。本书的主题围绕在索引、执行计划的产生与重用,交易锁定以及使用工具、分析问题等,是本进阶技术书籍。
若要为 SQL Server 做效能调教与优化,恐怕需要熟悉的是整个系列,而非单此一本。日常有空时,多翻翻它们,在系统出状况时,才能看得出蛛丝马迹,否则只能呆在服务器前束手无策。
书籍内容
本书共分六个章节,主要的叙述模式为成因 -> 观察 -> 解法。也就是先让你了解某项技术对于系统效能上的好处,而又有哪些因素会妨碍该技术的施行,你需要透过什么方式可以观察状况变差了,又如何排除这些因素。
第一章介绍调教效能所需的基本知识,如软硬件的基本设定与限制、应观察性能计数器、系统动态管理检视 (DMV) ,以及交易锁定等等。本章列出了较影响效能的机制,如何透过相关性能计数器知道是否达到其极限,并以其他的机制,如 DBCC DMV Profiler 等,左证其效能计数值的意义,让读者可以知其然也知其所以然。
第二章教你如何使用 Profiler 来追踪 SQL Server 所发生的各种事件,以及要有何种权限的登入者才可以启动或检视追踪。并介绍如何透过撰写预存程序,触发自定义事件,埋在我们自行开发的商业逻辑中,用以追踪不定时发生的问题。 Profiler 是追踪与分析 SQL 语法最重要的工具程序。笔者以自身参与的诸多效能调教案子为例,若不了解系统的领域知识与软硬件架构的来龙去脉,通常都是从性能计数器、事件查看器、 Profiler SQL Server 自身的 Log 入手,也就是本书前两章介绍的内容。
前两章都是与效能调教相关的基本知识,并不困难。在第三章中,详尽的解说执行计划,这一章的难度就大幅增加。它说明了常用查询的运作方式,例如扫描 (Scan) 和搜寻 (Seek) 数据、链接 (Join) 、汇总、子查询 (Subquery)… 等等,藉由解释其执行计划的形成和运算符的逻辑,来探就数据库核心引擎运作的方式。而 SQL Server 执行效能的好坏,很大一部分取决于使用者写作 SQL 的技巧,是否发挥了 SQL 语法和函数的特性,以及查询引擎 (Query Engine) 是否判读到正确的统计信息,做出有效的查询计划。
由于需要透过执行计划的细节来深入研究,因此本章充斥着一句范例 SQL 查询,接着是该段查询的文字描述执行计划。逐范例解释,再在范例中逐行分析,这让读者容易丧失阅读的趣味与耐心。或许先浏览后,日后再随需要重读与精读吧。
第四章主要谈的是索引、统计、提示 (hint) 。如何有效建立索引, SQL Server 又如何正确判读,恰到好处地使用索引。抑或是透过提示来改变 SQL Server 执行计划,以验证与测试数据库对象架构的设计与执行计划之优劣。
第四章说明的是执行计划的快取、重用与否,以及 SQL Server 如何重新找寻和编译执行计划。由于替 SQL 语法找执行计划很耗时间与 CPU 资源,因此, SQL Server 会自动做执行计划快取。但若用户撰写应用程序存取数据库时,习惯采用 ad-hoc 查询,或是服务器的内存不足,乃至于数据分布不均匀 等等,都会导致无法有效使用执行计划快取。本章说明执行计划快取的运作原理, SQL Server 找寻与重用快取的执行计划之流程,不利执行计划快取的因素,以及观察和应变之道。
最后一章讨论的是交易、锁定与死结。一直以来,这是笔者处理效能问题的主要议题之一,只要系统有众多用户,又需考虑数据完整性,就逃不掉要深入研究交易和锁定。读 / 写分开、不要有大型的交易、交易时间越短越好,避免提升交易层级等是基本认知。本章除了强调这些基本认知与应有的做法外,也深入解释了死结的成因、监控与避免方式。另外,本章最后深入说明了使用 SQL Server 2005 新增的 Read Committed Snapshot/Snapshot Isolation Level 应注意之事项,探讨了一般较少见的主题。
我们恐怕无法一口气读完本书,初次阅读时,会难以理解作者所解释的许多观念 [3] ,这需要大家耐心地重读。另外,最好伴着 SQL Server 2005 Management Studio ,并安装 Nothwind Northwind2 等范例数据库 ( 可在该书所附的网址 http://insidesqlserver.com/companion/ 下载,并执行其 SQL Script 以建立范例数据库 )
最后,在本书中引用了大量的 DMV/DMF 等系统对象来解释各种行为,这是 SQL Server 2005 所新增的功能,但面对 DMV 提供多如牛毛、彼此相关但各 DMV 又彼此独立的状况,让人感觉如同要把英文学好,但老师却给了本英文字典叫我回家背。期待微软在未来可以给我们统整这些 DMV/DMF 的工具,它自行广泛搜集数据后,直接提供效能调教的建议。


[1] 以自己的经验,大家找我去做效能调教的出发点通常是怀疑 SQL Server 效能不好。但很多次我都是在证明「猜错了」。
[2] 此书系的列名作者共有 9 位,而每本书的书序还感谢一大堆提供建议、校稿的微软工程师与 SQL MVP,再加计出版社本身的系列编辑、技术校阅和文字编辑…等,看来 Inside SQL Server 2005 系列丛书在 SQL Server 相关书籍中,立了一个参与专家人数的标竿 J。
[3] 我在阅读本书时,常常发现作者一笔带过他认为的基本知识,但自己遍寻不着参考数据。只好等待缘份,期待埋下「每有会意,便欣然忘食」的机会 L
 
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页