优化SqlServer查询性能02 - 大纲.doc

优化SQLServer查询性能-02刘豹0 参考书籍SQL Server2005技术内幕：查询、调整和优化SQL Server2005技术内幕：存储引擎SQL Server2005技术内幕：T-SQL查询SQL Server2005性能调校Dissecting+SQL+Server+Execution+Plans1 SQL Server 性能诊断和优化全局简介&本次分享的假设场景SQLServer分享资料 1.0SQL Server 性能诊断和优化全局.mmap2再说计划缓存2.1 SqlServer的内存：计划缓存(Cache)、数据缓存(Buffer)、SqlServer引擎本身命令：DBCC memorystatus DEMO1：了解SQL Server内存的各种组成DBCC memorystatus -返回文本便于查看命令详细：/kb/907877/EN-US /kb/271624/zh-cn其他资料：/questions/2810781/how-to-analyze-dbcc-memorystatus-result-in-sql-server-2008通过DBCC命令我们可以了解SQLServer内存的使用详细情况，同时也便于精细分析一条问题SQL的内存分配、变化和情况。这个命令主要用于深入研究SQL Server的内存分配行为，比如结合SQL语句详细执行计划分析物理查询操作对内存的使用特点。在实际开发中，并不需要对此命令过多关注。2.2 回顾冷、热查询&基本IO,Time冷查询：没有缓存的SQL语句,这里缓存指数据缓存Buffer热查询：存在数据缓存的查询DEMO2：查看IO,Time的各种指标，理解冷、热查询的区别0冷热查询基本IO，Time时间.sql 这个DEMO说明了执行时间作为衡量时间比较不靠谱，即使cost很大的查询，在热查询的情况下，时间也可能花费很少。仔细观察输出发现只有逻辑IO是固定不变的，所以逻辑IO是衡量SQL语句优化的核心指标。* 注意：逻辑IO是衡量单个SQL语句是否优化的核心指标。但在实际生产环境面对具体问题的场景中，有极少数情况，可能需要更加优化时间、物理IO等等其他指标。2.3关于计划缓存的核心问题2.3.1 SQL提交方式、对应计划缓存类型SQL客户端提交SQL语句的时候，主要提交形式分为以下几种：Ad-hoc查询、EXEC SQL、EXEC Proc、sp_executesql sql 参数 参数值。ADO.NET客户端的主要提交方式映射到SQL客户端Ad-hoc查询、sp_executesql、EXEC Proc。2.3.2 缓存利用率，缓存大小SELECT *FROM sys.syscacheobjects SELECT stats.execution_count AS 执行次数 , p.size_in_bytes AS 大小:字节数 , sql.text AS 执行计划对应SQLFROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text(p.plan_handle) sql JOIN sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle F1&google&msdn是最好的老师：sys.dm_exec_cached_planssys.dm_exec_sql_textsys.dm_exec_query_stats stats2.4各种类型计划缓存DEMODEMO3：Ad-hoc查询，计划大小1研究计划缓存01_Adhoc.sqlDEMO4：参数化查询DEMO、EXEC查询DEMO、sp_executesql DEMO2研究计划缓存02_参数化查询.sqlDEMO5：ADO.NET对应缓存DEMO,用Profiler捕获文件夹：3Con_AdoNet_Demo这些DEMO说明了缓存计划需要相当的内存开销，而ad-hoc查询浪费的内存空间比较多。存储过程的计划缓存除了具备一般缓存类型的特点外，还有自身的特点和设置，暂时不在本次讨论范围内，掌握了基本的计划缓存特点后，只需要针对存储过程的特有几个特征加以注意后，非常容易掌握。*注意：SQL的数据缓存机制不同于计划缓存机制，SQLServer的数据缓存机制，是类似OS的页替换算法。2.5 即席查询&参数化查询性能分析与对比DEMODEMO6：避免ad-hoc查询4即席查询&参数化查询性能分析与对比.sql这些例子说明了ad-hoc查询在生成缓存中可能对IO有相当的负面影响。2.6决定计划缓存初始寿命的成本组成主要因素Ad-hoc即席查询的成本为0;CPU 时间 (cputime)从磁盘读取的页数 (ioread)；写入磁盘的页数 (iowrite)；以及批处理的查询计划所占的内存页数 (pagecount)。初始寿命由以上元素在一复杂公式&算法下计算出来，同时新的SQL是否能够运用已有的执行计划，还需要由一个额外的算法来确定已缓存的计划是否适用。即，在有计划缓存的情况下，也存在一些特定的不能使用计划缓存的场景(因为某些场景下，如架构更新，大量数据修改或者新增，重用计划可能导致错误，效率低效)。2.7总结计划缓存最佳实践主要总结和开发人员有关的（不包括调优和设计）1 尽量避免ad-hoc查询,特别是频率高的查询。2不论是ad-hoc查询或者是参数化查询，都要保持SQL文本部分完全一致才能重用计划缓存。3 开发时需要关注复杂SQL语句的成本和评估可能的执行次数，可以考虑使用ad-hoc查询来执行语句极其复杂，而执行计划不大可能重用的查询。4 重点考虑优化复杂存储过程的执行计划和设置重新编译参数和阀值。5 对于存储过程或者报表类复杂SQL，要考虑参数数值变化对索引选择度的影响，进而对存储过程IO的影响，如果存在较大影响，则应该选择每次编译或者ad-hoc查询。3详解SQL语句的IO开销3.1 Scan & Logical reads & physical reads & read-ahead 输出项含义表(table) 表的名称扫描计数(scan count) 执行的扫描次数逻辑读取(logical reads) 从数据缓存读取的页数物理读取(physical reads) 从磁盘读取的页数预读(read-ahead reads) 为进行查询而放入缓存的页数Lob逻辑读取(lob logical reads) 从数据缓存读取的 text、ntext、image 或大值类型 (varchar(max)、nvarchar(max)、varbinary(max) 页的数目Lob物理读取(lob physical reads) 从磁盘读取的 text、ntext、image 或大值类型页的数目Lob预读(read ahead-reads) 为进行查询而放入缓存的 text、ntext、image 或大值类型页的数目3.2 IO开销明细DEMO:IO读数的各种组成部分计算逻辑读取IO: IAM+索引页+数据页(分很多种) 、管理页等等、重点关注数量大小。DEMO7（只看第一个）：了解IO的组成，各种扫描，查找涉及到的页类型，反过来理解物理执行过程。关于冷查询逻辑读为什么不一定等于物理读，这涉及到脏数据页和日志写入机制。5详解IO组成.sql4索引碎片对查询时间的影响4.1 磁盘核心结构示意&磁盘取数臂扇区-(组成)-磁道-柱面磁道：一个磁头读的一圈柱面：一排磁头读的n圈取数臂：移到相邻的磁道越1ms,移到随即的磁道510ms4.2 索引碎片对查询性能的影响索引碎片主要对有序查询的性能有明显影响。需要付出额外的内存排序计算(逻辑是需要排序，物理上有很多物理操作都可以实现排序),或者在IO相同的情况下，因取数臂的频繁移动而导致消耗时间过长。4.3 碎片产生的原因4.3.1索引Page Split（页切分/页拆分）SQL Server在Insert/Update时，如果要更新的page已经存储满，无法容纳下新的数据，则SQL Server将这个page的一半数据切分出来，重新分配一个page存放，然后再进行Insert/Update操作，将以满的数据页切分成两个数据页的操作叫做page split。index page会发生page split。在Insert操作时，如果page上的free space小于要插入的记录大小，将进行page split；在Update时，如果table中存在变宽字段，变宽字段的长度变大导致原page上free space不够，将进行page split。4.3.2堆的指针转发在堆(data page)发生，在update时，如果数据也中存在变宽字段或者是nvarchar(max)等类型，变宽字段的长度变大导致原page上free space不够，将进行对的指针转发。1 碎片的生产主要会降低有序查询的性能2 碎片生产的速度，最主要取决于索引数量和索引key字段的更新频度。应对措施：设置合理的页、索引填充因子定期碎片分析整理按需更新*注意：碎片和数据页中的空余空间是不同的。4.4 索引碎片分析&和索引碎片最有有关的参数DEMO8（略过）：索引碎片分析和整理6索引碎片DEMO.sql6索引，视图大小及碎片信息，维护索引.sql4.5 关于减少碎片的设计考虑&建议考虑：表的数据量有序查询占该表操作的比例：一般一级界面需要有序查询(列出所有合同)，二级界面一般是索引查找（打开特定合同编辑，查看等）插入、更新的频度，和插入更新最常见的字段建议：选择合适的填充因子(建表、索引)分析更新频度较多的变长字段，看是否可以设计为等宽字段。例子：xxx编号，合同号，订单号等考虑标记删除记录，而不是物理删除记录，特别是核心的业务表。（这条主要是从减少锁冲突，数据归档，业务数据安全考虑的）对核心大表进行垂直拆分(字段超多的表)4 索引能否应用主要取决于选择度4.1 选择度评估方式和经验值选择度=符合记录的条数/总记录条数这个值越小，则选择度越高，越适合建立索引。这里有一个经验值15%，则建立索引是合适的几率非常大。DEMO9:理解选择度对索引的影响7索引和选择度.sql4.2 统计信息与密度了解：SQL编译时，是否生成使用索引的计划，主要取决于选择度，而选择度的评估依据就是建立索引字段的统计信息，即密度抽样。了解：特定的SQL场景下，需要人工定制统计信息或者是手动更新统计信息。4.3 评估SQL优化空间的杀手锏：数据库优化向导工具DEMODEMO10:熟练sql优化分析引擎来优化开发中的sql8查询优化分析引擎DEMO.sqlSQL引擎的分析结果，可以作为教可靠的优化空间参考。对比前后IO,可以找到95%以上的优化潜力。但是一般来说，SQL引擎的结果不能直接引用，需要根据实际情况去掉冗余部分。4.4 索引规划和创建索引对性能的重要性，我个人认为仅次于数据库设计(直接设计或者投影后的数据库设计)。规划：指导和帮助我们如何可以更好更系统的建立和规划索引。（概念层）建立：从分析到选择最终形成的要创建的索引的过程。（逻辑层） 索引：DBMS中真实的索引对象实体。（物理层）规划：围绕核心业务设计表和索引，花费更多的精力在核心业务涉及到的表应用的性质和划分（客观）：表的生产与查询性主要表和次要表数据规模生产频率查询频率数据库系统（依赖于数据库系统和物理表）命名规范（帮助我们更科学的管理）索引建立顺序：先核心业务表，后次要表主键选择：大小，可重复性(越低则可升级和集成性越好)，业务无关性（业务无关不一定不表达业务含义，而是说业务上拥有不可能改写这个字段，比如你自己创造一个:公司前缀-部门前缀-职位前缀-名字-时间-流水号作为主键）先聚集，主键(主键不一定建聚集索引)/外键，先主要场景后次要场景，先一级界面，后二级界面。5 最佳实践5.1 SQL查询语句最佳实践1 表要带架构. demo： dbo.Orders 2 连接多了要起简短别名3 要格式化你的查询4 只返回你需要的行和列，哪怕打字多一点，也不要取额外的不需要的行和列5 尽量避免昂贵的否定过滤。 比如 not like。6 where 后的过滤条件要符合SARG.尽量避免隐式或者显式的函数调用7 不需要锁的时候，用锁定提示nolock.8 用参数化查询和存储过程。9 避免触发器。如果不能避免，保持尽量简短。10 避免大视图里取小数据。关于执行计划：1 避免表扫描、索引扫描。2 关注非内连接：内外循环的数量，能够减少。3 关注 索引查找：为什么索引查找，缺少哪些字段。4 关注过滤：是否先过滤，缩小操作范围。5 关注排序：不是每个字段排序都有意义。关注排序在计