Common reasons for query time-outs are :

1)     The application starts using a query not optimal for the index

2)     Hardware changes\ Configuration changes

3)     Increased load

If you suspect the query time-out is due to memory issue , continue reading.

Debugging a query-timeout is tricky. If it’s a Production system and users are experiencing timeouts – pressure mounts on the DBA. The application owner starts looking towards the DBA , for reasons and a solution. (and to blame)

Isolate the problem by using a systematic approach . There are multiple approaches to any problem –

A system for debugging a query-timeout.  Each problem has it’s own characteristics – using this system , should give you enough ideas. Contact me: Jack Vamvas(sqlserver-dba.com) for any help

Step 1 – system memory status

Use sys.dm_os_memory_clerks, sys.dm_os_sys_info, and memory performance counters.

-- sys.dm_os_memory_clerks
select  memory_clerk_address,type,single_pages_kb,awe_allocated_kb
from  sys.dm_os_memory_clerks
--sys.dm_os_sys_info
select physical_memory_in_bytes,virtual_memory_in_bytes,bpool_commited,bpool_commited,bpool_commit_target from sys.dm_os_sys_info
--memory performance counters
"\Memory\Available MBytes" 
"\Memory\Page Faults/sec" 
"\Memory\Pages/sec" 
"\Memory\Paging File(_Total)\%Usage" 

 Step 2 – query execution memory reservations

select * from sys.dm_os_memory_clerks where
type = 'MEMORYCLERK_SQLQERESERVATIONS'

Step 3 – identify queries waiting memory grants

SELECT * from . sys.dm_exec_query_memory_grants

Step 4 – identify more memory-intensive queries

select session_id, command,  
status, sql_handle
from sys.dm_exec_requests 

Step 5 – Analyse and fix Query from cache. Work with developers to overcome any bottlenecks

SQL Server query plans in cache

See Also

Sys.dm_os_memory_clerks and AWE memory allocation

Performance Monitor Counters for SQL Server performance testing

SQL WAIT TYPE – RESOURCE SEMAPHORE and how to reduce it