Search

Monday, March 23, 2015

Queries waiting for memory

You can use below query to find out the queries that require a memory grant to execute or have acquired a memory grant. 
The queries that do not have to wait on a memory grant will not appear in the result. 
If this query returns a lot of rows than it could be a indication of internal memory pressure. This will help you to identify the queries which are requsting larger memory grants. There are various reason for this. The quiry might be poorly written. That may require some index for optimization. You should run this query periodically and check for the resource hungry queries.  The user who runs below query must have View SErver STate permission on the server.

SELECT DB_NAME(ST.DBID) AS [Database Name], MG.Requested_Memory_KB AS [Requested memoty in KB], MG.Ideal_Memory_KB AS [Ideal Memory in KB], MG.Request_Time AS [Request Time], MG.Grant_Time AS [Grant Time], MG.Query_Cost AS [Query Cost], MG.DOP, ST.[TEXT], QP.Query_Plan AS [Query Plan]
FROM SYS.DM_EXEC_QUERY_MEMORY_GRANTS AS MG CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE) AS ST CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(MG.PLAN_HANDLE) AS QP 
ORDER BY MG.REQUESTED_MEMORY_KB DESC

No comments:

Post a Comment