Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

output understanding for most memory reads? Expand / Collapse
Author
Message
Posted Saturday, April 27, 2013 7:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:00 AM
Points: 1,054, Visits: 2,974
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
Post #1447264
Posted Sunday, April 28, 2013 2:30 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:20 PM
Points: 364, Visits: 384
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

Post #1447343
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse