Memory Usage Per Query

USE [msdb]


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


SET ANSI_PADDING ON

GO


CREATE TABLE [dbo].[T_MemoryUsage](

[session_id] [smallint] NULL,

[request_id] [int] NULL,

[scheduler_id] [int] NULL,

[dop] [smallint] NULL,

[request_time] [datetime] NULL,

[grant_time] [datetime] NULL,

[requested_memory_kb] [bigint] NULL,

[granted_memory_kb] [bigint] NULL,

[required_memory_kb] [bigint] NULL,

[used_memory_kb] [bigint] NULL,

[max_used_memory_kb] [bigint] NULL,

[query_cost] [float] NULL,

[timeout_sec] [int] NULL,

[resource_semaphore_id] [smallint] NULL,

[queue_id] [smallint] NULL,

[wait_order] [int] NULL,

[is_next_candidate] [bit] NULL,

[wait_time_ms] [bigint] NULL,

[plan_handle] [varbinary](64) NULL,

[sql_handle] [varbinary](64) NULL,

[group_id] [int] NULL,

[pool_id] [int] NULL,

[is_small] [bit] NULL,

[ideal_memory_kb] [bigint] NULL,

[dbid] [smallint] NULL,

[objectid] [int] NULL,

[number] [smallint] NULL,

[encrypted] [bit] NOT NULL,

[text] [nvarchar](max) NULL

) ON [PRIMARY]



GO



SET ANSI_PADDING OFF

GO


USE [msdb]

GO

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



CREATE PROCEDURE [dbo].[P_MemoryUsage]

as

BEGIN

SET NOCOUNT ON;



DECLARE @mgcounter INT

SET @mgcounter = 1

WHILE @mgcounter <= 1 -- return data from DMV five times when there is data

BEGIN

IF (SELECT COUNT(*)

FROM sys.dm_exec_query_memory_grants) > 0

BEGIN



INSERT INTO T_MemoryUsage

SELECT *

FROM sys.dm_exec_query_memory_grants mg

CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) -- shows query text

WHERE used_memory_kb >0

-- WAITFOR DELAY '00:00:01' -- add a delay if you see the exact same query in results

SET @mgcounter = @mgcounter + 1

END

END

SET NOCOUNT OFF;

END

GO



Exec [dbo].[P_MemoryUsage]

go

SELECT TOP 1000 [session_id]

,[request_id]

,[scheduler_id]

,[dop]

,[request_time]

,[grant_time]

,[requested_memory_kb]

,[granted_memory_kb]

,[required_memory_kb]

,[used_memory_kb]

,[max_used_memory_kb]

,[query_cost]

,[timeout_sec]

,[resource_semaphore_id]

,[queue_id]

,[wait_order]

,[is_next_candidate]

,[wait_time_ms]

,[plan_handle]

,[sql_handle]

,[group_id]

,[pool_id]

,[is_small]

,[ideal_memory_kb]

,[dbid]

,[objectid]

,[number]

,[encrypted]

,[text]

FROM [msdb].[dbo].[T_MemoryUsage] order by used_memory_kb desc --,grant_time desc

 ----------------------

select
text, query_plan, requested_memory_kb, granted_memory_kb, used_memory_kb from sys.dm_exec_query_memory_grants MG
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
CROSS APPLY sys.dm_exec_query_plan(MG.plan_handle)