http://sqlserverplanet.com/dmv-queries/find-queries-taking-most-memory
/********************************************************** |
* top procedures memory consumption per execution |
* (this will show mostly reports & jobs) |
***********************************************************/ |
SELECT TOP 100 * |
FROM |
( |
SELECT |
DatabaseName = DB_NAME(qt.dbid) |
,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) |
,DiskReads = SUM(qs.total_physical_reads) -- The worst reads, disk reads |
,MemoryReads = SUM(qs.total_logical_reads) --Logical Reads are memory reads |
,Executions = SUM(qs.execution_count) |
,IO_Per_Execution = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count) |
,CPUTime = SUM(qs.total_worker_time) |
,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time) |
,MemoryWrites = SUM(qs.max_logical_writes) |
,DateLastExecuted = MAX(qs.last_execution_time) |
|
FROM sys.dm_exec_query_stats AS qs |
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt |
GROUP BY DB_NAME(qt.dbid), OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) |
|
) T |
ORDER BY IO_Per_Execution DESC |
|
/********************************************************** |
* top procedures memory consumption total |
* (this will show more operational procedures) |
***********************************************************/ |
SELECT TOP 100 * |
FROM |
( |
SELECT |
DatabaseName = DB_NAME(qt.dbid) |
,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) |
,DiskReads = SUM(qs.total_physical_reads) -- The worst reads, disk reads |
,MemoryReads = SUM(qs.total_logical_reads) --Logical Reads are memory reads |
,Total_IO_Reads = SUM(qs.total_physical_reads + qs.total_logical_reads) |
,Executions = SUM(qs.execution_count) |
,IO_Per_Execution = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count) |
,CPUTime = SUM(qs.total_worker_time) |
,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time) |
,MemoryWrites = SUM(qs.max_logical_writes) |
,DateLastExecuted = MAX(qs.last_execution_time) |
|
FROM sys.dm_exec_query_stats AS qs |
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt |
GROUP BY DB_NAME(qt.dbid), OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) |
) T |
ORDER BY Total_IO_Reads DESC |
|
/********************************************************** |
* top adhoc queries memory consumption total |
***********************************************************/ |
SELECT TOP 100 * |
FROM |
( |
SELECT |
DatabaseName = DB_NAME(qt.dbid) |
,QueryText = qt.text |
,DiskReads = SUM(qs.total_physical_reads) -- The worst reads, disk reads |
,MemoryReads = SUM(qs.total_logical_reads) --Logical Reads are memory reads |
,Total_IO_Reads = SUM(qs.total_physical_reads + qs.total_logical_reads) |
,Executions = SUM(qs.execution_count) |
,IO_Per_Execution = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count) |
,CPUTime = SUM(qs.total_worker_time) |
,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time) |
,MemoryWrites = SUM(qs.max_logical_writes) |
,DateLastExecuted = MAX(qs.last_execution_time) |
|
FROM sys.dm_exec_query_stats AS qs |
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt |
WHERE OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) IS NULL |
GROUP BY DB_NAME(qt.dbid), qt.text, OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) |
) T |
ORDER BY Total_IO_Reads DESC |
|
/********************************************************** |
* top adhoc queries memory consumption per execution |
***********************************************************/ |
SELECT TOP 100 * |
FROM |
( |
SELECT |
DatabaseName = DB_NAME(qt.dbid) |
,QueryText = qt.text |
,DiskReads = SUM(qs.total_physical_reads) -- The worst reads, disk reads |
,MemoryReads = SUM(qs.total_logical_reads) --Logical Reads are memory reads |
,Total_IO_Reads = SUM(qs.total_physical_reads + qs.total_logical_reads) |
,Executions = SUM(qs.execution_count) |
,IO_Per_Execution = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count) |
,CPUTime = SUM(qs.total_worker_time) |
,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time) |
,MemoryWrites = SUM(qs.max_logical_writes) |
,DateLastExecuted = MAX(qs.last_execution_time) |
|
FROM sys.dm_exec_query_stats AS qs |
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt |
WHERE OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) IS NULL |
GROUP BY DB_NAME(qt.dbid), qt.text, OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) |
) T |
ORDER BY IO_Per_Execution DESC |
Comments
Post a Comment