0

I want to capture SQL Server queries with a memory grant over 10 GB so those queries and the tables they touch can be tuned to reduce the memory grants. I created an extended events session and a test query that should have been captured in the extended events file. However, the xEvents session isn't capturing anything.

As a troubleshooting step, I lowered the memory grant value for a query to 10 KB so I knew the xEvent session would be sure to capture activity in the file. However, that still didn't solve my issue.

    CREATE EVENT SESSION [CaptureLargeMemoryGrants] ON SERVER

ADD EVENT sqlserver.query_memory_grants
(ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.sql_text,sqlserver.username)

    WHERE ([granted_memory_kb]>(10)

    )

   )

ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\Log\CaptureLargeMemoryGrants.xel')

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

GO

I wrote this test query:

    --test query that has a sufficient memory grant to be captured:
USE StackOverflow2013;

GO

SELECT TOP (6000000) U.DisplayName
    ,U.Location
    ,U.Reputation
    ,P.title
    ,p.CreationDate
    ,p.ClosedDate
    ,p.ViewCount
    ,PT.Type
FROM dbo.users AS U
LEFT JOIN dbo.Posts AS P ON P.OwnerUserId = U.Id
INNER JOIN dbo.PostTypes AS PT ON P.PostTypeId = PT.Id
OPTION (RECOMPILE);

I can tell from both of these queries below that my test query has a sufficient memory grant that it should be captured. I'm not sure what I'm missing. I assume I must have the wrong event in my xEvents session, but I'm not sure what else I should be using.

SELECT query_stats.query_hash
    ,query_stats.query_plan_hash
    ,SUBSTRING(sql_text.TEXT, 1, 1000) AS QueryText
    ,-- Limit text length for readability
    query_stats.max_grant_kb
    ,query_stats.max_used_grant_kb
FROM sys.dm_exec_query_stats AS query_stats
CROSS APPLY sys.dm_exec_sql_text(query_stats.sql_handle) AS sql_text
ORDER BY query_stats.max_grant_kb DESC;

SELECT r.session_id
    ,r.granted_query_memory
    ,r.STATUS
    ,r.command
    ,t.TEXT
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.granted_query_memory > 0;
Improve this question
0

2 Answers 2

Reset to default
6

nope

I'm not sure why you've chosen this Extended Event. If you look at the description in the GUI, it says:

NUTS

What occurs? Who knows. Really, I don't.

searching

To find all Events that allow filtering on granted_memory_kb, you can do this:

SELECT
    package_name = p.name,
    event_name = o.name,
    event_desc = o.description,
    column_name = oc.name,
    column_type = oc.column_type,
    type_name = oc.type_name,
    is_predicate_source =
        CASE 
            WHEN oc.capabilities_desc LIKE '%predicate_source%' 
            THEN 1 
            ELSE 0 
        END
FROM sys.dm_xe_objects AS o
JOIN sys.dm_xe_object_columns AS oc
  ON  oc.object_name = o.name
  AND oc.object_package_guid = o.package_guid
JOIN sys.dm_xe_packages AS p
  ON p.guid = o.package_guid
WHERE o.object_type = 'event'
AND   oc.name = N'granted_memory_kb'
ORDER BY
    p.name,
    o.name,
    oc.name;

But what comes back doesn't generally look helpful for the most part. There are some exceptions:

  • query_plan_profile
  • query_post_execution_plan_profile
  • query_post_execution_showplan
  • query_pre_execution_showplan

But of course all of them involve collecting some form of execution plan, which may be more observer overhead than you're allowed. The Events with "profile" in the name use the lightweight profiling mechanism, which may be helpful to reduce overhead.

not it

I'm not sure which would most closely meet your needs, or what exactly you're looking to investigate. It's up to you to figure this part out.

Improve this answer
1
  • Thanks for your guidance on this, Erik. I experimented some more and got what I needed. Commented Oct 9 at 3:04
2

After quite a bit of experimenting, here is the xEvents script that captures what I am looking for. From the file I can get the sql text, the memory granted in KB, and a few other things.

CREATE EVENT SESSION [TrackHighMemoryGrants] ON SERVER 
ADD EVENT sqlserver.query_memory_grant_usage(
  ACTION(
    sqlserver.database_id,
    sqlserver.plan_handle,
    sqlserver.session_id,
    sqlserver.sql_text
  )
  WHERE (
    [granted_memory_kb] > (1024000)
  )
),
ADD EVENT sqlserver.query_memory_grant_wait_end(
  ACTION(
    sqlserver.database_id,
    sqlserver.session_id,
    sqlserver.sql_text
  )
  WHERE (
    [sqlserver].[database_id] = (8)
    AND [granted_memory_kb] > (1024000)
  )
)
ADD TARGET package0.event_file(
  SET filename = N'C:\XE\HighMemoryGrants.xel',
      max_file_size = (1024),
      max_rollover_files = (5)
)
WITH (
  MAX_MEMORY = 51200 KB,
  EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY = 5 SECONDS,
  MAX_EVENT_SIZE = 0 KB,
  MEMORY_PARTITION_MODE = NONE,
  TRACK_CAUSALITY = ON,
  STARTUP_STATE = OFF
);
GO
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.