重读SQLServer技术内幕 －－ 故障检测概要
第一节 影响性能的因素
一般来说我们说SQLServer出现性能问题时，主要指的是：
响应时间：提交请求到收到第一个响应字节的第一个字符的时间间隔；
吞吐量： 单位时间内处理的批数量；
可扩展性： 当上述两个指标不够理想时，是否能够简单的通过添加新的服务器来满足。Scale Out(相对于Scale up)；
当DBMS出现问题时，对运维来说可能是监控出现明显的有别于历史曲线的异常波动，例如CPU高，锁上升，IO异常上升等，对于系统使用者来说则是系统响应明显变慢。
从全局来看，能够影响数据库性能的因素有很多，概括的来说可归结于如下几个方面：
应用程序的体系结构： 一个好的架构很多时候都能最大的利用服务器，数据库的潜力；不好的，额，看看12306就知道了，相信在给他们1W台服务器他们也搞不好。
应用程序设计：我就看到过曾经有兄弟傻乎乎的把几百万的用户信息一次加载到内存中。当然实际情况大都比这个要更为复杂；程序设计包括物理设计和数据库模型设计，物理设计是指数据文件在存储介质上的分布，例如temp数据库最好能放在独立高速的磁盘；日志和数据最好能放在不同的磁盘上避免IO竞用； 将频繁使用的数据表分散到不同的磁盘同样是避免IO竞用。数据模型设计则主要是范式的适用和选择，更高的范式意味着更高的写入更新一致性和最低的查询性能。企业级开发和互联网开发有截然不同的选择。企业开发可能尽可能的保证数据一致性即使这将导致更为复杂的程序结构和更复制的连接查询。 但是互联网行业则更看重可用性和高吞吐量，通过异步和补偿机制保证延迟一致性和最终一致性。这就可以将范式降到较低的级别上。
事务和隔离级别： 将SQLServer设置在一个比较合适的隔离级别上不是容易的事，大多数我们将其放在默认的级别上（读已提交）上就够了，对于特殊需要的在个别的加上提示（ULock/NoLock等），务必保证不持有当前不必要的数据。尽量将持有的锁的力度降到最低（这里不是说行锁就比页锁要好，大量的行锁同样会带来负面的影响。没有特殊原因和实际的测试不要轻易的升级/降级锁的粒度。大部分时候SQLServer锁都能选择合适的粒度）。事务隔离机制包括：读未提及，读已提交，可重复读，串行化。这个更详细的自己去Google就好了，此处略。
Transact-SQL: 完成同样的逻辑处理，使用不同的SQL语句和不同的谓词，最终的性能差别可能会差很大，有的时候甚至超出你的想象。 这归结于SQLServer的查询优化器其实还不够智能，当他认为优化到足够好的时候，处于成本考虑就不会继续的优化下去了。 我个人碰到过的一个例子就是当我把一个很纠结的多表Join查询改成一个层次分明相关子查询时，执行时间一下子从40s+ 降到了20ms。这个当时我自己都很意外。 比对两次生成的执行机会，发现优化的执行计划很同样简洁，Index Seek 后直接 Merge Join了，而之前的执行计划则是查询优化器几乎没有任何的优化，直接按照SQL的书写顺序一路HashJoin到底。
硬件资源： 没什么可说的，上述几条没有优化的余地的时候，就需要考虑是否升级硬件了，网卡是不是还是10M，硬盘是不是还是5400转的，内存是不是太小等等。当然如果你的用户从1000~100W了也真该考虑是不是把服务器整个换成更高配的机器了。
SQLServer配置：很多时候如果业务的数据交换量真的很大，无论如何优化都没有改善的时候可以看下SQLServer的一些配置上是否有改善的余地，这点上一定要谨慎且要有住够的测试结果支撑。否则可能适得其反。 微软的一贯风格，一般来说，默认的就是最好的。这里需要特别说明的有两点，一：工作进程在X86，X64，IA64上分别消耗512K，2M，4M内存。且最大进程数受CPU数量有关。所有并不是越大越好。32位CPU个数与最大进程数的关系是：4，8，16，32个分别对应最大进程数为256，288，352，480； 64位处理器则翻倍。 这个对应的就是最大并行批处理的个数了。 如果有较多的需长时间运行的批处理则考虑将最大进程数适当调高。 二： SQLServer最大和最小可用内存，请务必不要就将CPU和内存全部分配给SQLServer,否则一旦SQLServer宕了，服务器也就宕了，补救都没办法。
第二节 故障排查概要
对于开发人员来说故障可能就是指Bug，系统崩溃等，对于运维和ＢＩ来说可能异常的数据波动就意味这故障。当然不能等什么时候系统真正完蛋了，才去着手干活。一般来说都有各种各样的监控系统在跑。监控各种指标CPU，内存，IO，网络等等。 按照历史基线，一旦偏离历史值或者某个绝对值。就发出去预警，接着优化的活就来了。
SQLServer的各种监控数据主要来自与一下几个方面：
系统监控器： 这个是基于轮询的。开销取决于计数器的数量和频率。总的来说开销蛮大的，几乎没见有实际的项目中用这个。
SQLServer性能分析器： SQLSERVER跟踪的一个图形界面。用来监视SQLServer实例，结果输出到文件或者数据表。性能分析器基于事件，基本上不会丢掉任何指定要监视的事件。 开销同样取决于监视的事件类型和事件发生的频率。对性能有一定影响。
数据库引擎优化顾问（DTA）：这个只能做静态分析，也许系统测试的时候可以跑跑看，然后提些建议之类的。实际运行中则没什么用。
DBCC命令：数据库控制台命令，大多数用来检测数据库的一致性。监控内存，日志，CPU的使用情况等大方面指标。不能反映实际的性能问题。
动态管理视图/函数（DMV，DMF）：这个才是监视和分析的重头戏，通过各种视图/函数能取得几乎所有你要的检测和运行数据。DMV设计和系统视图和系统函数相同，可以通过跨视图的Join获得各类数据。 同样DMV/DMF 也是基于轮询的，可能会丢掉时间临界点的数据。
有了这些工具就能获得运行时的各类监控数据，一旦收到警报就能着手进行故障的排查。对DBA来说，活来了。
一般来说,大部分的SQLServer性能故障的排查步骤不十分类似，一旦一个故障出现，就意味着在CPU，内存，IO，网络等一个或多个资源上出现瓶颈。
CPU： 在以提交的批处理总会处于运行中，可运行，挂起三种状态中的一种。这个可以参考线程的概念。后台实际每个批处理对应的线程。 注意一点：可运行表示等待CPU，挂起表示等待资源，如锁，IO。 所以锁如果一旦有大量的可允许意味着CPU瓶颈，大量的挂起这说明锁太多了。 上述指标都可以通过特定的DMV（Sys.dm_os_workers，sys.dm_os_schedulers,sys.dm_os_wait_stats等）来查询。查询可运行状态的工作进程数和可预先状态下花费的时间SQL如下：
1 SELECT COUNT(1) AS workers_waiting_for_cpu， t2.scheduler_id 2 FROM SYS.dm_os_workers as t1, sys.dm_os_schedulers AS t2 3 WHERE t1.stat = 'Runnable' AND t2.scheduler_id < 255 4 GROUP BY t2. scheduler_id 5 6 7 SELECT SUM(signal_wait_time_ms) 8 FROM sys.dm_os_wait_stats.
SQLServer 同样提供了DMV的图像化界面。可以查看单个指标的图形化显示。
一把来说CPU高主要原因是： 生成低效的查询计划，统计值更新或其他原因导致的重编译。可以冲这些方面着手排查。当然如果你每次搞个上百万数据的Join或者函数逐行处理也会导致CPU高。
内存和IO瓶颈： 这两个交容易排查，且有关联性，如果内存压力较大则会导致大量的IO产生。一般来说出现这两个问题直接通过DMV查找消耗内存和IO最高的批处理就对了。
tempdb: 临时数据库的瓶颈比较少见，但是一旦出现会比较致命，它见导致整个SQLServer实例的所有DB的性能出现问题。 一般来说只要不在临时数据库上大量的创建临时对象和数据就不会出现。 检测方式和其他DB类似，同样可以使用DMV和系统监视器来检测具体的瓶颈。
阻塞： 这里的阻塞不仅仅是指逻辑锁，虽然锁是最常见的阻塞原因。 还包括IO等待，内存分配等待等等。对于的DMV主要是sys.dm_os_wait_stats.
