SQL Server 2008 Query Memory Usage

You may see Hash or Sort warnings in the default trace output and this is typically caused by query memory pressure.

If you would like to know how much memory a query is using, then the DMV sys.dm_exec_query_memory_grants may be able to help you.

An example for using the DMV is shown below:

DECLARE @mgcounter INT
SET @mgcounter = 1
WHILE @mgcounter <= 5 -- return data from dmv 5 times when there is data
  BEGIN
     IF (SELECT COUNT(*)
          FROM sys.dm_exec_query_memory_grants) > 0
        BEGIN
                 SELECT *
                 FROM sys.dm_exec_query_memory_grants mg
                             CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) -- shows query text
                 -- WAITFOR DELAY '00:00:01' -- add a delay if you see the exact same query in results
                 SET @mgcounter = @mgcounter + 1
        END
  END