April 27, 2013 at 7:03 am
Hi..
Top procedures memory consumption per execution as below capture script
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
as below outputs, how can understand these numbers? which is normal or critical? Logical Reads means, data read from memory cache is too high number. ( how can reduce? By using proper indexing)
output
------
Memory Reads: 104131
Total IO Reads: 104155 (
Executions: 1505
IO_Per_execution: 557
CPU Time: 215258
DiskWaitAndCPUTime: 215272
Memory writes: 2
Thanks
ananda
April 28, 2013 at 2:30 pm
You'll need to cross-reference this with the actual query execution plan to see if it is reasonable or badly optimised.
This modified query will add the execution plans to the output:
SELECT TOP 100 T.*, qp.query_plan
FROM
(
SELECT
DatabaseName = DB_NAME(qt.dbid)
,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,qt.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)
,QueryText = qt.Text
,PlanHandle = qs.plan_handle
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,qt.dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid), qt.Text, qs.plan_handle
) T
CROSS APPLY sys.dm_exec_query_plan(T.PlanHandle) as qp
ORDER BY IO_Per_Execution DESC
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply