查询处理和执行----查询优化（2）

计划缓存和复制

一旦优化器产生计划，SQL Server就会尽力确保再次利用它。要做到这一点，就要把计划缓存起来，并采取措施确保计划尽可能被广泛地复用，而这又是通过使用参数化选项实现的。SQL Server借助参数化实现类似查询复用同一计划。参数化的查询可以通过sys.dm_exec_cached_plans。参数化由SQL Server配置选项simple或forced控制。

计划缓存创建在SQL OS提供的缓存基础设施之上。缓存存储能用于缓存各种对象。计划缓存包含几个不同的缓存存储，用于不同类型的对象。你可以通过下面的SQL查看一些缓存存储的内容：

select name, entries_count, pages_kb from sys.dm_os_memory_cache_counters
where [name] in ( 'object plans', 'sql plans', 'extended stored procedures')

要查找有long lookup times引起的性能问题，你可以查看如下SQL，建议bucket包含的对象不超过20个，查过100个就要注意了。

select * from sys.dm_os_memory_cache_hash_tables
where type in ('cachestore_objcp', 'cachestore_sqlcp', 'cacchestore_phdr', 'cachestore_xproc')

使用下面的SQL查看繁重被使用的buckets：

select bucketid, count(*) as entries_in_bucket
from sys.dm_exec_cached_plans
group by bucketid
order by 2 desc

另一种查找使用相同查询计划哈希的查询的方式是使用如下T-SQL:

select query_plan_hash,count(*) as occurrences
from sys.dm_exec_query_stats
group by query_plan_hash
having count(*) > 1

计划缓存存放Algebrizer树、编译的计划、游标执行上下文、执行上下文等各种对象。下面的SQL查看不同类型对象的统计：

select cacheobjtype, objtype, COUNT (*)
from sys.dm_exec_cached_plans
group by cacheobjtype, objtype
order by cacheobjtype, objtype

要查看执行上下文，你必须传一个plan handle给sys.dm_exec_cached_plans_dependent_objects，然后，做这个之前，你需要运行dbcc freeproccache来清空缓存（不要在生成系统执行）。先运行如下T-SQL，查看ad hoc计划的plan_handle：

select p.refcounts, p.usecounts, p.plan_handle, s.text
from sys.dm_exec_cached_plans as p
cross apply sys.dm_exec_sql_text (p.plan_handle) as s
where p.cacheobjtype = 'compiled plan'
and p.objtype = 'adhoc'
order by p.usecounts desc

将上面结果中的plan_handle带入下面T-SQL即可查看执行上下文：

select * from sys.dm_exec_cached_plan_dependent_objects
(0x060001006C032129A08D41C00100000001000000000000000000000000000000000000000000000000000000)

带入下面的T-SQL可以查看计划的属性：

select * from sys.dm_exec_plan_attributes
(0x060001006C032129A08D41C00100000001000000000000000000000000000000000000000000000000000000)

 

带入下面T-SQL可以查看sql text：

select * from sys.dm_exec_sql_text
(0x060001006C032129A08D41C00100000001000000000000000000000000000000000000000000000000000000)

编译和重新编译

编译和重新编译差不多，只是在触发次数上稍有不同。当SQL Server判定一个计划不再有效，这通常是因为schema变更、统计变更或一些其他的原因，就会重新编译计划。你可以监视编译或重新编译的发生量，通过观察PerfMon Object SQL Server: SQL Statistics，然后查看下面两个计数器：SQL compilations/sec and SQL recompilations/sec。

影响优化

有两个主要方式能够影响查询优化器--查询提示（hints）或计划向导（guides）。提示要谨慎使用，因为多少情况下SQL Server已经选择了正确的计划，对于复杂的查询或处理复杂的数据集时，使用提示或许是必要的。使用提示之前，去网上搜索“SQL Server Query Hints”，特别是Craig Fredman写的博客。不像锁定提示（SQL Server尝试满足），查询提示更强，所以，如果SQL Server不能满足查询提示，就会产生8622错误，也不会创建任何计划。以下是几个比较受关注的查询提示：

FAST <number_rows>：指定对查询进行优化，以便快速检索第一个 number_rows.。该值是非负整数。 在返回第一个 number_rows 后，查询继续执行并生成完整的结果集。

{Loop | Merge | Hash } JOIN:指定整个查询中的所有联接操作由 LOOP JOIN、MERGE JOIN 或 HASH JOIN 执行。 如果指定了多个联接提示，则优化器从允许的联接策略中选择开销最少的联接策略。要查看它是如何工作的，可以使用类似下面的语句：

MAXDOP number：对于指定了 max degree of parallelism 配置选项的查询，会覆盖 sp_configure 和资源调控器的该选项。 MAXDOP 查询提示可以超出使用 sp_configure 配置的值。 如果 MAXDOP 超出使用资源调控器配置的值，则数据库引擎会使用资源调控器 MAXDOP 值。 当使用 MAXDOP 查询提示时，所有和 max degree of parallelism 配置选项一起使用的语义规则均适用。 如果 MAXDOP 设置为零，服务器将选择最大并行度。

OPTIMIZE FOR：在编译和优化查询时指示查询优化器对局部变量使用特定值。 仅在查询优化期间使用该值，在查询执行期间不使用该值。OPTIMIZE FOR UNKNOWN指示查询优化器在编译和优化查询时使用所有局部变量的统计数据而不是初始值，包括使用强制参数化创建的参数。

RECOMPILE：指示 SQL Server 数据库引擎在执行为查询生成的计划后将其丢弃，从而在下次执行同一查询时强制查询优化器重新编译查询计划。如果未指定 RECOMPILE，数据库引擎将缓存查询计划并重新使用它们。 在编译查询计划时，RECOMPILE 查询提示将使用查询中任意本地变量的当前值，如果查询位于存储过程中，这些当前值将传递给任意参数。在只须重新编译存储过程中的一部分查询，而不是重新编译整个存储过程时，RECOMPILE 是创建使用 WITH RECOMPILE 子句的存储过程的很有用的替代方法。

USE PLAN N'xml_plan'：强制查询优化器对查询使用由 'xml_plan' 指定的现有查询计划。 不能使用 INSERT、UPDATE、MERGE 或 DELETE 语句来指定 USE PLAN。

计划向导

在SQL Server 2005就已出现，它能够让DBA在不变更查询本身的情况下影响查询的优化。典型的情况，DBA会使用计划向导试图在第三方应用数据库上调校查询执行，在这里执行的T-SQL代码是不能被改变的。计划向导在SQL Server 2008中得到增强。有3种不同类型的计划向导：

• 对象计划向导：可用于存储过程、触发器或用户自定义函数
• SQL计划向导：用于特定的SQL语句
• 模板计划向导：为特定SQL查询的参数化提供了覆盖数据库设置的一种途径

要利用计划向导，第一步是创建或捕获一个好计划；第二步是把计划应用到你想改变查询优化器行为的对象或T-SQL语句。