Top Queries in the SQL that is consuming most of the memory of Server

Hi SQL Server Guys,

It has been many times happens when performance of the SQL Server goes down, Then we are trying to find out out the cause of this. Frankly say there can be multiple issues for that which can cause for performance but one area is that where we can find out what are the queries that is currently running in the SQL Server and they only causing the problem.

Below are the queries that help you to find out memory contentation in SQL Server.

/* 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 indexesor queries that simply read a lot of data.*/

/**********************************************************

* 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_statsAS 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 ORDERBY 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_statsAS qs

CROSS

APPLYsys.dm_exec_sql_text(qs.sql_handle)AS qt

GROUP

BYDB_NAME(qt.dbid),OBJECT_SCHEMA_NAME(qt.objectid,dbid)+‘.’+OBJECT_NAME(qt.objectid, qt.dbid)

)

T ORDERBY 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_statsAS qs

CROSS

APPLYsys.dm_exec_sql_text(qs.sql_handle)AS qt

WHERE

OBJECT_SCHEMA_NAME(qt.objectid,dbid)+‘.’+OBJECT_NAME(qt.objectid, qt.dbid)ISNULL

GROUP

BYDB_NAME(qt.dbid), qt.text,OBJECT_SCHEMA_NAME(qt.objectid,dbid)+‘.’+OBJECT_NAME(qt.objectid, qt.dbid)

)

T ORDERBY 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_statsAS qs

CROSS

APPLYsys.dm_exec_sql_text(qs.sql_handle)AS qt

WHERE

OBJECT_SCHEMA_NAME(qt.objectid,dbid)+‘.’+OBJECT_NAME(qt.objectid, qt.dbid)ISNULL

GROUP

BYDB_NAME(qt.dbid), qt.text,OBJECT_SCHEMA_NAME(qt.objectid,dbid)+‘.’+OBJECT_NAME(qt.objectid, qt.dbid)

)

T ORDERBY IO_Per_Execution DESC

 

Hope it will be usefull.

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

%d bloggers like this: