您好,欢迎访问三七文档
当前位置:首页 > IT计算机/网络 > 数据库 > SQLServer2008R2-监视与调优解决方案
SQLServer2008R2监控与调优解决方案PerformanceTurningSQLServer2008R2的架构概要性能调优性能监控性能调优的方法学调优顺序最困难但最有成效最简单但是收效最少架构设计•表查询优化•存储过程•视图索引优化•覆盖查询并发控制•锁•事务存储优化•文件组•分区服务器优化•内存•处理器亲和度性能最优化如何设计良好的关系型数据库架构对数据热区的判断根据数据热区定义索引、表分割定义优化SELECT查询尽量将数据存储在同一张表中使用索引及索引覆盖策略优化UPDATE事务尽量将需要更新的数据放在一张较小的表中优化DELETE事务在大规模删除中评估分区的效果优化INSERT事务减少对自动编号的依赖性能调优的方法学调优顺序最困难但最有成效最简单但是收效最少架构设计•表查询优化•存储过程•索引视图索引优化•覆盖查询并发控制•锁•事务存储优化•文件组•分区服务器优化•内存•处理器亲和度性能最优化使用有效的查询参数SARGs(查询参数的有效格式)限制以完全符合、一个范围的值、或是以AND连接两个以上的项目来定义搜寻。格式数据字段部分的运算符常数或变量常数或变量部分的运算符数据字段符合SARGs的范例FirstName=‘王’60000SalaryFirstName=‘王’andSalary60000容易犯的错误对数据字段进行运算Select*fromEmployeeswhereLastName+’,’+FirstName=‘Davolio,Nancy进行负向查询NotExists、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提示FROMOrdersWITH(FORCESEEK)OPTION(TABLEHINT(dbo.Orders,IDNEX(0),FORCESEEK))sys.fn_validate_plan_guide函数用于验证强制计划的有效性计划指南支持XMLShowPlan参数为监控强制计划设计了新的事件类(PlanGuideSuccessful和PlanGuideUnsuccessful)以及性能计数器(SQLServerSQLStatistics对象下的GuidedPlanExecutions/sec计数器和MisguidedPlanExecutions/sec计数器)性能调优的方法学调优顺序最困难但最有成效最简单但是收效最少架构设计•表查询优化•存储过程•视图索引优化•覆盖查询并发控制•锁•事务存储优化•文件组•分区服务器优化•内存•处理器亲和度性能最优化是否值得建索引选择性数据密度数据分布统计在多个字段上使用索引复合索引索引覆盖查询(include)使用工作载荷分析数据性能提供图形化和命令行两种方式什么是数据库引擎优化顾问?报表和建议工作载荷数据库引擎优化顾问数据库和数据库对象索引碎片碎片如何产生的当数据被修改或者导致索引页面分裂,SQLServer会重组索引页面解决方法=30%碎片=Reorganize30%碎片=Rebuild筛选索引应用场合对特殊的属性值进行索引对指定分区的值进行索引常见场合产品目录仅对热门商品的属性进行索引数据仓库仅对最近三个月的销售订单进行索引仓储系统仅对未标记为空的商品进行索引筛选索引的工作原理IDNameatt1att2att3att4att5att6att7att8att91Aax2Bdf3Ctj4Dmu5Ekl6Ftko7Gw8Hhu9IbCREATEINDEXIX_AONT(att1)WHEREName=‘A’ORName=‘E’SELECTNameFROMTWHEREatt1=‘a’筛选索引的工作原理IDNameatt1att2att3att4att5att6att7att8att91Aax2Bdf3Ctj4Dmu5Ekl6Ftko7Gw8Hhu9IbCREATEINDEXIX_AONT(att4)WHEREName=‘C’ORName=‘G’SELECTNameFROMTWHEREatt4=‘t’CREATEINDEXIX_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’FilegroupDATA_2002FilegroupDATA_2003FilegroupDATA_2004FilegroupIDX_2002FilegroupIDX_2003FilegroupIDX_2004OrdersCustomerIDOrderDateAmount…OrderIDOrderHistoryCustomerIDOrderDateAmount…OrderIDOrderHistoryTableOrderHistoryTableOrderHistoryTable表分区TempDB存放以下对象:内部对象版本存放区用户自定义对象建议根据需要自动扩大设置合理的原始大小将文件增长百分比设置成合理的大小放在快速的I/O系统上创建多个数据库文件,个数和服务器的CPU数目相同,文件大小相同性能调优的方法学调优顺序最困难但最有成效最简单但是收效最少架构设计•表查询优化•存储过程•视图索引优化•覆盖查询并发控制•锁•事务存储优化•文件组•分区服务器优化•内存•处理器亲和度性能最优化内存管理内存管理X86系列CPU,32位操作系统2GB=服务器物理内存=4GBBoot.ini加上/3GB4GB=服务器物理内存=16GBBoot.ini加上/3GB/PAE服务器物理内存=16GBBoot.ini加上/PAESQLServer启用AWE(4GB以上内存)LocalDB内存处理器和线程SQLServer关系引擎OpenDataServices存储引擎Processor0Processor1ProcessornIOCompletionPortThreadThreadThreadThreadThreadThreadThreadThreadThread行集IOCompletionPortThreadThreadThreadThreadThreadThreadIOCompletionPortThreadThreadThreadThreadThreadThreadUMS(UserModeScheduler)SchedulerUMSSchedulerUMSScheduler23IOCompletionPortThreadThreadThreadThreadThreadThreadThreadThreadThread查询查询查询查询14ThreadsSQLServer维护一个线程池来处理用户的需求如查询或是连接使用自己的调度而非操作系统的,来决定哪个处理器执行哪条线程处理器处理查询从内存或是硬盘中取出数据,并将这些结果返回存储引擎将线程返回IOCompletionPort处理器处理器处理器关联I/O关联最大工作线程数提升SQLServer的优先级默认优先级为7,提升以后优先级为13仅在服务器同时安装多个应用程序时使用使用Windows纤程(轻型池)有多个C
本文标题:SQLServer2008R2-监视与调优解决方案
链接地址:https://www.777doc.com/doc-6110460 .html