这些天看了一篇微软官方发布的MS SQL Server2008性能问题处理及优化的英文文档，里面知识点介绍地很详细，在现实工作中也很实用，遂产生了想把它翻译一下的念头。翻译的过程，既可以帮助自己复习一下这些技术，也可以向其他还不熟悉这一块的朋友介绍一些新的知识，何乐而不为呢。只是这篇文章有点长，我会分成几篇随笔去介绍，所以，不光是对我耐性的考验，也是对你的考验哦！
--------------------------------------------
低效率的查询计划
当为一个查询生成一个执行计划时，SQL Server 优化器尝试为这个查询选择一个拥有最快响应时间的计划。注意，最小的响应时间并不意味着最小的I/O消耗，也不意味着说使用了最小数量的CPU，它只是多多种资源的一个平衡。
这些类别的操作会比其它的操作使用更多的CPU。就其本质而言，Hash操作和Sort操作会遍历整个输出的数据。如果在这种遍历之前进行一个read-ahead（预读取），那么在执行这些操作之前，这些数据页就已经总是在缓存中持续可用。所以，等待物理I/O的可能性就很小或者可以忽略了。如果这些类型的操作不再对物理I/O造成影响，它们就会变得开始大量消耗CPU。相比之下，嵌套的遍历连接有很多的索引查询，并且如果索引查询要穿越表的好多个部分，就会导致这些数据页不能被放入缓存中，以至于快速地达到I/O限制。
优化器用来评估不同查询计划的最有效的输入是对于不同操作的一个基估计，你可以从Showplan中查找（EstimateRows和EstimateExecutions 属性）。离开了精准的基估计，在优化中的主要输入也是有缺陷的，并且许多时候这就是最终的计划。
这里有一个描述SQL Server 如何使用统计信息的白皮书，可以查看：Statistics Used by the Query Optimizer in Microsoft SQL Server 2008.这个白皮书阐述了优化器如何使用统计信息，并且一些设计的通病会避免生成有效的基预测以致导致低效率的查询计划。
诊断
低效率的查询计划通常比较地去诊断。一个低效率的查询计划可以增加CPU的使用。
下面这个使用到sys.dm_exec_query_stats的查询，是确定哪个查询最消耗CPU的一种高效方式。
select highest_cpu_queries.plan_handle, highest_cpu_queries.total_worker_time, q.dbid, q.objectid, q.number, q.encrypted, q.text from (select top 50 qs.plan_handle,qs.total_worker_time from sys.dm_exec_query_stats qs order by qs.total_worker_time desc) as highest_cpu_queries cross apply sys.dm_exec_sql_text(plan_handle) as q order by highest_cpu_queries.total_worker_time desc
交互地，你可以通过sys.dm_exec_cached查询不同目的的CPU密集使用情况，例如‘%Hash Match%’，‘%Sort%’。
解决方案
考虑通过以下方式去确定低效率的查询计划：
--通过使用Database Engine Tuning Advisor去查看它是否会呈现出任意关于索引的建议。
--检查有问题的预估计。
--是否已经写好的查询都是用了最合适的where谓词？无约束的查询只会按照本身的自然特性运行。
--将对表的UPDATE STATISTICS添加到查询语句中，并去查看问题是否还存留。
--查询是否使用了优化器无法进行预估计的结构？考虑一下，这个查询是否可以按照某种方式进行修改以避免相关的问题。
--如果不可能去更改对应的结构或者查询，你可以使用plan guide新特性去指定查询提示以对应这些文本标准。Plan guides可以给单个的语句创建，效果就像是存在于存储过程中的语句一样。例如OPTION(OPTIMIZE FOR)可以在保留优化器的全部潜在计划的同时去影响查询的预估计。其它的提示，比如OPTION(FORCE ORDER)或者OPTION(USE PLAN)提供给你更多角度控制查询计划。SQL Server 2008提供了完整的DML plan guide支持，这也就意味着它可以用在SELECT,INSERT,UPDATE,DELETE或者MERGE语句中。
--SQL Server 2008同样提供了一个新特性叫做plan freezing，它允许你分解一个存在于缓存计划中的计划。这个选项跟使用USE PLAN查询提示创建plan guide很相似。然而，它省去了在创建plan guides时生成长串命令的需要。它同样最小化了用户在使用较长查询命令时可能会出现的错误。例如，呈现在下面的简单的两语句批处理，就很需要按照对应的文本标准进行分解。
declare @plan_handle varbinary(64); --Extract the query's plan_handle select @plan_handle=plan_handle from sys.dm_exec_query_stats as qs cross apply sys.dm_exec_sql_text(sql_handle) where text like N'Some query matching criteria%'; execute sp_create_plan_guide_from_handle @name =N'Sample_PG1', @plan_handle = @plan_handle, @statement_start_offset =NULL; go
