output understanding for most memory reads?

  • 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

  • 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