Post reply

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

  • June 28, 2021 at 3:41 pm

    #3901263

    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!

  • June 29, 2021 at 10:49 am

    #3901572

    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

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

  • June 29, 2021 at 5:36 pm

    #3901759

    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)

  • July 8, 2021 at 4:24 am

    #3905583

    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 years, 9 months ago by  892717952.

  • July 8, 2021 at 4:26 am

    #3905585

    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?

  • July 8, 2021 at 12:33 pm

    #3905720

    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

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

  • July 8, 2021 at 2:37 pm

    #3905754

    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)

  • July 8, 2021 at 2:52 pm

    #3905760

    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!

  • July 8, 2021 at 9:51 pm

    #3905898

    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)

  • July 9, 2021 at 12:23 am

    #3905930

    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!

  • July 9, 2021 at 10:06 am

    #3906020

    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

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

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

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