none
Determine Plan Execuction counts RRS feed

  • Question

  • Fellow SQLers,

    So, lets say we have a simple paramertized prepared sql statment - not an sp.

    There are a number of dmvs I used to determine usage, counts, etc for reviewing poor performing queries.

    Two of them are:

    sys.dm_exec_query_stats - which has a field "execution_counts" which BOL indicates is "Number of times that the plan has been executed since it was last compiled."

    sys.dm_exec_cached_plans - which has a field "usecounts" which BOL indicates is "Number of times this cache object has been used since its inception.

    Obviously the big difference is the definition.

    I have come across a scenario where the usecount is 112 and the execution count is 1.

    The query is something like this:

    select count(1) from V_customer

    where  (  ( status1 = @P1 )

    AND (changed_date >= @P2 and changed_date < @P3 ) AND (proj = @P4 )  )                                 

    Why would the numeric differnce occur?  Am I incorrect to interpret that the cached object in the definition for  usecounts is the plan?

    thanks 
    MG



    • Edited by mg101 Thursday, August 9, 2018 2:44 PM wording
    Thursday, August 9, 2018 2:40 PM

Answers

All replies

  • Hi MG,

    In SQL Server, whenever a query is run for the first time. It will be compiled and a new query plan will be generated for it. This query plan is stored in SQL Server query plan cache. Then when that query is ran again, SQL Server will use the cached query plan and no need to create another query plan. This is how SQL Server improve the database performance.

    So in your scenario, per my understanding the execution count = 1 is only one query plan be generated and be used. And usecount = 112 means the query plan cache has been used for 112 times.

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 10, 2018 3:02 AM
    Moderator
  • As far as I can see, for rows in sys.dm_exec_cached_plans that represent queries that would be in sys.dm_exec_query_stats, the values of "execution_count" and "usecounts" are the same. I used the following query to check:

    SELECT qstat.[execution_count], qplan.[usecounts],
           txt.[text],
           '---' AS [---], qstat.*,
           '---' AS [---], qplan.*
    FROM   sys.dm_exec_query_stats qstat
    INNER JOIN sys.dm_exec_cached_plans qplan
            ON qplan.[plan_handle] = qstat.[plan_handle]
    OUTER APPLY sys.dm_exec_sql_text(qstat.[sql_handle]) txt;
    

    Now, if you are seeing that there is a difference, especially with the query (not the plan) being "1", I would venture to guess that perhaps the object containing the query stat info for that query got evicted from memory, but not the plan.

    I also thought it was possible that multiple queries could use the same query plan / "plan_handle", but I am not seeing any evidence of that (yet). If that did happen, however, then the following query would return the instances of that happening:

    SELECT stat.[plan_handle], COUNT(DISTINCT stat.[sql_handle]) AS [NumSqlHandles]
    FROM sys.dm_exec_query_stats stat
    GROUP BY stat.[plan_handle]
    HAVING COUNT(DISTINCT stat.[sql_handle]) > 1;
    

    Monday, August 13, 2018 8:33 PM
  • Thanks Solomon and XI,

    I reached out to Grant Fritchey who explained. I think it is really about the definintion of the counts.

    Usecounts is how many times the code has been looked up. The execution_count is the number of times it has been used in execution. The lookup can be incremented multiple times during the life of the plan. You can see use count definition here: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-cached-plans-transact-sql?view=sql-server-2017

    Thanks Grant.

    • Marked as answer by mg101 Tuesday, August 14, 2018 1:08 PM
    Tuesday, August 14, 2018 1:08 PM