locked
Procedure cache question RRS feed

  • Question

  • Does SQL cache the entire TSQL batch or each individual TFS statement in a batch?  For cache match purposes, does it match on the entire batch or each statement within the batch will attempt to match a previously cached plan?


    When I batch together two TSQL queries in mgmt studio and query the dmvs (dm_exec views/functions), I get two separate rows back where each row has a a different plan_handle, sql_handle, query_hash,query_plan_hash, and text.  The text for each row represents a single query statement than the entire batch as the MSDN docs suggest.

    select * from mytable1 where id = 1

    select * from mytable2 where id = 2

    go

    SELECT 
    cp.objtype
    ,qs.plan_handle
    ,qs.SQL_HANDLE
    ,QS.query_hash
    ,QS.query_plan_hash
    ,ST.[TEXT]
    ,cp.usecounts
    ,QS.EXECUTION_COUNT
    ,qs.total_physical_reads
    ,qs.total_logical_reads
    ,P.query_plan
    FROM [SYS].[DM_EXEC_QUERY_STATS] AS [QS] 
    INNER JOIN SYS.dm_exec_cached_plans cp on cp.plan_handle = qs.plan_handle
    CROSS APPLY [SYS].[DM_EXEC_SQL_TEXT]([QS].[SQL_HANDLE]) AS [ST] 
    CROSS APPLY [sys].[dm_exec_query_plan]([qs].[plan_handle]) as [p]
    WHERE [st].[text] like '%mytable1%' or [st].[text] like '%mytable2%'
    ORDER BY 1, [qs].[execution_count] desc;

    go

    The MSDN docs suggest that sql handle from dm_exec_query_stats represent a given TSQL batch of statements.   For caching purposes what constitutes a batch?

    SQL2008



    • Edited by scott_m Thursday, December 26, 2013 9:52 PM
    Thursday, December 26, 2013 9:42 PM

Answers

  • SQL Server caches the plan for the entire batch, the match when looking for a cache entry is based on a hash that is computed over the entire batch. Note that the hash is computed over the batch text as-is. That is, everything counts: spaces, comments, and lowercase and uppercase counts differently.

    But that is not all. If two users submits the same query batch, and the batch includes one or more table references where the schema is not specified, and the users have different default schema, that will result in two cache entries.

    Furthermore, there are a number of SET options that must match for a cache hit. For instance, different settings for ARITHABORT will result in two cache entries.

    As I said, SQL Server initially compiles a plan for the entire batch. However, during execution, recompiles may occur for a number of reasons, and recompilation is on statement level. This causes the part of the plan to be replaced, and as I recall the plan_handle remains the same.

    What happens in your case, is something called autoparameterisation. You may note that the query text in the cache has changed, and reads:

    (@1 tinyint)SELECT * FROM [dbo].[mytable2] WHERE [id]=@1

    That is not what you submitted. If you take a query batch where autoparameterisation does not occur, you will still see two entries in the output, because there is always one row per statement, but the sql_handle and plan_handle will be the same. For instance try this:

    create table mytable1 (id int NOT NULL)
    create table mytable2 (id int NOT NULL)
    go
    DBCC FREEPROCCACHE
    go
    select * from dbo.mytable1 where id in (SELECT id FROM dbo.mytable2)
    
    select * from dbo.mytable2 where id in (SELECT id FROM dbo.mytable1)
    go
    
    SELECT
    cp.objtype
    ,qs.plan_handle
    ,qs.sql_handle
    ,qs.statement_start_offset
    ,qs.statement_end_offset
    ,qs.query_hash
    ,qs.query_plan_hash
    ,st.[text]
    ,cp.usecounts
    ,qs.execution_count
    ,qs.total_physical_reads
    ,qs.total_logical_reads
    ,p.query_plan
    FROM [sys].[dm_exec_query_stats] AS [qs]
    INNER JOIN sys.dm_exec_cached_plans cp on cp.plan_handle = qs.plan_handle
    CROSS APPLY [sys].[dm_exec_sql_text](qs.[sql_handle]) AS st
    CROSS APPLY [sys].[dm_exec_query_plan]([qs].[plan_handle]) as [p]
    WHERE [st].[text] like '%mytable1%' or [st].[text] like '%mytable2%'
    ORDER BY 1, [qs].[execution_count] desc;
    go
    DROP TABLE mytable1, mytable2

    I have added the column statement_start_offset and statement_end_offset, so that you can see the entries are per statement.

    By the way, all the DMVs are spelled in lowercase only, and I recommend that you stick to this. One day, you may need to run your queries on a case-sensitive system, and things like SYS.DM_EXEC_QUERY_STATS will not work for you in this case.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Fanny Liu Friday, December 27, 2013 1:23 AM
    • Marked as answer by scott_m Friday, December 27, 2013 2:11 AM
    Thursday, December 26, 2013 10:42 PM