SQL Server 性能调优―如何定位并解决系统瓶颈

SQLServer性能调优——如何定位和解决系统瓶颈议程•SQLServer性能优化概念•CPU性能瓶颈分析•内存性能瓶颈分析•磁盘性能瓶颈分析性能优化介绍•什么是性能问题？•性能的定义–基线（Baseline）–开销–优化影响性能的因素•应用程序•查询语句•事务管理•数据库设计•数据分布•SQLServer本身•网络•操作系统•硬件CPU性能瓶颈分析•CPU性能瓶颈的特征•常见问题及解决方法问题特征•何时查看–计数器Processor:%ProcessorTime一直高于80%•查看内容：–SQLServer的计数器Process:%ProcessorTime值是否很高•解决方法–找到谁在消耗CPU，并对其进行优化–降低负载–升级硬件常见的原因•过度的编译/重编译•低效的查询执行计划•语句内的并行执行•游标的不合理使用过度编译/重编译•编译和重编译–重编译：在SQLServer执行一个查询之前，它会查看当前执行计划的准确性和有效性，如果检查失败，就需要编译或者重编译–消耗较多的CPU检测•性能监视器–SQLRecompilations/sec对BatchRequests/Sec的比率较高•Trace–SP:Recompile–SP:Compile–RPC:Completed–AutoStats原因和解决方案•SET语句–在连接建立时运行SET语句•统计信息变化–指定KEEPFIXEDPLAN查询提示•不明确的对象名称–使用明确的对象名称•HINT提示–去掉WITHRECOMPILE低效的执行计划•背景知识–SQLServer优化器试图找到响应时间最快的执行计划–一旦查询被优化之后，存储引擎就使用选中的执行计划将结果返回，而被使用的这个执行计划就会被保存在内存中一个被称之为“计划缓存”的地方，从而使得这个执行计划可以被重用，从而节省CPU等资源。–由于某些原因，例如索引缺失、统计数据过期，这个执行计划实际上并不一定是最快的–这个次优执行计划的某些因素(表扫描/索引扫描等)可能会使CPU使用率变高原因和解决方案•过时的统计信息–UPDATESTATISTICS•索引缺失–使用数据库引擎优化顾问(DTA)•代码质量–修改查询语句•错误索引–查询提示(hint)并行查询•背景知识–查询优化器会选择最快的计划.–如果查询的开销并行的开销阈值(默认为5秒),查询将会并行执行–在大多数情况下，并行能够加强查询的性能–然而，一个给定查询的响应时间必须从整个系统的吞吐量和系统上的其他查询出发来综合考虑解决方案•通过在服务器级别设置最大并行度选项为1（关闭并行），然后根据确实需要在单个查询级别使用OPTION(MAXDOPn)覆盖它，即可达到此目的。游标的不合理使用•背景知识–在关系数据库中，我们对于查询的思考是面向集合的。而游标打破了这一规则，游标使得我们思考方式变为逐行进行.–SQL游标的优点是可以方便从一个结果集中进行循环遍历数据在进行操作–游标也有缺点——复杂和低效，是游标的最大缺点解决方案对于游标一些优化建议如果能不用游标，尽量不要使用游标用完之后一定要关闭和释放尽量不要在大量数据上定义游标尽量不要使用游标上更新数据如果可以，尽量使用FAST_FORWARD关键字定义游标如果只对数据进行读取，当读取时只用到FETCHNEXT选项，则最好使用FORWARD_ONLY参数内存性能瓶颈分析•SQLServer如何使用内存•如何识别内存性能瓶颈SQLServer如何使用内存•虚拟地址空间和物理内存•动态内存管理•AWE和LockedPagesinmemory虚拟地址空间和物理内存VirtualAddressSpace（虚拟地址空间）•它是一个应用程序能够申请访问的最大地址空间。在32位的服务器上，由于地址总线的长度是32位，所以寻址空间最大也就是4GB。在64位的服务器上，目前的系统限制是8TB。虚拟地址空间里存放的数据信息不一定都在物理内存里。PhysicalMemory（物理内存）•顾名思义，就是内存芯片提供的物理存储空间。访问存放在物理内存里的数据通常比访问硬盘要快得多。对SQLServer来讲，要频繁访问的数据对象必须能长时间地缓存在物理内存里，才能达到最优化的效率。AWE和LockedPagesinmemory•AWE的作用是使进程能超出32位地址空间的限制访问内存。•如果向SQLServer服务账户授予了LockPagesinMemory权限，一旦SQLServer的内存页面被锁定了，那么这些页面就不能被页面交换出去。动态内存管理•当SQLServer动态地使用内存，它定期地查询系统，来确定空闲物理内存量•PerfMon–对象SQLServer：MemoryManger的计数器totalservermemory内存配置选项•Minservermemory(MB)–指定最小值–不会在启动时自动提交–可能不会达到最小值•Maxservermemory(MB)–指定最大值–SQLServer可能因为当前的使用而来不及释放内存，预留2到4GB内存给操作系统•开启AWE和内存锁定页内存对其他方面的影响•内存影响了整个服务器的性能•对磁盘I/O的影响–过度的硬换页会干扰页文件设备上的数据I/O操作–降低了系统文件缓存的有效性•对CPU的影响–增加了内核时间–如果内存有压力(4或100MB)，换页则会占用大部分的CPU时间LazyWriter•两个目的:–试图保持最低数量的空缓冲区–保留足够的空闲物理内存，从而避免频繁换页•LazyWriterVS.检查点checkpoint检测•SQL性能计数器–主要使用的计数器:Memory:AvailableMbytes,SQLServer:MemoryManager,SQLServer:BufferMananger内存误区•误区–系统有大量的可用内存=没有内存瓶颈–SQLServer在有压力时总是会释放内存–SQLServer会把页锁在物理内存中–“最小服务器内存”在启动时就采用–增加内存就一定会提高性能磁盘性能瓶颈分析•介绍–SQLServer性能依赖于磁盘子系统的性能–数据库中的数据页并非总能够全部放到内存当中，因此可能会发生磁盘I/O监视I/O信息•使用单独的日志•使用小的时间间隔:2，4秒•不要把日志记到待评估的磁盘上检测DEMO—案例1从一个硬盘读写非常慢的sqlserver服务器上收集的性能日志DEMO—案例2有一个客户抱怨，每天晚上SQLServer会突然很慢，几乎没响应。根据客户提供的时间点，在SQLServerErrorlog里可以找到这样的信息。DEMO—案例2磁盘IO的影响因素•不合适的执行计划(缺少有效的索引)•太多OLAP类型的查询•硬件能力不足够满足I/O需求解决方案•找到引起这么多I/O的语句，看看语句有没有调整优化的空间•调整应用和数据库设计，降低I/O请求数目•如果有内存瓶颈，先要解决内存瓶颈，才能降低I/O瓶颈•数据压缩•I/O子系统升级推荐红宝书之一推荐红宝书之二谢谢听讲