View queries waiting for memory grant

On 21 Oct, 2015

MS SQL DBA

By : Charith Silva

No Comments

One of the smartest ways to identify memory consumption issues in SQL Server is checking details of the queries waiting for memory grant. Below script will show all the queries that have acquired a memory grant or that still require a memory grant to execute. Queries that do not have to wait on a memory grant will not appear in the result. Also below script result will help you to identify queries that are requesting relatively large memory grants, perhaps because they are poorly written or they’re missing indexes that make the query more expensive.

SELECT *
FROM sys.dm_exec_query_memory_grants q
INNER JOIN sys.dm_exec_sessions s ON s.session_id=q.session_id
WHERE CAST(q.session_id AS INT)<>@@SPID

SELECT *

FROM sys.dm_exec_query_memory_grants q

INNER JOIN sys.dm_exec_sessions s ON s.session_id=q.session_id

WHERE CAST(q.session_id AS INT)<>@@SPID

Previous Post Next Post

About The Author

Charith Silva

Charith Silva is a Microsoft certified SQL Server developer and database administrator who was graduated at Buckinghamshire New university in the UK. His career was started in 1998, primarily into Web application development, and later diversified into database development. He has got a vast experience in SQL Server database development, Database administration and Business Intelligence development. He believes that sharing the knowledge is key to the success.

Number of Posts : 87

All Posts by : Charith Silva