Post reply

Procedure plan changed suddenly

  • July 5, 2017 at 10:22 am

    #322462

    Hello,

    Is there anyway to find how suddenly a procedure plan is changed. I understand that there can be multiple reasons on it but if there is any specific steps been followed to assess this would be useful. I understood this is not because of parameter values, so discarded that option.
    Used this std dmv joins to get some meaningful values but unable to come to the root cause that is know how the execution plan is changed suddenly.

    SELECT usecounts, cacheobjtype, objtype, bucketid, text,*
    FROM sys.dm_exec_cached_plans
        CROSS APPLY sys.dm_exec_sql_text(plan_handle)
    WHERE cacheobjtype = 'Compiled Plan'
    and text like '%sp name%'
    ORDER BY objtype;

    Thanks.

  • July 5, 2017 at 10:33 am

    #1949528

    You can get the statistics updated dates on all objects.


    SELECT name AS index_name, STATS_DATE(object_id, index_id) AS statistics_update_date
    FROM sys.indexes

  • July 5, 2017 at 10:41 am

    #1949530

    Why it was thrown out of cache, or why the new plan is different?

    The first you can get via extended events, there are cache remove, cache insert and recompile events.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 5, 2017 at 10:43 am

    #1949531

    Bill Talada - Wednesday, July 5, 2017 10:33 AM

    You can get the statistics updated dates on all objects.


    SELECT name AS index_name, STATS_DATE(object_id, index_id) AS statistics_update_date
    FROM sys.indexes

    Use sys.stats rather. It gets you both the index and column statistics. sys.indexes excludes the latter.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 5, 2017 at 10:44 am

    #1949533

    All indexes stats update says that it was done today. But few days observed high cpu activity while running on a specific proc. Wanted to check how the plan is changed etc.

    Thanks.

  • July 5, 2017 at 10:45 am

    #1949534

    Does your monitoring tools track plans? If it doesn't then the old plan will be gone, nothing in SQL 2014 tracks it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 5, 2017 at 3:19 pm

    #1949576

    I compared the old good and new plan in sql sentry and found that there are missing index recommendation in the new plan. Creating that the index performance is improved.

    Thanks.

  • July 5, 2017 at 6:26 pm

    #1949597

    SQL-DBA-01 - Wednesday, July 5, 2017 3:19 PM

    I compared the old good and new plan in sql sentry and found that there are missing index recommendation in the new plan. Creating that the index performance is improved.

    I would have looked at the row estimates and actuals in the old and new plans.  Also, has the procedure has changed?

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • July 5, 2017 at 6:31 pm

    #1949598

    No change in the procedure script. Wanted to check how a new Execution Plan is created.

    Thanks.

  • July 6, 2017 at 7:33 am

    #1949710

    If you're not specifically monitoring for recompiles, there's no for sure way to know why it happened. Now, you can understand why the plan changed. It generally comes from three sources, assuming no changes to the query itself (and yeah, there are other causes, but these at the most common).

    Changes to the data and/or statistics.
    Changes to the parameters used to compile the plan.
    Changes to the ANSI settings from the calling process.

    If you have both plans, before and after, you can determine these causes. First, look at the first operator, the INSERT/UPDATE/DELETE/SELECT one. In the properties there you'll find the ANSI settings used to compile the plan. Are they different or the same? You'll also find the parameters used to compile the plan (if any). Are they different or the same. Finally, you can see the estimated number of rows for the various operations and the overall plan. Are these the same or different. Spot the differences and you've identified why the plan changed.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 10 posts - 1 through 9 (of 9 total)

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