《Microsoft Sql server 2008 Internals》读书笔记--第九章Plan Caching and Recompilation(1)

 

《Microsoft Sql server 2008 Internals》读书笔记订阅地址：

http://www.cnblogs.com/downmoon/category/230397.html/rss

《Microsoft Sql server 2008 Internals》索引目录：

《Microsoft Sql server 2008 Internal》读书笔记--目录索引

 

通过第八章的查询优化，我们大致了解SQL Server中的查询优化进程及查询执行的一些细节。因为查询优化通常是复杂和耗时的进程，SQL Server频频受益于重用已经生成并保存在计划缓存中的查询计划，而不是每次都产生一个新计划。当然，如果前一个计划不理想，SQL Server还是会生成一个新的计划。当然也可以强制生成新计划。

在这一章，我们将关注SQL Server　2008的计划缓存，了解它是如何组织的。

■计划缓存(The plan Cache)

在SQL Server　2008中，计划缓存并不是一块隔离的内存区域。早期的版本（到SQL Server7）中有两个配置值会影响到计划缓存的大小，即所谓的Procedure Cache。其中一个值定义SQL Server中可用内存的固定大小，另一个定义用于存储存储过程计划的内存的百分比。在早期的版本（到SQL Server7）中，针对临时添加的SQL语句的查询计划从来不会被缓存。仅仅缓存存储过程。这就是为什么在旧版本中，它被称作过程缓存。在SQL Server　2008中，内存的总大小是动态的，查询计划使用的空间也是非常宽泛的。

■计划缓存元数据

我们并不需要记忆过多的语句，一个视图足以帮我们了解计划缓存元数据。

sys.dm_exec_cached_plans，关于它的详细说明，请看MSDN:http://msdn.microsoft.com/zh-cn/library/ms187404.aspx
例如，我们想要知道计划缓存的使用数，可以使用如下语句：

SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
        CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
        AND [text] NOT LIKE '%dm_exec_cached_plans%'; 

 ■清除计划缓存

SQL Server　2008会倾向于缓存每一个查询，缓存中的计划数会变得非常大。有一个很有效的机制（本章后面会提到。大量的计划数会不会直接影响系统的性能。然而如果拥有数量众多的类似计划时，SQL Server的查找一个正确计划的时间成本有时可能是高昂的。而且，出于测试和解决冲突的观点，很多的计划使得我们找到我们目前关注的计划变得困难了。SQL Server提供了一种机制以清除计划缓存中的计划。你可以使用如下命令：
1、DBCC FreeProCache
从内存中清除所有缓存计划。可以带参数，详细用法，请参看MSDN:
http://msdn.microsoft.com/zh-cn/library/ms174283.aspx

2、DBCC FreeSystemCache
从所有缓存中释放所有未使用的缓存条目。详细用法，参考MSDN:
http://msdn.microsoft.com/zh-cn/library/ms178529%28SQL.90%29.aspx

SQL Server　2008会倾向于缓存每一个查询，缓存中的计划数会变得非常大，因而需要一个很有效的机制（本章后面会提到)。大量的计划数不会直接影响系统的性能，然而如果拥有数量众多的类似计划时，SQL Server查找一个正确计划的时间成本有时可能是高昂的。而且，出于测试和解决冲突的观点，很多的计划使得我们找到我们目前关注的计划变得困难了。SQL Server提供了一种机制以清除计划缓存中的计划。你可以使用如下命令：
1、DBCC FreeProCache
从内存中清除所有缓存计划。可以带参数，详细用法，请参看MSDN:
http://msdn.microsoft.com/zh-cn/library/ms174283.aspx
2、DBCC FreeSystemCache
从所有缓存中释放所有未使用的缓存条目。详细用法，参考MSDN:
http://msdn.microsoft.com/zh-cn/library/ms178529%28SQL.90%29.aspx
3、DBCC FlushprocINDB(<dbid>)
这是一个未公开的内部命令。清除特定数据库的缓存，注意前面显示的SQL语句并没有提供dbid，你可以从函数sys.dm_exec_SQL_Text中获取可用的dbid
需要提醒的是：并不推荐你手工修改计划缓存，这可能会影响不应用程序的性能。
■缓存机制

 

SQL Server能通过四个机制使计划缓存可访问而不重新编译以前的执行过的查询：
1、临时查询缓存(Adhoc query caching)

2、自动参数化

3、使用sp_executesql或通过API（调用预编译或执行方法）预编译。

4、存储过程或其他编译对象（如触发器、TVFs等）
为了决定哪个机制会在缓存中被计划采用。我们需要sys.dm_exec_cached_plans视图中观察cacheobjtype和objtype的值。cacheobjtype的值可能是下列6个之一：
1、Compiled Plan

2、Compiled Plan Stub

3、Parse Tree

4、Extended Proc

5、CLR Complied Func

6、CLR Complied Proc

在这一部分，我们只关注Compiled Plan和Compiled Plan Stub

objtype的值可能是下列11个之一：

1、Proc(存储过程）

2、Prepared(预声明语句)

3、Adhoc(临时查询)

4、ReplProc(复制筛选过程Replication-Filter-Procedure)

5、Trigger

6、View

7、Default(默认约束或默认对象）

8、UsrTab(用户表）

9、SysTab(系统表）

10、Check(Check约束)

11、Rule(规则对象）

我们将关注前三个值。

■临时查询缓存(Adhoc Query Caching)

  若cacheobjtype为Compiled Plan,且objtype为Adhoc,则这个计划被考虑作一个Adhoc计划。在SQL Server　2005中，Adhoc计划偶尔被缓存，然而，即便缓存，你也无法重用该计划。当SQL Server从一个Adhoc查询中缓存计划时，缓存计划仅当被子序列处理准确匹配时才可重用。
示例数据库NorthWind2下载：
http://www.sqlserverinternals.com/companion2008/SQL%20Server%20Sample%20Databases.zip

 

USE Northwind2;
DBCC FREEPROCCACHE;
GO
SELECT * FROM Orders WHERE CustomerID = 'HANAR';
GO
SELECT * FROM Orders WHERE CustomerID = 'CHOPS';
GO
SELECT * FROM Orders WHERE CustomerID = 'HANAR';
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
        CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
        AND [text] NOT LIKE '%dm_exec_cached_plans%';

结果：

结果表明：根据CustomID值的变化，同样的计划不能被重用。然而，想重用Adhoc查询计划，你需要同时确认查询中使用相同的CustomerID值，且查询语句完全相同的，字符对字符。如果一个查询包含一个新行或一个特殊的空格，而另一个没有，则不相同。如果一个查询包含注释，而另一个不包含，则也不同。而且，如果一个查询使用不同的大小写，即便数据库设置忽略大小写，也还是不同的字符。我们看下面例子：

USE Northwind2;
DBCC FREEPROCCACHE;
GO
SELECT * FROM orders WHERE customerID = 'HANAR'
GO
-- Try it again
SELECT * FROM orders WHERE customerID = 'HANAR'
GO
SELECT * FROM orders
WHERE customerID = 'HANAR';
GO
SELECT * FROM Orders WHERE CustomerID = 'HANAR'
GO
select * from orders where customerid = 'HANAR'
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
        CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
        AND [text] NOT LIKE '%dm_exec_cached_plans%'; 

 结果：

 注意：如果没有Go,则将是一个批处理语句。还有几种语句总是被考虑作为adhoc,这个结构包含以下：
1、一个使用”EXEC”的语句，如”EXEC(select FirstName,LastName,Title from Employee where EmployeeID=6)”

2、一个使用sp_executeSql提交的语句，在没有参数提供时。

而通过应用程序的sp_prepare和sp_prepexec提交的查询不会被考虑为Adhoc
下文将关注Adhoc workload和Parameterization