Post reply

how to identify why my proc remove from the cache

  • February 4, 2022 at 10:15 am

    #3980636

    Hi,

    I've sql server 2016 with SP2 and i have a problem that every few hours my proc get out of the cache.

    I've tried to use the option(keepfixed plan) inside the proc and it's still get out of cache.

    my question is - how i can find exactly why the proc get out from the cache?

    THX

  • February 4, 2022 at 1:07 pm

    #3980701

    You want to look at Extended Events. sp_cache_remove is the key to understanding what's going on. Here's an example session for detailing cache behaviors:

    CREATE EVENT SESSION [PlanCacheBehavior]
    ON SERVER
        ADD EVENT sqlserver.rpc_completed,
        ADD EVENT sqlserver.rpc_starting,
        ADD EVENT sqlserver.sp_cache_hit,
        ADD EVENT sqlserver.sp_cache_insert,
        ADD EVENT sqlserver.sp_cache_miss,
        ADD EVENT sqlserver.sp_cache_remove
    WITH
    (
        TRACK_CAUSALITY = ON
    );

    I have this one specifically set up for remote procedure calls, but you could add batches to it, or replace the rpc with batch start & completed.

    "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 2 posts - 1 through 1 (of 1 total)

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