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

《Microsoft Sql server 2008 Internals》索引目录《Microsoft Sql server 2008 Internals》读书笔记--目录索引在这一节我们将继续关注Adhoc和参数化■Adhoc混合负载的优化如果你的查询大多数是临时(adhoc)的从来不会被重用似乎浪费内存去缓存这些计划了。SQL Server 2008增加了一个配置选项在这种情况下也能满足你的需求。一旦选项启用仅仅在第一次任何一个adhoc查询被编译时缓存一个存根在第二次编译后存根用以取代全部计划。控制为Ad Hoc混合负载设置的优化启用选项方法有二1、EXEC sp_configure optimize for ad hoc workloads, 1; RECONFIGURE;2、 在SQL Server Management Studio界面高级属性中启用。编译计划存根当 optimize for ad hoc workloads启用时SQL Server缓存的存根仅仅大约300字节并不包含查询计划的任何部分。它基本上仅仅是一个容器以保持跟踪某一特殊的查询的前次编译。这个存根包含了全部的缓存键和一个指向实际查询文本的指针。这个存根被存在SQL Manager缓存中。该存根的usecounts值始终是1因为从来不会重用。当生成编译计划存根的一个查询或批处理被重新编译时这个存根被全编译计划代替。初始usecounts被设置为1因为不确定前一个查询是否与执行计划完全精确相同。我们看一个例子EXEC sp_configure optimize for ad hoc workloads, 1; RECONFIGURE; GO USE Northwind2; DBCC FREEPROCCACHE; 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 LIKE Compiled Plan% AND [text] NOT LIKE %dm_exec_cached_plans%; 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 LIKE Compiled Plan% AND [text] NOT LIKE %dm_exec_cached_plans%; GO如果关闭选项执行EXEC sp_configure optimize for ad hoc workloads, 1; RECONFIGURE; GO■简单参数化(Simple Parameterization)对于确定的查询SQL Server能够决定是否需要把一个或多个常量参数化此时遵循相同基本模板的序列查询能够使用相同的计划。例如对于下列查询使用相同的计划SELECTFirstName, LastName, TitleFROMEmployeesWHEREEmployeeID6;SELECTFirstName, LastName, TitleFROMEmployeesWHEREEmployeeID2;SQL Server 在内部参数化这两个参数如下SELECTFirstName, LastName, TitleFROMEmployeesWHEREEmployeeID1;USE Northwind2 GO DBCC FREEPROCCACHE; GO SELECT FirstName, LastName, Title FROM Employees WHERE EmployeeID 6; GO SELECT FirstName, LastName, Title FROM Employees WHERE EmployeeID 2; 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查询结果类似如下你应该注意到两个独立的查询由于不同的常量作为临时查询得到了缓存。然而这些仅仅被考虑为shell queries(盒子查询它的缓存仅仅使得在以后相同常量的精准查询而可能被重用时查找查询的参数化版本容易一些。这个shell查询不包含全部查询计划而仅仅在相关预备计划中有一个指向全部计划的指针。注意不要把shell查询和计划存根混淆。一个shell查询包含查询的完整文本使用至少16K内存。Shell查询仅仅被SQL Server认为可参数化的计划创建。而计划存根(plan stub)仅仅在内存中使用200字节为不可参数化的、临时查询所创建并且仅仅当optimize for ad hoc workloads选项设置为时生效。上图中注意第三行objtype为Prepared注意useCounts为2或4意味着一个查询被重用了两次或四次。默认情况下SQL Server对于决定参数自动化是非常保守的。仅仅当查询模板被考虑为安全时才会自动参数化。如果即便实际参数值变化时一个查询计划也不改变那么这个模板是安全的。这个安全原则确保参数化不会消弱查询的性能。上面查询中Employee表有一个惟一的索引因此任何使用相等比较式的EmployeeID不会有两行。在惟一索引中使用seek是非常有用的无论实际值如何。然而考虑到查询可能是一个不等比较式或一个不惟一的列的相等比较式时。这种情况下一些实际查询值会返回多行或没有行或一行。因此一个查询是否最好的计划取决于查询使用的值是否被认为是安全的是否参数化。这是整个查询优化的核心。除了需求一个查询模板仅仅一个可能的计划外还有许多查询结构不允许简单参数化如下1、JOIN2、BLUK INSERT3、IN 列表4、UNION5、INTO6、FOR BROWSER7、OPTIONquery hints8、DISTINCT9、TOP10、WAITFOR语句11、GROUP BY,HAVING,COMPUTE12、全文谓词13、序列化(subqueies)14、FROM子句Select语句含有表变量方法或全文表或OPENROWSET或OPENXML或OPENQUERY或OPENDATASOURCE15、来自于比较谓词简单参数化在使用下列结构的修改语句时被禁用1、带from子句的Delete/update2、带含变量的set子句的Update■强制参数化(Forced Parameterization)如果你的应用程序使用相同的查询使用相同的计划明显会改善性能但却没有自动参数化。或SQL Server出于安全考虑或使用了禁止的结构而没有采用参数化。SQL Server 2008提供了一种替代方案一个数据库选项(Parameterization forced)ALTER DATABASE database_name SET PARAMETERIZATION FORCED;该选项启用时SQL Server将常量视作参数有一些例外1、INSERT...EXECUTE语句2、存储过程、触发器、或用户自定义函数内的语句。SQL Server已经为这些程序重用查询计划。3、在客户端已经参数化的预备语句4、包含XQuery方法调用的语句Statements that contain XQuery method calls, where the method appears in a context where its arguments would typically be parameterized, such as a WHERE clause. If the method appears in a context where its arguments would not be parameterized, the rest of the statement is parameterized.5、T-SQL游标中的语句(API游标内部的Select语句被参数化6、已经被废弃的查询结构7、任何运行在(ANSI_PADDING or ANSI_NULLS设置为Off的上下文的任何语句8、包含超过2097个Literals(直接常量)的语句9、引用变量如Where T.col2p10、包含RECOMPILE查询暗示的语句11、包含一个COMPUTE子句的语句12、包含一个WHERE CURRENT OF的子句你需要谨慎为数据库设置这个选项因为在优化期间所有的常量都参数化那么频繁重用已经重用的计划将会导致性能急剧下降。下文将继续关注简单参数化和预查询(Prepared Quries)邀月注本文版权由邀月和CSDN共同所有转载请注明出处。助人等于自助! 3wlive.cn