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

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

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

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

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

上文我们了解编译对象和重编译的起因:Correctness-Based Recompiles,Optimality-based Recompiles。今天我们继续关注如何从缓存中移除计划和计划缓存内部操作。

■从缓存中移除计划

除了基于架构或统计变化而需要重编译一个计划，SQL Server也为那些已经从计划缓存中移除的批处理重新编译计划。基于内存压力计划会从缓存中移除。然而，其他的操作也会引起计划从缓存中移除。其中的一些操作只移除某个特殊的数据库，而其他的从整个数据库实例中移除计划。

下列操作清除完全的计划缓存以使随后提交的所有的批处理依赖新的计划。注意，尽管一些操作仅仅影响一个数据库，但完整的计划缓存被清除。

■在SQL Server　2008中升级任何数据库

■运行一个DBCC FREEPROCCACHE或DBCC FREESYSTEMCACHE

■改变下列配置选项中的任何一个：

1、cross db ownership chaining

2、index create memory

3、cost  thresholds for parallism

4、max degreee of parallism

5、max text repl size

6、min memory per query

6、min server memory

7、max server memory

8、query governor cost limit

9、query wait

10、remote query

11、user options

下列操作清除所有与某个特定数据库相关的计划：

■运行一个DBCC　FLUSHPROCINDB命令

■Detaching a database

■关闭或打开一个auto-close的数据库

■使用ALTER databse…………Collate命令修改一个数据库的Collation

■使用下列命令修改数据库：
1、Alter database…………modify_Name

２、Alter database…………modify Filegroup

3、Alter database…………set online

４、Alter database…………set offline

5、Alter database…………set emergency

6、Alter database…………set read_only

7、Alter database…………set read_write

8、Alter database…………collate

■ Droping a database

从缓存创建一个计划有两种方式：一、创建一个计划向导，以准确匹配针对缓存计划的SQL文本。与文本有关的所有计划将会自动移除。SQL Server2008提供了一种简易的方式从计划缓存创建一个计划向导。后面会有详细介绍。二、在SQL Server　2008中使用DBCC FREEPROCCACHE，语法如下：

DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ]

更详细的用法，请查看MSDN:http://msdn.microsoft.com/zh-cn/library/ms174283.aspx

该命令允许你定义三个参数中的一个,以指示你想从缓存中移走一个或多个计划。

■plan_Handle

通过定义一个plan_Handle，你可以利用缓存里的句柄移除计划。Plan_Handle用以确认所有当前已经存在的计划中的惟一性。

■sql_Handle

通过定义一个sql_Handle，你可以利用缓存里的句柄移除计划。如果任何一个缓存键值改变，你可以为相同的SQL文本有多个计划，比如Set选项。演示代码如下：

USE Northwind2;
GO
DBCC FREEPROCCACHE;
GO
SET ANSI_NULLS ON
GO
SELECT * FROM orders WHERE customerid = 'HANAR';
GO
SELECT * FROM Orders WHERE CustomerID = 'CENTC';
GO
SET ANSI_NULLS OFF
GO
SELECT * FROM orders WHERE customerid = 'HANAR';
GO
SET ANSI_NULLS ON
GO

SELECT execution_count, text, sql_handle, query_plan  
FROM sys.dm_exec_query_stats
   CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS TXT
       CROSS APPLY sys.dm_exec_query_plan(plan_handle)AS PLN;
GO

 

DBCC FREEPROCCACHE(0x02000000CECDF507D9D4D70720F581172A42506136AA80BA);
GO

SELECT execution_count, text, sql_handle, query_plan  
FROM sys.dm_exec_query_stats
   CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS TXT
       CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS PLN;
GO

■pool_Name

通过定义一个资源调节器池(Resource Governor pool)的名称，你可以清除所有缓存里（指派给wrokload group的使用定义的资源池）的计划。

 

■Plan Cache Internals

知道何时和计划如何被重用和重编译将会有助于设计高性能的应用程序。你知道关于优化计划的越多，不同的实际值和基线需要不同的计划。你就越能决定何时重编译的时机。当你得到不必要的重编译，或你想让SQL Server重编译而它没有重编译时，你的排除故障将更加有效和容易。在下面的内容，我们浏览计划缓存的内部组织，可用的元数据，SQL Server如何查找一个缓存的计划，计划缓存的大小，计划回收策略(eviction policy)

■缓存存储(cache store)

在SQL Server中，计划缓存由四个分隔的内存区域组成，即缓存存储。实际上内存中还有其他存储，只是只有这四个包含计划缓存，这可以通过(一个叫sys.dm_os_memory_cache_counters)的DMV查看。下面的列名就是圆括号内的值：

1、Object Plans(CacheStore_OBJCP)

对象计划包括针对存储过程、函数、和触发器的计划。

2、Sql Plans(CacheStore_SQLCP)

SQL  计划包含针对临时缓存计划(adhoc cashed plans)、自动参数化计划和预编译计划。管理SQLCP缓存过程的内存记事员(memory clerk)，也被用于SQLManager,管理所有的（在临时查询中使用的）T-SQL文本。

3、Bound Trees(CacheStore_PHDR)

 Bound Trees是在SQL Server中代数化的结构过程，被用于视图、约束和默认值。

4、Extend Stored Procedure(CacheStore_XPROC)

扩展存储过程(Extend Procs(Xprocs))，是预定义的系统存储过程，如sp_executesql和sp_tracecreate等，被定义为使用DLL,而不是使用T-SQL语句，这些缓存结构仅仅包含实现过程的函数名称和DLL名称，

每个计划缓存存储含有一个哈希表，以保持跟踪在这个特定的存储中的所有的计划。每一个哈希表的存储桶(buckets)包含0,1和其他的缓存计划。当决定某个存储桶被使用时，SQL Server使用简单的哈希算法。哈希值＝(object_id *database_id) mod(hashtable table size)。对与adhoc和prepared计划相关的计划，object_id是一个批处理文本的内部哈希。DMV sys.dm_os_memory_cache_hash_tables包含每个哈希表的信息，包括它的大小。你可以通过下列语句查询视图以清点每一个计划缓存过程的bucket的数量：

SELECT type as 'plan cache store', buckets_count
FROM sys.dm_os_memory_cache_hash_tables
WHERE type IN ('CACHESTORE_OBJCP', 'CACHESTORE_SQLCP',
  'CACHESTORE_PHDR', 'CACHESTORE_XPROC');

 

 你应该注意到Bound Trees存储了大约10%的（存储对象计划和SQL计划的过程的）哈希bucket的数量，（64位系统中，存储存储对象计划和SQL计划的过程的哈希bucket数量大约是40000,32位系统，则为10000)。扩展存储过程的bucket数量总是被设置为127。本章中剩余部分，我们将主要讨论Object　Plans和SQL Plans

你可以使用视图sys.dm_os_memory_objects查找过程自身的大小，示例语句如下：

SELECT type AS Store, SUM(pages_allocated_count) AS Pages_used
FROM sys.dm_os_memory_objects
WHERE type IN ('MEMOBJ_CACHESTOREOBJCP', 'MEMOBJ_CACHESTORESQLCP',
  'MEMOBJ_CACHESTOREXPROC', 'MEMOBJ_SQLMGR')
GROUP BY type

 查找缓存里的一个计划有两个步骤，前面描述的哈希键引导SQL Server到一个计划可能被找到的bucket，但如果在bucket中有多个项(entries)，SQL Server需要更多的信息决定是否准确的计划能被找到。第二步，需要一个缓存键，它是计划中几个属性的结合。还记得前面我们介绍的DMF函数sys.dm_exec_plan_attributes吗？通过plan_Handle参数查询。在前文表中，有17个属性，SQL Server需要这全部17个值确保它在缓存中找到一个匹配计划。

本文主要了解从缓存中移除计划和计划缓存内部操作的第一部分－－缓存存储。下文将关注计划缓存内幕相关的编译计划、执行上下文和计划缓存元数据。