Post reply

query plan related questions

  • November 5, 2019 at 10:41 am

    #3695058

    Hi All,

    I have few questions on query plans.

    1. Wanted to check for how long a query plan will stay in the plan cache? On what scenarios a plan can be removed from the plan cache?

    2. for some reasons, a query which was running fine, now it is not running as in expected? For this, I want check the plan cache and see if there are new plans for that query has been generated or not ?

    3. How to remove a specific plan from the plan cache.? Can we do it in SQL 2016 EE?

    4. Can we take the plan from sub-prod env and use the plan guide in prod? Asking this because the same query with same data is running fast within 30 secs and the same query taking ~45 mins.

    Is that a fair comparison to make between sub-prod and prod ? I know the workload changes and many other factors can make the query slow but want to see if this works or not. please let me know if that is a good thing to do?

    Environment

    ===========

    SQL 2016 EE

    I am using below query to get the plans from the plan cache.

    SELECT

    @@servername as Servername

    ,[st].[text]

    , [qs].[execution_count]

    , [qs].plan_generation_num

    ,[qs].creation_time

    ,[qs].last_execution_time

    ,[qs].[sql_handle]

    ,[qs].plan_handle

    ,p.query_plan

    ,qs.query_hash

    ,qs.query_plan_hash

    , [qs].*

    , [p].*

    FROM [sys].[dm_exec_query_stats] AS [qs]

    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 '%vw_taxware%'

    and [st].[text] not like '%[sys].[dm_exec_query_stats]%'

    ORDER BY 1, [qs].[execution_count] DESC;

    GO

    Thanks,

    Sam

  • November 5, 2019 at 11:33 am

    #3695073

    1. a plan will stay in the procedural cache for a varying amount of time - memory pressure, data changes, new indexes etc may all push the plan out of cache
    2.  if a query is not running fine, then get an "actual" execution plan, have a look at what is going on - also try running sp_updatestats to see if the plan has changed/not changed because of data changes that put the statistics out of date
    3.  have a look at this article

      https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql?view=sql-server-ver15

      you can clear a single plan from the cache by getting a plan handle and calling dbcc freeprocache

    4. if you are going from 30 seconds to 45 minutes, i'd be looking at blocking and parameter sniffing rather than plans

    MVDBA

  • November 6, 2019 at 9:29 am

    #3695379

    Thank you MVDBA.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply