SQL: Troubleshooting_Memory_Pressure

References: : http://www.mssqltips.com/sqlservertip/2827/troubleshooting-sql-server-resourcesemaphore-waittype-memory-issues/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=201313

select * from sys.dm_exec_requests –Check the wait type, if the wait type has RESOURCE_SEMAPHORE then it is due to memory pressure.

SELECT * FROM sys.dm_exec_query_resource_semaphores –waiter_count is the number of queries waiting in queue for memory to get allocated

SELECT * FROM sys.dm_exec_query_memory_grants –grant time and granted_memory_kb is null means queries waiting for memory to get allocated. Take the note of the plan_hanlde and sql_handle.

SELECT * FROM sys.dm_exec_sql_text(0x030005008A543939E047AF0040A100000100000000000000) –pass the sql handle to get the name of the query waiting for memory to be allocated.

SELECT * FROM sys.dm_exec_query_plan(0x050005008A54393940419C24020000000000000000000000) –pass the plan_handle to get the execution plan for the above query waiting for memory to be allocated.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s