All geek questions in one place

How to find queries that used most of the memory to sort

How to determine which queries use most of the memory to sort / combine / etc? How to determine how much memory one particular request uses?

I was hoping to find something like SET STATISTICS IO ON for an individual query and DMV to find out the worst offenders, but I could not find them.

+4
source share
2 answers

sys.dm_exec_query_memory_grants :

  • requested_memory_kb : total requested memory in kilobytes
  • granted_memory_kb : total amount of memory actually provided in kilobytes.
+5
source

Try to find an individual request with a high intruder and their implementation plans

 -- Find single-use, ad-hoc queries that are bloating the plan cache SELECT TOP(100) [text], cp.size_in_bytes FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE cp.cacheobjtype = N'Compiled Plan' AND cp.objtype = N'Adhoc' AND cp.usecounts = 1 ORDER BY cp.size_in_bytes DESC;

Alternatively, go to Black's blog in Bern and get the full diagnostic script (where I am above). It has really great DMV-based scripts to find high IO / Mem / CPU requests (AdHoc and SP).

Luck

+1
source

Source: https://habr.com/ru/post/1391538/

More articles:


All Articles