none
这种sql什么情况下会重用执行计划? RRS feed

  • 问题

  • select * from test1 where id=2和select * from test1 where id=1

    执行SELECT c.usecounts,           c.cacheobjtype,
               c.objtype,
               t.text
    FROM sys.dm_exec_cached_plans c
    CROSS APPLY sys.dm_exec_sql_text(c.plan_handle) t
    WHERE t.text LIKE '%select * from test1%' 
    发现根本就没重用执行计划。执行类型都为即席查询。那要满足什么条件才能重用执行计划呢?
    这个表只有不到十笔数据,id为1和2的都只有一行


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

    2012年2月9日 3:08

答案

  • hi jacky_shen,我相信你说的应该是对的,Adhoc对应的plan_handle的确不存在于sys.dm_exec_query_stats中。
    不过对于你回复Adhoc是假的,指向Prepared 对应的PLAN这块,有没有什么文档可以参考?

    有文档可以参考,你在下面的URL中自己找,找不到再告诉你,哈哈

    http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/ba0e06a1-3219-47c6-8225-44dd85120626/#3836ffa9-05a4-4ac9-b7d1-45e88714de84


    Hi jacky,谢谢哈,我想应该是这部分:

    the auto-parameterization can be "safe" or "unsafe". If the query processor can't find a better plan wih the constant plugged in, instead the parameterized query, then the plan can be used across different parameter values (think about parameter sniffing, for example), then the parameterization is considered "safe".

    For "unsafe" parameterizations, the query is parameterized, but the parameterized version is not cached and is not visible through any of the plan cache DMVs. The shell query points to the parameterized query, and this is the only way we can access it. If you shred the xml doc for the plan, and find the attribute "@ParameterizedText", then you can get to it. This attribute seems to be introduced in SS 2008 or later.

    这个连接解释的更清楚一些:
    http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/11/4-0-query-parameterization.aspx

    The second row contains what we call a parameterized query and has objtype of prepared. The parameterized (or prepared) query is the actual compiled plan for the query with the literal values replaced with parameters. The first row contains what we call the shell query and has objtype adhoc. As the name suggests, this entry just saves the exact adhoc batch text in cache and points to the parameterized query plan (the prepared compiled plan). In other words the shell query has a dummy compiled plan which just points to the parameterized compiled plan. The shell query is relatively small in size compared to the parameterized (or prepared query).
    There are benefits in caching the shell query: If the same query were to be re-executed, then we would compute the hash value of the sql text of the query and find an exact match in the cache i.e. the shell query. Since this shell query points to the compiled plan, the compiled plan is executed and we are done. If we had not cached this shell query and if the same query was re-executed then the steps followed would be slightly different: first we would compute the hash of the sql text of the query and not find an exact match in the cache. Next, the query is auto-parameterized. Now for this auto-parameterized query we will search the cache and find an exact match in the cache avoiding the need to go to the query optimizer. Finally we execute this compiled plan and are done. Clearly there are performance gains from caching the shell query, especially for applications that re-execute the same query with the same literal values as well. Note that we do not cache insert shell queries because the probability of re-using the exact same adhoc query is low.
    SQL Server attempts to auto-parameterize queries and sends the auto-parameterized query to the query processor. Now the query processor decides if it can generate a potentially better plan given the constant values instead of the auto-parameterized query. If the query processor cannot find a better plan with the constant value plugged in, it means that the parameterized plan can be used across different parameter values. This parameterization is considered ‘safe’. In this case we cache both the parameterized and the shell query. However if the query processor can generate a better plan with the literal values, then this parameterization is considered ‘unsafe’. The query is parameterized and the shell query is cached. The parameterized query is not cached and is not visible through any of the plan cache DMV’s. The shell query points to the parameterized query and this is the only way to get to the parameterized query. In other words, unless the exact same query (and parameter value) is re-executed there is no chance of re-using this ‘unsafe’ parameterized query. Queries that already have explicitly defined parameters are not auto-parameterized. 




    Best Regards, nicofer

    2012年2月17日 5:22

全部回复

  • Because they are ad hoc queries, and that's why should use stored proc whenever possible. 
    2012年2月9日 3:37
  • sqlserver中存在简单参数化的说法,为什么这个时候不能实现简单参数化。

    那要满足什么条件才能使用简单参数化呢?


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

    2012年2月9日 5:57
  • Hi Wison-Ho,

      有两种不同的方式来创建参数化查询.第一个方式是让查询优化器自动地参数化你的查询.另一个方式是通过以一个特定方式来编写你的T-SQL代码,并将它传递给sp_executesql系统存储过程,从而编程一个参数化查询.


    orichisonic http://blog.csdn.net/orichisonic If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    2012年2月9日 6:06
  • 联机文档描述如下:
    在简单参数化的默认行为下,SQL Server 只对相对较少的一些查询进行参数化。但是,您可以通过将 ALTER DATABASE 命令的 PARAMETERIZATION 选项设置为 FORCED,指定对数据库中的所有查询进行参数化。
    我试了下,ALTER DATABASE DB_TEST SET PARAMETERIZATION  FORCED之后,将出现缓存这样的执行计划:
    usecounts    cacheobjtype    objtype    text
    1    Compiled Plan    Adhoc    select * from test1 where id=2
    1    Compiled Plan    Adhoc    select * from test1 where id=1
    2    Compiled Plan    Prepared    (@0 int)select * from test1 where id = @0
    再次分别执行这两个语句后:
    usecounts    cacheobjtype    objtype    text
    2    Compiled Plan    Adhoc    select * from test1 where id=2
    2    Compiled Plan    Adhoc    select * from test1 where id=1
    2    Compiled Plan    Prepared    (@0 int)select * from test1 where id = @0
    然后将id改为3和4,并分别执行后:
    usecounts    cacheobjtype    objtype    text
    1    Compiled Plan    Adhoc    select * from test1 where id=4
    1    Compiled Plan    Adhoc    select * from test1 where id=3
    2    Compiled Plan    Adhoc    select * from test1 where id=2
    2    Compiled Plan    Adhoc    select * from test1 where id=1
    4    Compiled Plan    Prepared    (@0 int)select * from test1 where id = @0
    最后再执行id为3和4的查询,执行两次:
    usecounts    cacheobjtype    objtype    text
    3    Compiled Plan    Adhoc    select * from test1 where id=4
    3    Compiled Plan    Adhoc    select * from test1 where id=3
    2    Compiled Plan    Adhoc    select * from test1 where id=2
    2    Compiled Plan    Adhoc    select * from test1 where id=1
    4    Compiled Plan    Prepared    (@0 int)select * from test1 where id = @0

    这样来看,这个 Prepared    (@0 int)select * from test1 where id = @0似乎没有起到什么作用。


    Best Regards, nicofer

    2012年2月9日 6:31
  • sqlserver中存在简单参数化的说法,为什么这个时候不能实现简单参数化。

    那要满足什么条件才能使用简单参数化呢?


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

     請問你指的簡單參數化是這種樣子嗎?

    DBCC FREEPROCCACHE
    go
    
    USE Northwind
    go
    
    exec sp_executesql N'SELECT * FROM Region WHERE RegionID = @RegonID',N'@RegonID int',@RegonID = 1
    go
    
    exec sp_executesql N'SELECT * FROM Region WHERE RegionID = @RegonID',N'@RegonID int',@RegonID = 2
    go
    
    
    SELECT cp.usecounts as '使用次數'
    			,objtype as '快取類型'
    			,st.text			
    FROM sys.dm_exec_cached_plans cp
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
    WHERE st.text not like '%sys%'
    


    以上說明若有錯誤請指教,謝謝。
    http://www.dotblogs.com.tw/terrychuang/

    2012年2月9日 6:34
  • Hi TerryChuang

    http://msdn.microsoft.com/zh-cn/library/ms186219%28v=sql.90%29.aspx
    如果执行不带参数的 SQL 语句,SQL Server 2005 将在内部对该语句进行参数化,以增加将其与现有执行计划相匹配的可能性。此过程称为简单参数化。

    Best Regards, nicofer

    2012年2月9日 6:51
  • 謝謝nicofer的資訊,受教了。

    以上說明若有錯誤請指教,謝謝。
    http://www.dotblogs.com.tw/terrychuang/

    2012年2月9日 7:11
  • 当你第一次执行 select * from test1 where id=2 的时候,会产生2条PLAN,其中一条为objtype='Adhoc'的,另一条是参数化了的,它的objtype='Prepared',所以你用下面的SQL可以查到它(因为参数后的SQL语句会自己把test1改写为[test1],所以你上面的语句查询不到),并且你再次执行select * from test1 where id=1的时候会看到它的usecounts递增。

    需要说明的是objtype='Adhoc'对应的那个PLAN不是真实存在的,那只是指向objtype='Prepared'对应的那条PLAN。

    --请使用下面的语句查询

     SELECT c.usecounts,           c.cacheobjtype,
               c.objtype,
               t.text
    FROM sys.dm_exec_cached_plans c
    CROSS APPLY sys.dm_exec_sql_text(c.plan_handle) t
    WHERE t.text LIKE '%test1%'  and  c.objtype='Prepared'

    --或者下面的语句

    SELECT c.usecounts,           c.cacheobjtype,
               c.objtype,
               t.text
    FROM sys.dm_exec_cached_plans c
    CROSS APPLY sys.dm_exec_sql_text(c.plan_handle) t
    WHERE t.text LIKE '%test1%'  and   t.text LIKE '%@%'



    2012年2月9日 8:13
  • 联机文档描述如下:
    在简单参数化的默认行为下,SQL Server 只对相对较少的一些查询进行参数化。但是,您可以通过将 ALTER DATABASE 命令的 PARAMETERIZATION 选项设置为 FORCED,指定对数据库中的所有查询进行参数化。
    我试了下,ALTER DATABASE DB_TEST SET PARAMETERIZATION  FORCED之后,将出现缓存这样的执行计划:
    usecounts    cacheobjtype    objtype    text
    1    Compiled Plan    Adhoc    select * from test1 where id=2
    1    Compiled Plan    Adhoc    select * from test1 where id=1
    2    Compiled Plan    Prepared    (@0 int)select * from test1 where id = @0
    再次分别执行这两个语句后:
    usecounts    cacheobjtype    objtype    text
    2    Compiled Plan    Adhoc    select * from test1 where id=2
    2    Compiled Plan    Adhoc    select * from test1 where id=1
    2    Compiled Plan    Prepared    (@0 int)select * from test1 where id = @0
    然后将id改为3和4,并分别执行后:
    usecounts    cacheobjtype    objtype    text
    1    Compiled Plan    Adhoc    select * from test1 where id=4
    1    Compiled Plan    Adhoc    select * from test1 where id=3
    2    Compiled Plan    Adhoc    select * from test1 where id=2
    2    Compiled Plan    Adhoc    select * from test1 where id=1
    4    Compiled Plan    Prepared    (@0 int)select * from test1 where id = @0


    Best Regards, nicofer

    不,Prepared 对应的PLAN重用到了,而Adhoc  对应的那个所谓的PLAN其实一次都没有被执行,因为它根本不存在。

    你应该去查看 ys.dm_exec_query_stats中的execution_count跟  plan_handle ,你上面的情况,Adhoc  对应的plan_handle 在这里都不会存在,因为它是假的,指向只是Prepared 对应的PLAN而已,而每执行上面的任何一个语句,Prepared  对于的那条记录的execution_count都会递增。



    2012年2月9日 10:55
  • hi jacky_shen,我相信你说的应该是对的,Adhoc对应的plan_handle的确不存在于sys.dm_exec_query_stats中。
    不过对于你回复Adhoc是假的,指向Prepared 对应的PLAN这块,有没有什么文档可以参考?


    SELECT c.usecounts,           c.cacheobjtype,
               c.objtype,
               t.text,d.execution_count
    FROM sys.dm_exec_cached_plans c
    CROSS APPLY sys.dm_exec_sql_text(c.plan_handle) t
    left join sys.dm_exec_query_stats as d on c.plan_handle=d.plan_handle
    WHERE t.text LIKE '%test1%'  --and  c.objtype='Prepared'
    and t.text not like '%c.usecounts%'


    Best Regards, nicofer

    2012年2月14日 7:29
  • hi jacky_shen,我相信你说的应该是对的,Adhoc对应的plan_handle的确不存在于sys.dm_exec_query_stats中。
    不过对于你回复Adhoc是假的,指向Prepared 对应的PLAN这块,有没有什么文档可以参考?

    有文档可以参考,你在下面的URL中自己找,找不到再告诉你,哈哈

    http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/ba0e06a1-3219-47c6-8225-44dd85120626/#3836ffa9-05a4-4ac9-b7d1-45e88714de84


    2012年2月16日 10:43
  • hi jacky_shen,我相信你说的应该是对的,Adhoc对应的plan_handle的确不存在于sys.dm_exec_query_stats中。
    不过对于你回复Adhoc是假的,指向Prepared 对应的PLAN这块,有没有什么文档可以参考?

    有文档可以参考,你在下面的URL中自己找,找不到再告诉你,哈哈

    http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/ba0e06a1-3219-47c6-8225-44dd85120626/#3836ffa9-05a4-4ac9-b7d1-45e88714de84


    Hi jacky,谢谢哈,我想应该是这部分:

    the auto-parameterization can be "safe" or "unsafe". If the query processor can't find a better plan wih the constant plugged in, instead the parameterized query, then the plan can be used across different parameter values (think about parameter sniffing, for example), then the parameterization is considered "safe".

    For "unsafe" parameterizations, the query is parameterized, but the parameterized version is not cached and is not visible through any of the plan cache DMVs. The shell query points to the parameterized query, and this is the only way we can access it. If you shred the xml doc for the plan, and find the attribute "@ParameterizedText", then you can get to it. This attribute seems to be introduced in SS 2008 or later.

    这个连接解释的更清楚一些:
    http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/11/4-0-query-parameterization.aspx

    The second row contains what we call a parameterized query and has objtype of prepared. The parameterized (or prepared) query is the actual compiled plan for the query with the literal values replaced with parameters. The first row contains what we call the shell query and has objtype adhoc. As the name suggests, this entry just saves the exact adhoc batch text in cache and points to the parameterized query plan (the prepared compiled plan). In other words the shell query has a dummy compiled plan which just points to the parameterized compiled plan. The shell query is relatively small in size compared to the parameterized (or prepared query).
    There are benefits in caching the shell query: If the same query were to be re-executed, then we would compute the hash value of the sql text of the query and find an exact match in the cache i.e. the shell query. Since this shell query points to the compiled plan, the compiled plan is executed and we are done. If we had not cached this shell query and if the same query was re-executed then the steps followed would be slightly different: first we would compute the hash of the sql text of the query and not find an exact match in the cache. Next, the query is auto-parameterized. Now for this auto-parameterized query we will search the cache and find an exact match in the cache avoiding the need to go to the query optimizer. Finally we execute this compiled plan and are done. Clearly there are performance gains from caching the shell query, especially for applications that re-execute the same query with the same literal values as well. Note that we do not cache insert shell queries because the probability of re-using the exact same adhoc query is low.
    SQL Server attempts to auto-parameterize queries and sends the auto-parameterized query to the query processor. Now the query processor decides if it can generate a potentially better plan given the constant values instead of the auto-parameterized query. If the query processor cannot find a better plan with the constant value plugged in, it means that the parameterized plan can be used across different parameter values. This parameterization is considered ‘safe’. In this case we cache both the parameterized and the shell query. However if the query processor can generate a better plan with the literal values, then this parameterization is considered ‘unsafe’. The query is parameterized and the shell query is cached. The parameterized query is not cached and is not visible through any of the plan cache DMV’s. The shell query points to the parameterized query and this is the only way to get to the parameterized query. In other words, unless the exact same query (and parameter value) is re-executed there is no chance of re-using this ‘unsafe’ parameterized query. Queries that already have explicitly defined parameters are not auto-parameterized. 




    Best Regards, nicofer

    2012年2月17日 5:22
  • 哈哈,那么快就被你小子找到啦
    2012年2月17日 8:34