Sql效能优化总结
前一段时间公司系统遇到了严重的系统效能问题，经过一段时间的努力，现在已缓解了不少，今天抽空总结下，希望能起到抛砖引玉的作用，若你也遇到同样的问题，不妨多交流下。
环境：Windows2003+Sql2005
一直以来，不断的系统维护，新功能的增加，和一些新系统的应用，已经使现有的数据服务器不堪重负，虽然服务器的硬件配置已经比较顶级（一般的企业不会有这么顶级的配置），但还是逐渐难以应付大量的数据响应。用户也逐渐开始投诉系统效能问题。这个问题是一路积累出来的，现在浮出水面而已。其中一些旧系统当中使用的sql语句存在严重的效能问题，只是以前的应用相对较少，服务器可以轻松应付，现在随着应用不断增加，已经达到了瓶颈，逐渐难以支撑，问题浮现。
由于不想有较大的投资，又想解决现存问题，所以，经过分析，主要从以下几个方面入手考虑，解决问题。
- 数据服务器架构调整，分散服务器压力，（读写分离）
- 针对耗费资源较多的具体应用进行针对性的优化
- 对一些已经不使用的旧数据进行封存处理
经过以上措施，现系统效能问题已有较大改善，但这是个逐步改善，持续优化的过程。
看到几个博友对笔者的这个话题挺感兴趣，今天终于可以静下心来继续总结，废话少说，入正题。
从SQL Server2005到SQL Server 2008和现在的SQL Server2012，微软好像一直没有推出负载均衡组件，如类似Oracle RAC的技术，不能实现负载均衡。由于数据库的高并发及横向扩展是经常遇到的问题，不少用户遇到这样的情况时考虑的方案是移植到Oracle平台，采用RAC来解决问题。这是个很艰难的过程，成本相信也将是巨大的。也考虑过一些第3方方案。如：Moebius等，由于具多限制，也未能采用。所以只能利用MS现有技术了，介绍前先简单提下MS的SQL平台的几种技术：
故障转移集群
Microsoft Cluster Server(MSCS)是一个提升可用性的技术，当一台服务器出现故障是可以段时间切换到其他服务器继续提供应用服务，不能实现负载均衡。
SQL Server 2005镜像和快照
该技术利用镜像来提高可用性，利用快照服务提供数据访问服务。
镜像是大家都熟悉的一个概念，快照是SQL Server 2005中引入的新特性。快照是某一个时间点上的数据库的克隆。快照的生成一般只需要几秒钟，创建快照时其实没有复制任何数据。对镜像数据库创建了快照DB，快照DB就可以给用户提供数据服务器。但是由于是快照，实时性较差，所以使用时要考虑是否适合一些实时应用。如果要想把负载分散在主数据服务器和镜像服务器上，就要阶段性的创建快照。
复制、订阅
SQL Server提供了复制技术（Replication），可以使现读写分离。有几种方式，基于快照、基于log和复合方式等，由于在本例中没有使用，在此不再赘述。
数据服务器架构调整：
由于公司几年前的应用逐渐增多，已经出现过一次系统效能问题，当时对数据服务器的硬件已经升级过一次了，但是由于近几年系统的不断增加，又一次达到临界点，但是目前的数据服务器在一般的企业应用中基本已经算是顶级配置了，再进行硬件升级的话，一定需要一笔不小的开支。经过分析，其实现有系统中一部分功能是不需要进行对数据的更新的，只需要读取数据（一些报表、查询等），于是决定把现有的服务器架构进行调整，进行读写分离，把一些唯读即可满足的功能，转移到其他服务器，以此来分担现有的主数据服务器的压力。
原来的数据服务器架构，就是一般的客户端直解连接连接数据服务器，如下图：
调整后的数据服务器架构：
其实目前这个架构还不是最佳方案，采用复制技术应该在当前Case应该是最合适的，一是对系统的修改量最小，只要改变数据连接就可以达到目前的效果。二是复制技术相对目前采用的镜像+快照方案技术上也是相对成熟的，但是之前由于出现过一次故障，所以该方案没被采纳，直到现在都还在愤愤不平，（TMD公司政治，不说了），数据服务器架构调成后，客户端的一些唯读功能，可以直接连接到镜像服务器，但是镜像是一种数据容错方案，若你提供镜像服务器上更新数据的话，可能会发生意想不到的情况（具体没有测试，不妄加评论），而我们又想提供数据服务给客户端，该怎么办呢？SqlServer2005提供了快照功能，建立快照DB后就象访问原DB一样，可以增删改，快照DB刚开始创建时其实是没有数据的，只是记录变化的记录信息，具体快照DB的原理，大家可另外了解，在此不再赘述。
这种方案有个缺点，创建了快照DB后，用户访问快照DB相当于访问创建快照DB的那个时间点的原有DB。
例如：原DataBase名字为DB1，在上午10：00整时创建了快照DataBase，名字为DB2，用户访问DB2就相当于访问10：00点整时的DB1，快照嘛，顾名思义。
由于以下2个原因，就需要我们不断的删除旧的快照DB，创建新的快照DB提供数据访问服务。
- 客户端的用户永远想访问最实时的数据
- 快照DB的数量不能太多，因为增加快照DB会增加服务器负荷（因为他要同步源DB和每一个快照DB的差异数据）
由快照DB提供给用户访问的数据同原来的服务器中的数据有一个时间差，我们定为10分钟，这个时间差对一般的应用用户还是可以接受的。
经过迁移一些耗费服务器资源（主要是逻辑读，这个是服务器操作的瓶颈）较大的报表或查询到镜像服务器，有效减少了主数据服务器的压力，事实证明效果还是比较明显的。经过以上努力，合理分担了主数据服务器的压力到其它不是很重要的服务器上，从某种意义上来说达到了负载均衡的效果。到此，关于数据服务器架构调整先告一段落，后续再介绍具体的sql优化。
未完待续。
今晚继续进行Sql效能问题的分享，今天主要是一些具体的sql优化方法和思路分享，若看过后你也有其他想法，欢迎一起探讨，好了，进入今天的主题。
针对性地对一些耗资源严重的具体应用进行优化
出现效能问题时，首先要做的是什么？这个问题我问过不少同事，有人说凭经验对出问题的sql进行优化，如我们一般说的要合理使用索引，尽量不要使用前面带*号的Like语句，不要再比较操作符前边进行计算或使用函数等等，这些道路都是对的，但经验有时候不一定能解决问题。问题出现时，首先要做的是确定问题点是什么，只有正确的找到问题后才能有针对性的解决问题。下面简单介绍我们一般从哪些角度入手，来确定问题所在。
1.首先从业务上理解该处功能，理解用户的真正意图，用户真正关注的是什么，想要的是什么数据，是否有变通简洁的方法达到用户要求。而非使用复杂sql查询。其实有些时候进行变通的修改，同样能达到目的，但是采用的sql语句已经极大地简化了。这是解决效能问题的优先要考虑的。
2.对固定的sql进行优化时，一定要关注查询相关的数据量，关注数据量的大小，有些时候用户进行一个查询，若没有处理好查询条件的话，返回的记录集合太大，这对用户来说，其实意义不大，关键是这样必然会导致较多的磁盘IO，效能问题是必然的。除非是用户真的需要这么多数据，但事实证明，多数都不是的，所以着眼点是怎样限制返回的记录集的大小或查询中使用的临时中间数据集合的大小。这样才能使你的优化达到效果，起到作用。
下面简单介绍几种常用的检查问题sql的方法。
当然其中是有些技巧的，如：
- 使用 set statistics io on 检查实际的磁盘IO信息，物理读、逻辑读等信息，这个是一个简单有效的参考数据，在笔者以往的经验中，也是主要的参考数据。
在查询分析器中贴出问题sql，使用set statistics io 为on，也可以在空白处点击右键，选择<查询选项>，
选择<高级>
勾选Set Statistics Io 。
运行查询，除了得到结果集合以外，还可以得到本次查询相关的IO信息，如下图：
我们一般关注逻辑读的次数，当多个表联合查询时，这里会现时每一个表的IO信息，当某个表的逻辑读的次数很大时，你就要重点关注和分析这个表了，是不是查询时涉及到这个表中的记录条数过多，是不是没有合理使用到Index,是不是可以增加其它的过滤条件来减少相关的记录集合等等。下面是简单说明：
输出项 含义
Table 表的名称。
Scan count 执行的索引或表扫描数。
logical reads 从数据缓存读取的页数。
physical reads 从磁盘读取的页数。
read-ahead reads 为进行查询而放入缓存的页数。
lob logical reads 从数据缓存读取的 text、ntext、image 或大值类型 (varchar(max)、nvarchar(max)、varbinary(max)) 页的数目。
lob physical reads 从磁盘读取的 text、ntext、image 或大值类型页的数目。
lob read-ahead reads 为进行查询而放入缓存的 text、ntext、image 或大值类型页的数目。
磁盘IO相关信息先介绍到这里，另外一个参考数据是使用 set statistics time on 参考显示分析、编译和执行语句所需的毫秒数。具体的使用方法同set statistics io on 基本相同，只不过显示的是本次查询所使用的分析编译、执行等的时间信息。聪明的你一定一看就明白了。在此不再赘述。
- 使用 set statistics profile on 参考显示当前语句执行的配置文件信息，执行步骤等信息，使用方法同上。
执行查询后，除了显示所执行的结果集合外，还另外显示本次sql语句执行的相关配置信息，采用记录树的形式显示，对应执行计划中的各个步骤，比如某个步骤使用的索引类型，评估行数，IO信息，时间信息等。这些信息都可以用来参考，以确定该段sql语句的问题在哪里。
参考当前语句的估计的执行计划或实际的执行计划，分析当前语句执行时SQL Server 查询优化器所选择的数据检索方法。
实际的执行计划显示了本次执行所使用的执行计划。该图应该从右向左看，由下向上看，如果是多个表连接查询的话，这里也会显示多个执行步骤，你可以检查每一个步骤相关的操作相关信息，如IO开销，CPU开销，估计的行数，有没有使用到Index，以及使用的何种Index等信息。行数过多则需要留意了。所使用的Indexl类型也是需要关注的信息之一。
下面是执行计划中一些概念的简单说明：
工具提示项 说明
Physical Operation 使用的物理运算符，例如 Hash Join 或 Nested Loops。以红色显示的物理运算符表示查询优化器已发出警告，例如丢失列统计信息或丢失联接谓词。这可能导致查询优化器选择比预期的效率低的查询计划。有关列统计信息的详细信息，请参阅使用统计信息提高查询性能。
当图形执行计划建议创建统计信息、更新统计信息或创建索引时，使用 SQL Server Management Studio 对象资源管理器中的快捷菜单可以立即创建或更新丢失的列统计信息和索引。有关详细信息，请参阅索引操作指南主题。
Logical Operation 与物理运算符匹配的逻辑运算符，如 Inner Join 运算符。逻辑运算符列在物理运算符之后，两者均位于工具提示的顶部。
Estimated Row Size 操作符生成的行的估计大小（字节）。
Estimated I/O Cost 用于执行操作的所有 I/O 活动的估计开销。此值应尽可能低。
Estimated CPU Cost 用于执行操作的所有 CPU 活动的估计开销。
Estimated Operator Cost 用于执行此操作的查询优化器的开销。此操作的开销以占查询总开销的百分比的形式显示在括号中。由于查询引擎选择最高效的操作来执行查询或执行语句，因此此值应尽可能低。
Estimated Subtree Cost 查询优化器执行此操作及同一子树内位于此操作之前的所有操作的总开销。
Estimated Number of Rows 运算符生成的行数。
综合以上介绍的几种参考信息的方法，一般都可以确定问题sql的问题所在，然后对症下药，剩下的就是进行针对性的修改了，这里只是抛砖引玉，聪明的你一定会有方法解决的。
一看时间，都将近凌晨2点啦，该睡啦，呵呵。改天再续。。。
