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

《Microsoft Sql server 2008 Internals》索引目录《Microsoft Sql server 2008 Internals》读书笔记--目录索引上文主要介绍了已编译计划、执行上下文和计划缓存元数据相关的几个常用的系统函数并介绍了几个葵花宝典级的调优语句。本文将继续关注缓存大小管理、缓存项的成本(Costing of Cache entries)■缓存大小管理我们已经了解计划重用和SQL Server如何在缓存中查找一个计划。现在我们看看SQL Server如何管理计划缓存的大小以及它如何决定在缓存中没有空间时某个计划被移除。前面已经介绍的部分全局操作类如DBCC FREEPROCCACHE会从缓存中清除所有计划而alter procrdure时会从缓存中清除所有与这个存储过程相关的计划。此外在大多数其他情况下仅仅当SQL Server面临内存压力时才会从缓存中移除计划。SQL Server用于决定何时和计划如何应该被从缓存中移除的算法称为“回收策略”(eviction policy)。每个缓存过程都有自己的eviction policy,我们仅仅讨论对象计划过程和SQL计划过程。决定哪个计划被回收是基于计划的成本后文讨论。回收在SQL Server侦探到内存压力时开始首先是零成本的计划被移除其他计划的成本减半。两种内存压力为本地内存压力和全局内存压力。讨论内存压力时我们不得不提到一个词可见内存(visible memory),可见内存是在SQL Server缓冲池中可以直接地址化的可用物理内存。在一个32位SQL Server实例中可见内存的最大值为 23GB这取决于你在boot,ini文件中是否设置/3GB标志开关。大于这个数字的带有地址的内存仅仅通过AWE-mapped-memory间接实现。而一个64位SQL Server实例中所有内存全部可以直接地址化全部是可见内存。 你可以通过一个名为sys.dm_os_sys_info的DMV的一个列bpool_visible来查看这个值这是一个8KB的buffer值。另外请注意SQL 2005的不同版本及不同的SP对应的计划缓存压力限定值均不一样即SP!与SP2对应的值不一样。SQL Server2008也是如此。 32位与64位更是不同。SQL Server版本Cache Pressure LimitSQL Server 2005 RTMSP175%的可见目标内存0-8GB50%的可见目标内存8GB-64GB25%的可见目标内存64GBSQL Server 2005 SP2SP3SQL Server 2008 RTM75%的可见目标内存0-4GB10%的可见目标内存4GB-64GB5%的可见目标内存64GBSQL Server 20004GB upper cap on the plan cache例如一个64位SQL Server 2008 RTM实例28GB目标内存。那么这个上限(Limit)将是75%*4GB10%*(28-4)GB32.45.4GB◆局部内存变量如果单个缓存存储增长太大它标示局部内存压力SQL Server开始仅仅从该存储中移除项这种行为防止一个存储占用太多的总系统内存。在单个页分配时如果缓存达到计划缓存压力上限的75%如上表所示或多个分配页时达到计划缓存压力限定的50%内部内存压力被触发计划开始从缓存中移除。如上例中如果缓存存储达到75%*5.4GB4.05GB此时某些计划开始按指定成本顺序移除如果刚好有一些计划添加到缓存这一进一出将会引起新计划的响应时间增加。除了内存数量达到压力上限外SQL Server在一个存储中计划数量达到该存储中哈希表大小的四倍时也会触发内存压力上限。如上文所示。哈希表中大约10000-40000wh Bucket也就意味着SQL存储或对象存储超过40000-160000项。下面的查询分别返回哈希表中的buckets数量和每个存储中荐的数量SELECT type as plan cache store, buckets_count FROM sys.dm_os_memory_cache_hash_tables WHERE type IN (CACHESTORE_OBJCP, CACHESTORE_SQLCP); GO SELECT type, count(*) total_entries FROM sys.dm_os_memory_cache_entries WHERE type IN (CACHESTORE_SQLCP, CACHESTORE_OBJCP) GROUP BY type; GO在SQL Server 2008之前的版本内部内存压力上限很少触发。因为在哈希表中的项数量总是被缓存存储中的计划的大小初始化。然而在SQL Server 2008中如果你启用Ad Hoc workloads优化SQL Server 缓存存储中的实际项可能非常小(每一个已编译计划存根大约是300字节因此项的数量可能急剧上升而达到上限远远大于存储的Size增长速度。如果没有启用Ad Hoc workloads优化项的Size会非常大因为每个计划的最小限制为24KB。下列查询返回缓存存储中的所有计划的大小SELECT objtype, count(*) AS number of plans, SUM(size_in_bytes)/(1024.0 * 1024.0 * 1024.0) AS size_in_gb_single_use_plans FROM sys.dm_exec_cached_plans GROUP BY objtype;◆全局内存压力(Global Memory Pressure)全局内存压力应用于缓存存储中的所有内存可能是内部的或外部的。外部全部压力发生在操作系统侦探到SQL Server需要减少内存消耗经满足其他应用程序的内存需要时总的缓存存储的size减少。内部全局内存压力在虚拟地址空间很低时发生。也可能在memory broker预测已经使用的缓存存储超过压力限定值的80%。当发生时所有缓存存储中将有项移除。正如前面提到的SQL Server侦探到内存压力时所有零成本的计划先从缓存中移除其他所有计划的总成本减半。任何特定的周期更新最多每个缓存存储的16项。不使用的从属对象比正在使用中的编译计划优先移除。从属对象包括可执行计划和游。在遇到内存压力时为这些对象使用的内存的一半被移除。记住从属对象(dependent Objects)的re-create是低成本的特别是与已编译计划相比。■缓存项的成本决定什么计划比缓存中剔除基于它们自身的成本。对于Adhoc计划成本被看作0但是每次计划被重用时增加1。对于其他类型的计划成本基于计划产生时需要的资源。当这些计划被重用(reuse)时成本被复置为原始成本。对于non-Adhoc查询成本被按单位调用的ticks度量最大值为31。有三个影响因素I/O,上下文开关和内存。最大值计算如下1、每个I/O成本1tick刻度,最大值192、上下文开关每个1tick最大值83、每16页一个tick最大值4当没有处于内存压力时成本在所有计划缓存的总大小达到缓冲池大小的50%不会减少。sys.dm_os_memory_cache_entries DMV可以展示每个缓存项的当前和原始的成本以及组成成本的组件SELECT text, objtype, refcounts, usecounts, size_in_bytes, disk_ios_count, context_switches_count, pages_allocated_count, original_cost, current_cost FROM sys.dm_exec_cached_plans p CROSS APPLY sys.dm_exec_sql_text(plan_handle) JOIN sys.dm_os_memory_cache_entries e ON p.memory_object_address e.memory_object_address WHERE cacheobjtype Compiled Plan AND type in (CACHESTORE_SQLCP, CACHESTORE_OBJCP) ORDER BY objtype desc, usecounts DESC;sys.dm_os_memory_cache_entries的详细说明请查看MSDN:http://msdn.microsoft.com/zh-cn/library/ms189488.aspx■计划缓存中的对象大图片到目前为止除了前面讨论的DMVs和DMFs,还有一个元数据对象叫syscacheobjects仅仅是伪表(pseudotable)。在SQL Server 2005之前的版本没有Dynamic Management Objects,但是我们有6个这种类型的伪表包括sysprocesses和syslockinfo其实并不占用磁盘空间仅仅当有人执行一个查询而访问他们时才会成形。这与DMO的工作方式类似。这些对象在SQL Server 2008中仍然可用。在SQL Server 2000中这些伪表只在master数据库中可用。或者当引用它们时使用完整的修饰符。在SQL Server 2008中你可以从任何数据库访问syscacheobjects,只要使用sys schema作为一个修饰符。关于sys.syscacheobjects视图的详细用法请参看http://msdn.microsoft.com/zh-cn/library/ms187815.aspx在SQL Server 2000中syscacheobjects伪表也包括针对可执行计划的项即cacheobjectype列有一个值Executable Plan。在SQL Server 2008中因为可执行计划被看作从属对象而从已编译计划中完全脱离存储通过sys.syscacheobjects视图将无法访问。为了访问可执行计划你必须通过sys.dm_exec_cached_plan_dependent_objects函数传递一个参数plan_handle。作为sys.syscacheobjects视图的一个替代方案这个兼容性视图在后续版本中将不复存在。你可以从SQL Server Management Objects中提取同样的信息。下列脚本在master中创建了一个视图sp_cacheobjects。注意在master中创建的以sp_开头的任何对象都可以被任何数据库不需要对象地访问。还有一个好处是创建的对象可以按你的需要定制它。例如可以使用一个或多个outer apply,用连接使用sys.dm_exec_query_plan函数得到缓存中每个计划的XML计划。USE master GO CREATE VIEW sp_cacheobjects (bucketid, cacheobjtype, objtype, objid, dbid, dbidexec, uid, refcounts, usecounts, pagesused, setopts, langid, dateformat, status, lasttime, maxexectime, avgexectime, lastreads, lastwrites, sqlbytes, sql) AS SELECT pvt.bucketid, CONVERT(nvarchar(18), pvt.cacheobjtype) AS cacheobjtype, pvt.objtype, CONVERT(int, pvt.objectid) AS object_id, CONVERT(smallint, pvt.dbid) AS dbid, CONVERT(smallint, pvt.dbid_execute) AS execute_dbid, CONVERT(smallint, pvt.user_id) AS user_id, pvt.refcounts, pvt.usecounts, pvt.size_in_bytes / 8192 AS size_in_bytes, CONVERT(int, pvt.set_options) AS setopts, CONVERT(smallint, pvt.language_id) AS langid, CONVERT(smallint, pvt.date_format) AS date_format, CONVERT(int, pvt.status) AS status, CONVERT(bigint, 0), CONVERT(bigint, 0), CONVERT(bigint, 0), CONVERT(bigint, 0), CONVERT(bigint, 0), CONVERT(int, LEN(CONVERT(nvarchar(max), fgs.text)) * 2), CONVERT(nvarchar(3900), fgs.text) FROM (SELECT ecp.*, epa.attribute, epa.value FROM sys.dm_exec_cached_plans ecp OUTER APPLY sys.dm_exec_plan_attributes(ecp.plan_handle) epa) AS ecpa PIVOT (MAX(ecpa.value) for ecpa.attribute IN (set_options, objectid, dbid, dbid_execute, user_id, language_id, date_format, status)) AS pvt OUTER APPLY sys.dm_exec_sql_text(pvt.plan_handle) fgs也许你注意到在几个输出列已经被硬编码为0因为这些列在SQL Server 2005/2008中已经不再保留。特别这些列被用于为缓存计划保存性能信息的报告列。在SQL Server 2000中这些性能数据被为每个批处理保留。在后续版本中它被保留在语句级并且可以通过sys.dm_exec_query_ststs可用。为了兼容sys.syscacheobjects视图新的视图必须在特定列位置返回某些值。如果你选择定制视图你可以选择移除这些列。至此计划缓存的内部操作告一段落我们将继续关注计划缓存的时机和计划缓存冲突。邀月注本文版权由邀月和CSDN共同所有转载请注明出处。助人等于自助! 3wlive.cn