Find Queries Using Most Memory (IO)
-
Posted on June 17, 2009 by Derek Dieter
-
1
This query returns back the queries that use the most IO. This can mean that either the query is reading from disk more than usual or occupying and utilizing a large amount of buffer cache. These are typical symptoms of queries that do not have the proper indexes or queries that simply read a lot of data.
[cc lang=”sql”]
/**********************************************************
* 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
[/cc]
- Comments (RSS)
- Trackback
- Permalink
Thank you for such wonderful scripts. *kudos*
High Memory usage of SQLSERVR.exe
Server Configuration: Windows 2008 R2 server 64-bit; RAM – 4 Gigs; CPU 2.80 Ghz (2 processors); SQL Server 2008 32-bit has been installed.
From the Task Manager, the Memory usage for the Sqlservr.exe is always showing as 3,390,168, i.e. almost 3.5 Gigs are being consumed by the sqlservr.exe.
Is this a Normal? What are all the things to be checked?
Hi Anandan,
Yes it is normal if you have the memory for SQL set that high. To change it open SQL Server Management Studio and connect to the server. Right click on the server name listed in the object explorer in the upper left hand corner -> properties -> memory -> (and set it here)
Good luck
