none
2012 equivalent of sys.dm_exec_query_stats total_grant_kb?

    Question

  • Is there a mean to identify queries which consumed workspace memory in sql server 2012?

    A correspondance to the column total_grant_kb introduced to the sys.dm_exec_query_stats view in 2016?

    or a view containing the information of dm_exec_query_memory_grants for completed queries still in cache?

    Thursday, February 08, 2018 11:14 PM

Answers

  • Hi Antoine,

    >> Is there a mean to identify queries which consumed workspace memory in sql server 2012?

    What is your SQL Server version/service pack?

    Starting with SQL Server 2016 CTP 2.0, and back-ported to SQL Server 2012 SP3, it is possible to identify memory-grant allocations and parallelism operations in the plan cache with the DMV sys.dm_exec_query_stats. So apply SP3 to your SQL Server 2012 would meet your requirement.

    If you have any other questions, please let me know.

    Regards,

    Hannah


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 09, 2018 2:01 AM
  • Thank you,

    Hannah answers is perfect, I was looking at different alternatives to find why all of a sudden I see 45GB consumed in my workspace.

    There is a section memory_grant_info containted in the query_plan column which can be obtained from sys.dm_exec_query_plan.

    I also collected the granted_memory_kb from the sys.dm_exec_requests on a regular basis.

    Marking as answered.  2012 sp3 would have that information

    • Marked as answer by Antoine F Monday, February 19, 2018 4:36 PM
    • Edited by Antoine F Monday, February 19, 2018 4:37 PM
    Monday, February 19, 2018 4:36 PM

All replies

  • Hi Antoine,

    >> Is there a mean to identify queries which consumed workspace memory in sql server 2012?

    What is your SQL Server version/service pack?

    Starting with SQL Server 2016 CTP 2.0, and back-ported to SQL Server 2012 SP3, it is possible to identify memory-grant allocations and parallelism operations in the plan cache with the DMV sys.dm_exec_query_stats. So apply SP3 to your SQL Server 2012 would meet your requirement.

    If you have any other questions, please let me know.

    Regards,

    Hannah


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 09, 2018 2:01 AM
  • Is there a mean to identify queries which consumed workspace memory in sql server 2012?

    A correspondance to the column total_grant_kb introduced to the sys.dm_exec_query_stats view in 2016?

    or a view containing the information of dm_exec_query_memory_grants for completed queries still in cache?

    You can get that information from perfmon counter and DBCC memorystatus. I would suggest you to read This Blog and I would take query from there

    select(selectcntr_value
    
    from sys . dm_os_performance_counters   
    
    whereobject_namelike'%Memory Manager%'andcounter_namelike'Maximum
    
    Workspace Memory (KB)%')as'Maximum Workspace Memory (KB)',
    
      (selectcntr_value
    
    from sys . dm_os_performance_counters
    
       whereobject_namelike'%Memory Manager%'andcounter_namelike'Target Server Memory (KB)%')as'Target Server Memory (KB)',
    
        ( select cntr_value from sys . dm_os_performance_counters
    
       whereobject_namelike'%Memory Manager%'andcounter_namelike'Maximum Workspace Memory (KB)%')*100.0/
    
        ( select cntr_value from sys . dm_os_performance_counters
    
       whereobject_namelike'%Memory Manager%'andcounter_namelike'Target Server Memory (KB)%')asRatio
    
    go

    Basically workspace memory is same as query memory. The workspace memory is memory used for sorting and hash operations while query is running.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Proposed as answer by Visakh16MVP Friday, February 09, 2018 8:06 AM
    • Unproposed as answer by Antoine F Friday, February 09, 2018 4:02 PM
    Friday, February 09, 2018 7:58 AM
    Moderator
  • thanks both of you,

    @André, thank you (merci), I don't have sp3 installed.  One more reason to update.

    @shanky, thank you, I am actually monitoring this information.  I am at the point dive in and figure out the exact query which makes it flucutate.

    Marking both your answer as helpfull, I will wait a bit before marking it as answer hoping to receive additionnal information outside of those views or counters.

    Friday, February 09, 2018 3:48 PM

  • Marking both your answer as helpfull, I will wait a bit before marking it as answer hoping to receive additionnal information outside of those views or counters.

    Mark it as answer "only" if it has really helped you in solving your problem or else leave it. What additional information do you need please let me know ?

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, February 12, 2018 5:17 AM
    Moderator
  • Thank you,

    Hannah answers is perfect, I was looking at different alternatives to find why all of a sudden I see 45GB consumed in my workspace.

    There is a section memory_grant_info containted in the query_plan column which can be obtained from sys.dm_exec_query_plan.

    I also collected the granted_memory_kb from the sys.dm_exec_requests on a regular basis.

    Marking as answered.  2012 sp3 would have that information

    • Marked as answer by Antoine F Monday, February 19, 2018 4:36 PM
    • Edited by Antoine F Monday, February 19, 2018 4:37 PM
    Monday, February 19, 2018 4:36 PM