Find Procedures using Most Memory DMV

The following query displays the top 10 procedures that use the most memory and disk IO on your system. The query further breaks down the logical vs physical reads. Physical reads are the ones that actually touch the disk, logical reads are the ones that read from memory. The ones that read from disk more often will be the ones that are most likely executed less. Fixing the following queries will most likely alleviate your server’s resources quite a bit.

[cc lang=”sql”]
SELECT TOP 10
ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid)
,DiskReads = qs.total_physical_reads — The worst reads, disk reads
,MemoryReads = qs.total_logical_reads –Logical Reads are memory reads
,ReadsPerExecution = NULLIF(qs.total_physical_reads + qs.total_logical_reads,0) / qs.execution_count
,Executions = qs.execution_count
,CPUTime = qs.total_worker_time
,DiskWaitAndCPUTime = qs.total_elapsed_time
,MemoryWrites = qs.max_logical_writes
,DateCached = qs.creation_time
,DatabaseName = DB_Name(qt.dbid)
,LastExecutionTime = qs.last_execution_time

FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_physical_reads + qs.total_logical_reads DESC
[/cc]