转载自：
http://blogs.msdn.com/b/apgcdsd/archive/2013/03/13/10401823.aspx
SQL server运行到一定的时候, 执行计划的缓存可能会相当大,有些能到几个GB的大小。这个时候假设某个语句比较复杂而且SQL server 生成的执行计划不够优化，你希望把该执行计划的缓存清除使得SQL server能够重新编译该语句。该如何做呢？
如果是存储过程则很好办，直接使用sp_recompile就可以了，如下所示。如果参数是表，那么所有用到该表的存储过程或trigger都会重新编译，从而把原来的plan 替换掉：
|
1
2
3
4
|
USE AdventureWorks;
GO
EXEC
sp_recompile N
'Sales.Customer'
;
GO
|
如果是一般的语句呢？ 比如下面的语句:
|
1
2
3
4
5
6
|
use AdventureWorks
go
SELECT
*
FROM
Sales.SalesOrderHeader h, Sales.Customer c,Sales.SalesTerritory t
WHERE
h.CustomerID = c.CustomerID
AND
c.TerritoryID = t.TerritoryID
AND
CountryRegionCode = N
'CA'
;
|
我执行上面的语句几次后，观察下执行计划的缓存：
|
1
2
3
4
5
6
7
8
|
SELECT
usecounts,text,plan_handle,*
FROM
sys.dm_exec_cached_plans cp
CROSS
APPLY sys.dm_exec_query_plan(cp.plan_handle)
CROSS
APPLY sys.dm_exec_sql_text (cp.plan_handle)
where
textlike
'%SELECT * FROM Sales.SalesOrderHeader h, Sales.Customer c,Sales.SalesTerritory t
WHERE h.CustomerID = c.CustomerID
AND c.TerritoryID = t.TerritoryID
AND CountryRegionCode%'
;
|
得到结果如下：
Usecounts表示该语句被执行了7次。 如果这个语句的执行计划不好，如何删除它呢？
如果是SQL server 2008 R2就很好办，直接使用DBCC FREEPROCCACHE然后传入plan handle如即可，如：
|
1
|
DBCC FREEPROCCACHE(0x060001002903DC0B4001B887000000000000000000000000)
|
但是SQL server 2005的FREEPROCCACHE并没有这个用法。 SQL 2005里面如果运行DBCC FREEPROCCACHE那么所有的缓存都会被清空了。这对性能的影响比较大，因为SQL server 要对所有的语句重新编译然后重新生成缓存。SQL server 2005里面有没有其他方法只清除特定的语句的缓存呢？
有的，答案就使用使用plan guide如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
sp_create_plan_guide
@
name
= N
'recompile_Guide'
,
@stmt =
N
'SELECT * FROM Sales.SalesOrderHeader h, Sales.Customer c,Sales.SalesTerritory t
WHERE h.CustomerID = c.CustomerID
AND c.TerritoryID = t.TerritoryID
AND CountryRegionCode = N'
'CA'
';'
,
@type = N
'SQL'
,
@module_or_batch =
NULL
,
@params =
NULL
,
@hints = N
'OPTION (RECOMPILE)'
go
exec
sp_control_plan_guide N
'drop'
,N
'recompile_Guide'
|
上面的sp_create_plan_guide使用RECOMPILE参数，意思是说，每次碰到该语句，必须重新编译。sp_create_plan_guide运行后，该语句的执行计划缓存就被删除了，下次该语句再次执行就会重新编译。那么我为什么马上又删除这个plan guide呢？因为该语句的缓冲被清除后，我不希望该语句每次执行都重新编译，所以我删除了它，毕竟我执行sp_create_plan_guide的目的是删除该语句的执行计划缓存而已。所以如果你使用同样的手段，务必记得立即把sp_create_plan_guide建立的guide删除。
更新统计信息
转载自：https://msdn.microsoft.com/zh-cn/library/hh510198.aspx
更新特定的统计信息对象
|
1
2
3
4
5
|
USE AdventureWorks;
GO
-- The following example updates the statistics for the AK_SalesOrderDetail_rowguid index of the SalesOrderDetail table.
UPDATE
STATISTICS
Sales.SalesOrderDetail AK_SalesOrderDetail_rowguid;
GO
|
更新表中的所有统计信息
|
1
2
3
4
5
|
USE AdventureWorks;
GO
-- The following example updates the statistics for all indexes on the SalesOrderDetail table.
UPDATE
STATISTICS
Sales.SalesOrderDetail;
GO
|
更新数据库中的所有统计信息
|
1
2
3
4
|
USE AdventureWorks;
GO
-- The following example updates the statistics for all tables in the database.
EXEC
sp_updatestats;
|
提示（Hints）
转载自：https://msdn.microsoft.com/zh-cn/library/ms187713.aspx
https://msdn.microsoft.com/zh-cn/subscriptions/downloads/aa196160%28v=sql.80%29.aspx
提示分为：
-
联接提示（Join Hints）
-
查询提示（Query Hints）
-
表提示（Table Hints）
总结：
分析有性能问题的语句的时候，常常要根据它的执行计划去分析性能消耗高的部分，定位到问题到底出在哪里。
有的时候是需要更新统计信息；有的时候是需要优化SQL语句加上HINT提示。
最后查看SQL语句的执行计划是否得到优化。
本文转自UltraSQL51CTO博客，原文链接： http://blog.51cto.com/ultrasql/1626692，如需转载请自行联系原作者
