when the data of sys.dm_exec_query_stats will be reset/removed

  • I notice that even if we don't restart SQL Service, the data of sys.dm_exec_query_stats( sys.dm_exec_sql_text() and sys.dm_exec_query_plan() ) will be reset or removed/refreshed. when the data of sys.dm_exec_query_stats will be reset or removed/refreshed ? thanks!

  • The DMVs are completely dependent on the cache. That's where the information comes from. So, anything that affects the cache, will affect the data in the DMVs. For example, DBCC FREEPROCCACHE will remove all the DMV info (unless you pass a plan handle, then it will only remove data for one plan). Then, it's just a question of standard cache behavior. Query doesn't get called for a long time, it ages out of cache as memory pressure forces a cleanup. You have enabled "Optimize for Ad Hoc" (a good idea in general), then the query doesn't go into cache until the second time it's called. You have a query with a RECOMPILE hint, then it never goes into cache. It's all about what's in cache and what gets removed from cache.

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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 892717952 wrote:

    I notice that even if we don't restart SQL Service, the data of sys.dm_exec_query_stats( sys.dm_exec_sql_text() and sys.dm_exec_query_plan() ) will be reset or removed/refreshed. when the data of sys.dm_exec_query_stats will be reset or removed/refreshed ? thanks!

    I'll suggest it again... You really should read the documentation for things BEFORE you use them.  Seriously.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Grant Fritchey wrote:

    The DMVs are completely dependent on the cache. That's where the information comes from. So, anything that affects the cache, will affect the data in the DMVs. For example, DBCC FREEPROCCACHE will remove all the DMV info (unless you pass a plan handle, then it will only remove data for one plan). Then, it's just a question of standard cache behavior. Query doesn't get called for a long time, it ages out of cache as memory pressure forces a cleanup. You have enabled "Optimize for Ad Hoc" (a good idea in general), then the query doesn't go into cache until the second time it's called. You have a query with a RECOMPILE hint, then it never goes into cache. It's all about what's in cache and what gets removed from cache.

    Thank you Grant Fritchey!

    1. how can we check the cache for DMVs and  how to set the cache for DMVs( or how to set the cache for different DMVs) ?
    2. aside from cache and DBCC FREEPROCCACHE, if there is any other factor to affect  the data in DMVs?  thanks!

     

    • This reply was modified 2 months, 2 weeks ago by  892717952.
  • Jeff Moden wrote:

    892717952 wrote:

    I notice that even if we don't restart SQL Service, the data of sys.dm_exec_query_stats( sys.dm_exec_sql_text() and sys.dm_exec_query_plan() ) will be reset or removed/refreshed. when the data of sys.dm_exec_query_stats will be reset or removed/refreshed ? thanks!

    I'll suggest it again... You really should read the documentation for things BEFORE you use them.  Seriously.

    Thank you!

    do you mean to google some documentation affecting the data in DMVs?

  • 892717952 wrote:

    Thank you Grant Fritchey!

    1. how can we check the cache for DMVs and  how to set the cache for DMVs( or how to set the cache for different DMVs) ?
    2. aside from cache and DBCC FREEPROCCACHE, if there is any other factor to affect  the data in DMVs?  thanks!

    You don't want to try to control the behavior of the cache. Let SQL Server handle that. Just know that you're dependent on the cache for the behavior of the DMVs. That's just how it works. There's nothing you do to turn it on, off, up or down. If you need more query information than is available through the DMVs, look to Query Store or Extended Events to gather that information.

    As to things other than cache, it depends on the DMV. There are a lot of them for different purposes. However, we started talking about the query DMVs. Those are cache dependent and cache only. Anything that affects the cache, affects them. So, as I said, aging out of cache, clearing the cache (FREEPROCCACHE, failovers, reboots, dropping a db, detaching a db, off the top of my head list), never in cache in the first place (optimize for ad hoc, recompile hints). I'm sure I'm missing one or two things, but that's about it. Memory pressure can affect how much cache is available and how long stuff remains in cache.

    I hope that helps.

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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 892717952 wrote:

    Jeff Moden wrote:

    892717952 wrote:

    I notice that even if we don't restart SQL Service, the data of sys.dm_exec_query_stats( sys.dm_exec_sql_text() and sys.dm_exec_query_plan() ) will be reset or removed/refreshed. when the data of sys.dm_exec_query_stats will be reset or removed/refreshed ? thanks!

    I'll suggest it again... You really should read the documentation for things BEFORE you use them.  Seriously.

    Thank you!

    do you mean to google some documentation affecting the data in DMVs?

    Start off by Googling the names of the things you intend to use.  The MS docs normally show up close to the top listing.  Read those first.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Grant Fritchey wrote:

    892717952 wrote:

    Thank you Grant Fritchey!

    1. how can we check the cache for DMVs and  how to set the cache for DMVs( or how to set the cache for different DMVs) ?
    2. aside from cache and DBCC FREEPROCCACHE, if there is any other factor to affect  the data in DMVs?  thanks!

    You don't want to try to control the behavior of the cache. Let SQL Server handle that. Just know that you're dependent on the cache for the behavior of the DMVs. That's just how it works. There's nothing you do to turn it on, off, up or down. If you need more query information than is available through the DMVs, look to Query Store or Extended Events to gather that information.

    As to things other than cache, it depends on the DMV. There are a lot of them for different purposes. However, we started talking about the query DMVs. Those are cache dependent and cache only. Anything that affects the cache, affects them. So, as I said, aging out of cache, clearing the cache (FREEPROCCACHE, failovers, reboots, dropping a db, detaching a db, off the top of my head list), never in cache in the first place (optimize for ad hoc, recompile hints). I'm sure I'm missing one or two things, but that's about it. Memory pressure can affect how much cache is available and how long stuff remains in cache.

    I hope that helps.

     

    thank you for your kind help! thanks so much!

  • Even Grant will tell you that's just scratching the surface on such things.  Before you use one of them, you really need to study the documentation.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden wrote:

    Even Grant will tell you that's just scratching the surface on such things.  Before you use one of them, you really need to study the documentation.

     

    well noted,Than you Jeff Moden!

  • Jeff Moden wrote:

    Even Grant will tell you that's just scratching the surface on such things.  Before you use one of them, you really need to study the documentation.

    Yes!

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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

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

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