SQLServerR监视与调优解决方案

SQLServer2008R2监控与调优解决方案PerformanceTurningSQLServer2008R2的架构概要性能调优性能监控性能调优的方法学调优顺序最困难但最有成效最简单但是收效最少架构设计表查询优化存储过程视图索引优化覆盖查询并发控制锁事务存储优化文件组分区服务器优化内存处理器亲和度性能最优化如何设计良好的关系型数据库架构对数据热区的判断根据数据热区定义索引、表分割定义优化SELECT查询尽量将数据存储在同一张表中使用索引及索引覆盖策略优化UPDATE事务尽量将需要更新的数据放在一张较小的表中优化DELETE事务在大规模删除中评估分区的效果优化INSERT事务减少对自动编号的依赖性能调优的方法学调优顺序最困难但最有成效最简单但是收效最少架构设计表查询优化存储过程索引视图索引优化覆盖查询并发控制锁事务存储优化文件组分区服务器优化内存处理器亲和度性能最优化使用有效的查询参数SARGs（查询参数的有效格式）限制以完全符合、一个范围的值、或是以AND连接两个以上的项目来定义搜寻。格式数据字段

部分的运算符<常数或变量><常数或变量>部分的运算符数据字段符合SARGs的范例FirstName=‘王’60000<SalaryFirstName=‘王’

and

Salary>60000容易犯的错误对数据字段进行运算Select*from

Employees

where

LastName+’,’+FirstName=‘Davolio,Nancy进行负向查询Not

Exists、NotIN、NotLike、!=<>!>!<等在where语句中对数据字段使用函数Select*from[orderdetails]whereABS(quantity-100)<1使用OR运算符使用OR做运算符，则需要所有的字段都有可用的索引使用T-SQL的注意事项Select语法尽量不要传回所有的数据表内字段且不配置过滤条件若使用复合索引，则索引顺序上的第一个字段才适合当作过滤条件Distinct，Orderby等语法尽量到查询结果需要时才使用UnionAll要比Union好若未将连接事务级别降低到ReadUncommited，或是通过锁提示NOLOCK来降低阻塞的机会，最好配置SETLOCK_TIMEOUT选项，避免用户无尽等待使用T-SQL的注意事项Insert、Delete和Update大量批处理数据操作时，无Log的行为一定比逐笔数据由两次写入（先记录Log再写数据库）快。在Update和Delete采用Where子句时，记得条件也要符合SARGs格式查询调优选项使用OPTION子句调用表提示或视图提示OPTION(TABLEOPTION(dbo.Orders,IDNEX(IX_OrderID)))FORCESEEK提示FROM

OrdersWITH(FORCESEEK)OPTION(TABLEHINT(dbo.Orders,IDNEX(0),FORCESEEK))sys.fn_validate_plan_guide函数用于验证强制计划的有效性计划指南支持XMLShowPlan参数为监控强制计划设计了新的事件类(PlanGuideSuccessful和PlanGuideUnsuccessful)以及性能计数器(SQLServerSQL

Statistics对象下的GuidedPlanExecutions/sec计数器和MisguidedPlanExecutions/sec计数器)性能调优的方法学调优顺序最困难但最有成效最简单但是收效最少架构设计表查询优化存储过程视图索引优化覆盖查询并发控制锁事务存储优化文件组分区服务器优化内存处理器亲和度性能最优化是否值得建索引选择性数据密度数据分布统计在多个字段上使用索引复合索引索引覆盖查询（include)使用工作载荷分析数据性能提供图形化和命令行两种方式什么是数据库引擎优化顾问?报表和建议工作载荷数据库引擎优化顾问数据库和数据库对象索引碎片碎片如何产生的当数据被修改或者导致索引页面分裂，SQLServer会重组索引页面解决方法<=30%碎片=Reorganize>30%碎片=Rebuild筛选索引应用场合对特殊的属性值进行索引对指定分区的值进行索引常见场合产品目录仅对热门商品的属性进行索引数据仓库仅对最近三个月的销售订单进行索引仓储系统仅对未标记为空的商品进行索引筛选索引的工作原理IDNameatt1att2att3att4att5att6att7att8att91Aax2Bdf3Ctj4Dmu5Ekl6Ftko7Gw8Hhu9IbCREATE

INDEXIX_AONT(att1)WHEREName=‘A’ORName=‘E’SELECTNameFROMTWHEREatt1=‘a’筛选索引的工作原理IDNameatt1att2att3att4att5att6att7att8att91Aax2Bdf3Ctj4Dmu5Ekl6Ftko7Gw8Hhu9IbCREATE

INDEXIX_AONT(att4)WHEREName=‘C’ORName=‘G’SELECTNameFROMTWHEREatt4=‘t’CREATE

INDEXIX_AONT(att1)WHEREName=‘A’ORName=‘E’SELECTNameFROMTWHEREatt1=‘a’索引视图用途对大型数据表进行连接以及汇总重复同一种模式查询重复对相同的数据表，相同的键值作连接性能调优的方法学调优顺序最困难但最有成效最简单但是收效最少架构设计表查询优化存储过程视图索引优化覆盖查询并发控制锁事务存储优化文件组分区服务器优化内存处理器亲和度性能最优化锁与事务锁事务不可分割性(Atomicity)一致性(Consistency)隔离性(Isolation)持久性(Durability)并发事务隔离未提交读已提交读可重复读序列化读提交快照快照锁升级粒度行页分区表锁与并发行版本支持READ_COMMITTED_SNAPSHOT事务隔离级别ALLOW_SNAPSHOT_ISOLATION数据库选项行版本的收益读操作可以获得一致的数据库快照数据在读操作的过程中SELECT语句不会锁住数据(读不会阻塞写，反之亦然)SELECT语句可以获得最近一次其他事务更新数据并提交的值减少了死锁事务需要的锁减少了，则系统用于管理锁的负荷减少了减少了锁升级的发生SQLServer2008R2中锁的增强对分区表和索引优化了锁的升级使用事务的建议事务持续时间越短越好事务期间避免与用户互动查询数据期间，尽量不要启用事务活用事务隔离级别和锁提示死锁循环死锁转换死锁分布式死锁锁的原因和相关处理费时的查询或事务不正确的事务或事务隔离级别事务未正确处理未检测到的分布式死锁锁定数据粒度太高或者太低基本原则防止锁住他人事务不要跨批次，且越短越好，事务期间不要和用户互动小心处理超时放弃，或执行错误等状况建立合适的索引，数据表最有有聚集索引尽量不要启动隐性事务，避免长时间打开事务尽量降低事务隔离级别如果允许，可以尝试使用锁提示基本原则防止与处理死锁尽量避免或尽快处理阻塞访问数据的顺序要相同让不同的连接使用相同的锁提供不同的数据访问路径发生死锁后的解决设置Deadlock优先级，让不重要的事务自动放弃性能调优的方法学调优顺序最困难但最有成效最简单但是收效最少架构设计表查询优化存储过程视图索引优化覆盖查询并发控制锁事务存储优化文件组分区服务器优化内存处理器亲和度性能最优化磁盘子系统设计RAID0RAID1RAID3RAID5RAID0+1文件组规划数据库文件提升性能默认每个数据库文件可以同时处理32个异步I/O1个数据库文件=32个读取+32个写入2个数据库文件=64个读取+64个写入将数据文件和事务日志文件分开储存至不同磁盘阵列数据文件：RAID5或RAID0+1事务日志文件：RAID1利用文件组提升性能若性能瓶颈为DiskI/O，可考虑下列方法将经常要查询或更新的数据表，指定存放于不同磁盘阵列的文件组将非簇索引，指定存放于不同磁盘阵列的文件组将常用的现有数据与历史数据分割储存至不同的数据表，并指定存放于不同磁盘阵列的文件组RAID+文件组磁盘控制器文件组磁盘控制器文件E文件F文件G文件H文件A文件B文件C文件D事务日志事务日志磁盘控制器操作系统磁盘控制器CustomerIDIndexCustomerIDIndexCustomerIDIndex根据订单日期水平分区:OrderDate<‘2003-01-01’OrderDate>=‘2003-01-01’andOrderDate<‘2004-01-01’OrderDate>=‘2004-01-01’Filegroup

DATA_2002Filegroup

DATA_2003Filegroup

DATA_2004Filegroup

IDX_2002Filegroup

IDX_2003Filegroup

IDX_2004OrdersCustomerIDOrderDateAmount…OrderIDOrder

HistoryCustomerIDOrderDateAmount…OrderIDOrderHistoryTableOrderHistoryTableOrderHistoryTable表分区TempDB存放以下对象:内部对象版本存放区用户自定义对象建议根据需要自动扩大设置合理的原始大小将文件增长百分比设置成合理的大小放在快速的I/O系统上创建多个数据库文件，个数和服务器的CPU数目相同，文件大小相同性能调优的方法学调优顺序最困难但最有成效最简单但是收效最少架构设计表查询优化存储过程视图索引优化覆盖查询并发控制锁事务存储优化文件组分区服务器优化内存处理器亲和度性能最优化内存管理内存管理X86系列CPU，32位操作系统2GB<=服务器物理内存<=4GBBoot.ini加上/3GB4GB<=服务器物理内存<=16GB

Boot.ini加上/3GB/PAE服务器物理内存>=16GBBoot.ini加上/PAESQLServer启用AWE（4GB以上内存）

LocalDB内存处理器和线程SQLServer关系引擎OpenDataServices存储引擎Processor0Processor1ProcessornIOCompletionPortThreadThreadThreadThreadThreadThreadThreadThreadThread行集IOCompletionPortThreadThreadThreadThreadThreadThreadIOCompletionPortThreadThreadThreadThreadThreadThreadUMS(UserModeScheduler)SchedulerUMSSchedulerUMSScheduler23IOCompletionPortThreadThreadThreadThreadThreadThreadThreadThreadThread查询查询查询查询14ThreadsSQLServer维护一个线程池来处理用户的需求如查询或是连接使用自己的调度而非操作系统的，来决定哪个处理器执行哪条线程处理器处理查询从内存或是硬盘中取出数据，并将这些结果返回存储引擎将线程返回IOCompletionPort处理器处理器处理器关联I/O关联最大工作线程数提升SQLServer的优先级默认优先级为7，提升以后优先级为13仅在服务器同时安装多个应用程序时使用使用Windows纤程（轻型池）有多个CPU的大型服务器所有的CPU都以接近最大容量在执行内容切换(contextSwitches）的次数过高动态配置热插拔内存热插拔CPU在数据库服务器联机的情况下添加硬件资源SQLServer如何从硬盘读取数据SQLServer缓冲管理器

Windows2003I/OBuffer(64KB)8-KBincrements本地数据库内存缓冲页面CEADFHGBABCDEFGH

CEADFHGB123456781234567873186425

12345678硬盘相关设置硬盘恢复间隔数据库检查点(Checkpoint)事件发生的频率检查点的作用是把数据库缓存中标记为Dirty的数据页面与日志页面写入硬盘数据压缩主要目标缩小数据仓库事实标的尺寸第二目标增强查询性能可以在单个表或索引上启用支持分区需要在处理器资源和存储及IO带宽之间进行取舍数据压缩的工作原理Date_IDProduct_IDUnit_PriceAmount20071203MA_3587_110.001020071203MA_3587_210.00100020071204CF_7253_140.005020071205MA_3659_140.00400Date_IDProduct_IDUnit_PriceAmount20071203MA_3587_110.001020071203MA_3587_210.00100020071204CF_7253_140.005020071205MA_3659_140.00400SQLServer2005SP2推出了vardecimal存储选项允许decimal数据以变长方式存储Date_IDProduct_IDUnit_PriceAmount20071203MA_3587_110.001020071203MA_3587_210.00100020071204CF_7253_140.005020071205MA_3659_140.00400Date_IDProduct_IDUnit_PriceAmount20071203MA_3587_110.001020071203MA_3587_210.00100020071204CF_7253_140.005020071205MA_3659_140.00400数据压缩的工作原理SQLServer2008R2将变长的存储机制扩展到了所有定长数据类型上Date_IDProduct_IDUnit_PriceAmount20071203MA_3587_110.001020071203MA_3587_210.00100020071204CF_7253_140.005020071205MA_3659_140.00400数据压缩的工作原理Date_IDProduct_IDUnit_PriceAmount120071202MA_3587_132110.0010132210.00100014CF_7253_140.005015MA_3659_140.00400SQLServer2008R2还增加了前缀压缩机制(行压缩)：常见的前缀被存储在页面中的一个前缀列表中列中的值将利用前缀列表中的标号进行替代数据压缩的工作原理Date_IDProduct_IDUnit_PriceAmount1(2007120)2(MA_3587_)1(10.00)2(40.00)3(1)

4(3)142311014221100014CF_7253_325015MA_3659_32400SQLServer2008R2还增加了字典压缩机制(页压缩)：将常见的值编制成词典存储在页中列中的常见值利用词典中的标号进行替代对于正式的数据可以达到2-7倍的压缩率压缩率的大小依赖于数据值的模式Date_IDProduct_IDUnit_PriceAmount120071202MA_3587_132110.0010132210.00100014CF_7253_140.005015MA_3659_140.00400稀疏列应用场合半结构化数据：属性集常见场合产品目录不同类别的商品拥有不同的属性集文档管理系统用户对文档设置的自定义属性GPS/地图系统不同地图标记位置的属性稀疏列的工作原理IDNameatt1att2att3att4att5att6att7att8att91Aax2Bdf3Ctj4Dmu5Ekl6Ftko7Gw8Hhu9IbNull值不占用空间，其他值则会产生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)(t,k,o)(att4)(w)(att2,att8)(d,u)(att5)(b)稀疏列的工作原理IDNameatt1att2att3att4att5att6att7att8att91A2B3C4D5E6F7G8H9I(att1,att3)(a,x)(att25873,att35578)(d,f)(att42674,att52358)(t,j)(att245,att78856)(d,u)(att16,att9357)(k,i)(att28772,att65339,att99998)(t,k,o)(att467)(w)(att23,att57468)(d,u)(att5)(b)稀疏列的工作原理IDNameatt1att2att3att4att5att6att7att8att91A2B3C4D5E6F7G8H9I…Att100,000在单张表中支持100,000个稀疏列稀疏列的优势提高存储和访问效率支持那些存储稀疏值的超多列表稀疏列组ColumnSet可以将稀疏列看作一个组进行处理稀疏列组在表中定义的稀疏列的逻辑组CREATETABLEProduct

(IDint,Namevarchar(200),

ProductPropertiesXMLCOLUMN_SET

FORALL_SPARSE_COLUMNS);稀疏列组作为可更新的XML计算列SELECT*语句可以返回所有非稀疏列和稀疏列组通过XML语法可以获取或更新稀疏列组中的特定稀疏列概要性能调优性能监控性能排错的方法学监视整个数据库环境缩小性能问题到特定的数据库环境区域缩小性能问题到特定的数据库环境对象排错单个问题实现解决方案工欲善其事，必先利其器

SQLServer2005Windows系统监视器SQL跟踪探查器动态管理视图&动态管理函数性能仪表板报表活动监视器SQLServer代理警报数据引擎优化顾问查询执行计划关系型数据库性能

PerformanceStudio底开销收集性能数据数据提供者SQL跟踪性能计数器T-SQL集中化的性能数据存储可管理的数据仓库综合的性能数据报表资源调控器SQLServer管理工作负荷备份管理任务报表工作负荷OLTP工作负荷OLTP活动行政报表即席报表高最小内存10%最大内存20%最大CPU20%管理资源池最大

CPU90%应用程序资源池能区别对待不同的工作负荷例如使用应用程序名，登陆名等.每个请求限制重要性最大内存百分比最大CPU时间授予超时值最大请求数资源监控性能实例1(调优前)服务器处理器4个，内存8GB性能对象与计数器：System:ProcessorQueueLength等于0Memory:pages/sec超过2400Memory:AvailableMbytes超过5000PhysicalDisk:Avg.ReadDiskQueueLength超过110PhysicalDisk:Avg.WriteDiskQueueLength超过200Boot.ini已设置/3GB与/PAE参数未启动SQLServer的AWE模式DiskI/O?Memory?性能实例1(调优后)服务器处理器4个，内存8GB性能对象与计数器：System:ProcessorQueueLength等于0Memory:pages/sec低于20Memory:AvailableMbytes维持约500PhysicalDisk:Avg.ReadDiskQueueLength低于2PhysicalDisk:Avg.WriteDiskQueueLength低于2Boot.ini设置