1、SQL Server 2008 监控与调优解决方案,Performance Turning,SQL Server 2008的架构,概要,性能调优 性能监控,性能调优的方法学,调优顺序,最困难 但最有成效,最简单 但是收效最少,如何设计良好的关系型数据库架构,对数据热区的判断 根据数据热区定义索引、表分割定义 优化SELECT查询 尽量将数据存储在同一张表中(如建立冗余) 使用索引及索引覆盖策略 优化UPDATE事务 尽量将需要更新的数据放在一张较小的表中 优化DELETE事务 在大规模删除中评估分区的效果 优化INSERT事务 减少对自动编号的依赖,性能调优的方法学,调优顺序,最困难 但最有成
2、效,最简单 但是收效最少,使用有效的查询参数,SARGs（查询参数的有效格式） 限制以完全符合、一个范围的值、或是以AND连接两个以上的项目来定义搜寻。 格式 数据字段 部分的运算符 部分的运算符 数据字段 符合SARGs的范例 FirstName=王 6000060000,容易犯的错误,对数据字段进行运算 Select * from Employees where LastName+,+FirstName=Davolio,Nancy 进行负向查询 Not、Exists、Not IN、Not Like、!= ! !等 在where语句中对数据字段使用函数 Select * from order
3、 details where ABS(quantity-100)1 使用OR运算符 使用OR做运算符，则需要所有的字段都有可用的索引,使用T-SQL的注意事项,Select语法 尽量不要传回所有的数据表内字段且不配置过滤条件 若使用复合索引，则索引顺序上的第一个字段才适合当作过滤条件 Distinct，Order by等语法尽量到查询结果需要时才使用 Union All要比Union好 若未将连接事务级别降低到Read Uncommited，或是通过锁提示NOLOCK来降低阻塞的机会，最好配置SET LOCK_TIMEOUT选项，避免用户无尽等待,使用T-SQL的注意事项,Insert、Del
4、ete和Update 大量批处理数据操作时，无Log的行为一定比逐笔数据由两次写入（先记录Log再写数据库）快。 在Update和Delete采用Where子句时，记得条件也要符合SARGs格式,查询调优选项,使用OPTION子句调用表提示或视图提示 OPTION (TABLE OPTION(dbo.Orders, IDNEX(IX_OrderID) FORCESEEK提示 FROM Orders WITH (FORCESEEK) OPTION (TABLE HINT(dbo.Orders, IDNEX(0), FORCESEEK) sys.fn_validate_plan_guide函数用于
5、验证强制计划的有效性 计划指南支持XML ShowPlan参数 为监控强制计划设计了新的事件类(Plan Guide Successful和Plan Guide Unsuccessful)以及性能计数器(SQL Server SQL Statistics对象下的Guided Plan Executions/sec计数器和Misguided Plan Executions/sec计数器),性能调优的方法学,调优顺序,最困难 但最有成效,最简单 但是收效最少,是否值得建索引,选择性 数据密度 数据分布 统计,在多个字段上使用索引,复合索引 索引覆盖查询（include),使用工作载荷分析数据性能
6、提供图形化和命令行两种方式,什么是数据库引擎优化顾问?,报表和建议,工作载荷,数据库引擎 优化顾问,数据库和数据库对象,索引碎片,碎片如何产生的 当数据被修改或者导致索引页面分裂，SQL Server会重组索引页面 解决方法30% 碎片 = Rebuild,筛选索引,应用场合 对特殊的属性值进行索引 对指定分区的值进行索引 常见场合 产品目录 仅对热门商品的属性进行索引 数据仓库 仅对最近三个月的销售订单进行索引 仓储系统 仅对未标记为空的商品进行索引,筛选索引的工作原理,ID,Name,att1,att2,att3,att4,att5,att6,att7,att8,att9,1,A,a,x,
7、2,B,d,f,3,C,t,j,4,D,m,u,5,E,k,l,6,F,t,k,o,7,G,w,8,H,h,u,9,I,b,CREATE INDEX IX_A ON T(att1) WHERE Name = A OR Name = E SELECT Name FROM T WHERE att1 = a,筛选索引的工作原理,ID,Name,att1,att2,att3,att4,att5,att6,att7,att8,att9,1,A,a,x,2,B,d,f,3,C,t,j,4,D,m,u,5,E,k,l,6,F,t,k,o,7,G,w,8,H,h,u,9,I,b,CREATE INDEX IX
8、_A ON T(att4) WHERE Name = C OR Name = G SELECT Name FROM T WHERE att4 = t,CREATE INDEX IX_A ON T(att1) WHERE Name = A OR Name = E SELECT Name FROM T WHERE att1 = a,索引视图,用途 对大型数据表进行连接以及汇总 重复同一种模式查询 重复对相同的数据表，相同的键值作连接,性能调优的方法学,调优顺序,最困难 但最有成效,最简单 但是收效最少,锁与事务,锁 事务 不可分割性(Atomicity) 一致性(Consistency) 隔离性(
9、Isolation) 持久性(Durability),并发,锁与并发,使用事务的建议,事务持续时间越短越好 事务期间避免与用户互动 查询数据期间，尽量不要启用事务 活用事务隔离级别和锁提示,死锁,循环死锁 转换死锁 分布式死锁,锁的原因和相关处理,费时的查询或事务 不正确的事务或事务隔离级别 事务未正确处理 未检测到的分布式死锁 锁定数据粒度太高或者太低,基本原则,防止锁住他人 事务不要跨批次，且越短越好，事务期间不要和用户互动 小心处理超时放弃，或执行错误等状况 建立合适的索引，数据表最有有聚集索引 尽量不要启动隐性事务，避免长时间打开事务 尽量降低事务隔离级别 如果允许，可以尝试使用锁提示
10、,基本原则,防止与处理死锁 尽量避免或尽快处理阻塞 访问数据的顺序要相同 让不同的连接使用相同的锁 提供不同的数据访问路径 发生死锁后的解决 设置Deadlock优先级，让不重要的事务自动放弃,性能调优的方法学,调优顺序,最困难 但最有成效,最简单 但是收效最少,磁盘子系统设计,RAID 0 RAID 1 RAID 3 RAID 5 RAID 0+1,文件组,规划数据库文件提升性能,默认每个数据库文件可以同时处理32个异步I/O 1个数据库文件= 32个读取+ 32个写入 2个数据库文件= 64个读取+ 64个写入 将数据文件和事务日志文件分开储存至不同磁盘阵列 数据文件：RAID 5或RAI
11、D 0 + 1 事务日志文件：RAID 1,利用文件组提升性能,若性能瓶颈为DiskI/O，可考虑下列方法 将经常要查询或更新的数据表，指定存放于不同磁盘阵列的文件组 将非簇索引，指定存放于不同磁盘阵列的文件组 将常用的现有数据与历史数据分割储存至不同的数据表，并指定存放于不同磁盘阵列的文件组,RAID+文件组,根据订单日期水平分区:,Order Date 2003-01-01,Order Date = 2003-01-01 and Order Date 2004-01-01,Order Date = 2004-01-01,Filegroup DATA_2002,Filegroup DATA_
12、2003,Filegroup DATA_2004,Filegroup IDX_2002,Filegroup IDX_2003,Filegroup IDX_2004,表分区,TempDB,存放以下对象: 内部对象 版本存放区 用户自定义对象 建议 根据需要自动扩大 设置合理的原始大小 将文件增长百分比设置成合理的大小 放在快速的I/O系统上 创建多个数据库文件，个数和服务器的CPU数目相同，文件大小相同,性能调优的方法学,调优顺序,最困难 但最有成效,最简单 但是收效最少,内存管理,内存管理,X86系列CPU，32位操作系统 2GB=16GB Boot.ini加上/PAE SQL Server启
13、用AWE（4GB以上内存）,LocalDB,内存,处理器和线程,SQL Server,关系引擎,Open Data Services,存储引擎,Processor 0,Processor 1,Processor n,行集,UMS (User Mode Scheduler) Scheduler,UMS Scheduler,UMS Scheduler,2,3,SQL Server 维护一个线程池 来处理用户的需求 如查询或是连接,使用自己的调度 而非操作系统的， 来决定哪个处理器 执行哪条线程,处理器处理查询 从内存或是硬盘 中取出数据， 并将这些结果返回 存储引擎,将线程返回 IOComplet
14、ion Port,处理器,处理器,处理器关联 I/O关联 最大工作线程数 提升SQL Server的优先级 默认优先级为7，提升以后优先级为13 仅在服务器同时安装多个应用程序时使用 使用Windows纤程（轻型池） 有多个CPU的大型服务器 所有的CPU都以接近最大容量在执行 内容切换(context Switches）的次数过高,动态配置,在数据库服务器联机的情况下添加硬件资源,SQL Server如何从硬盘读取数据,SQL Server 缓冲管理器,Windows 2003 I/O Buffer(64 KB) 8-KB increments,本地数据库,内存缓冲页面,C,E,A,D,F,
15、H,G,B,A,B,C,D,E,F,G,H,C,E,A,D,F,H,G,B,7,3,1,8,6,4,2,5,硬盘相关设置,硬盘,恢复间隔 数据库检查点(Checkpoint)事件发生的频率 检查点的作用是把数据库缓存中标记为Dirty的数据页面与日志页面写入硬盘,数据压缩,主要目标 缩小数据仓库事实标的尺寸 第二目标 增强查询性能 可以在单个表或索引上启用 支持分区 需要在处理器资源和存储及IO带宽之间进行取舍,数据压缩的工作原理,SQL Server 2005 SP2推出了vardecimal存储选项 允许decimal数据以变长方式存储,数据压缩的工作原理,SQL Server 2008将
16、变长的存储机制扩展到了所有定长数据类型上,数据压缩的工作原理,SQL Server 2008还增加了前缀压缩机制(行压缩)： 常见的前缀被存储在页面中的一个前缀列表中 列中的值将利用前缀列表中的标号进行替代,数据压缩的工作原理,SQL Server 2008还增加了字典压缩机制(页压缩)： 将常见的值编制成词典存储在页中 列中的常见值利用词典中的标号进行替代,对于正式的数据可以达到2-7倍的压缩率 压缩率的大小依赖于数据值的模式,稀疏列,应用场合 半结构化数据：属性集 常见场合 产品目录 不同类别的商品拥有不同的属性集 文档管理系统 用户对文档设置的自定义属性 GPS/地图系统 不同地图标记位
17、置的属性,稀疏列的工作原理,ID,Name,att1,att2,att3,att4,att5,att6,att7,att8,att9,1,A,a,x,2,B,d,f,3,C,t,j,4,D,m,u,5,E,k,l,6,F,t,k,o,7,G,w,8,H,h,u,9,I,b,Null值不占用空间，其他值则会产生2-4字节的额外开销 同时数据访问效率会受到轻微影响,(att1, att3) (a, x),(att2, att8) (d, f),(att4, att5) (t, j),(att2, att7) (d, u),(att1, att9) (k, i),(att2, att6, att9)
18、 (t, k, o),(att4) (w),(att2, att8) (d, u),(att5) (b),稀疏列的工作原理,ID,Name,att1,att2,att3,att4,att5,att6,att7,att8,att9,1,A,2,B,3,C,4,D,5,E,6,F,7,G,8,H,9,I,(att1, att3) (a, x),(att25873, att35578) (d, f),(att42674, att52358) (t, j),(att245, att78856) (d, u),(att16, att9357) (k, i),(att28772, att65339, at
19、t99998) (t, k, o),(att467) (w),(att23, att57468) (d, u),(att5) (b),稀疏列的工作原理,ID,Name,att1,att2,att3,att4,att5,att6,att7,att8,att9,1,A,2,B,3,C,4,D,5,E,6,F,7,G,8,H,9,I,Att100,000,在单张表中支持100,000个稀疏列,稀疏列的优势,提高存储和访问效率 支持那些存储稀疏值的超多列表 稀疏列组Column Set可以将稀疏列看作一个组进行处理,稀疏列组,在表中定义的稀疏列的逻辑组 CREATE TABLE Product (ID
20、 int, Name varchar(200), ProductProperties XML COLUMN_SET FOR ALL_SPARSE_COLUMNS); 稀疏列组作为可更新的XML计算列 SELECT *语句可以返回所有非稀疏列和稀疏列组 通过XML语法可以获取或更新稀疏列组中的特定稀疏列,概要,性能调优 性能监控,性能排错的方法学,工欲善其事，必先利其器,Windows 系统监视器 SQL 跟踪探查器 动态管理视图 & 动态管理函数 性能仪表板报表 活动监视器 SQL Server 代理警报 数据引擎优化顾问 查询执行计划,关系型数据库性能 Performance Studio,
21、资源调控器,SQL Server,管理工作负荷,备份,管理任务,报表工作负荷,OLTP 工作负荷,OLTP 活动,行政报表,即席报表,高,能区别对待不同的工作负荷 例如使用应用程序名，登陆名等. 每个请求限制 重要性 最大内存百分比 最大 CPU 时间 授予超时值 最大请求数 资源监控,性能实例1(调优前),服务器处理器4个，内存8 GB 性能对象与计数器： System: Processor Queue Length等于0 Memory: pages/sec超过2400 Memory: Available Mbytes超过5000 PhysicalDisk: Avg. Read Disk Q
22、ueue Length超过110 PhysicalDisk: Avg. Write Disk Queue Length超过200,Boot.ini已设置/3GB与/PAE参数 未启动SQL Server的AWE模式,Disk I/O ?,Memory ?,性能实例1(调优后),服务器处理器4个，内存8 GB 性能对象与计数器： System: Processor Queue Length等于0 Memory: pages/sec低于20 Memory: Available Mbytes维持约500 PhysicalDisk: Avg. Read Disk Queue Length低于2 Phy
23、sicalDisk: Avg. Write Disk Queue Length低于2,Boot.ini设置/3GB与/PAE参数 启动SQL Server的AWE模式,Disk I/O, OK,Memory, OK,性能实例2(调优前),服务器硬件资源 处理器2个，内存2 GB 共有1 + 14个存储过程，共约有30000行指令的复杂系统，需要执行4 hr 21 min 使用者需求：必须于2 hr内执行完毕,性能实例2(调优后),仅将其中一个最慢的查询，不必要的table variable查询技巧，变更为直接从数据表的查询，并且针对查询中所需要的数据表字段建立复合字段的非簇索引 运行时间大幅缩
24、短至1 hr 15 min，性能提升71%，已满足使用者需求之期望 后续建议 性能对象与计数器： Processor: % Processor Time维持在70%以下 Memory：page/sec超过7800 Memory：available Mbytes只剩下4MB,再增加内存, 2006 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or tradem
25、arks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.,
