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?

Answer 1

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 [e-mail].

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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