Home Forums SQL Server 7,2000 T-SQL How to Reduce the Logical Reads, to imporve the Performance of the Query RE: How to Reduce the Logical Reads, to imporve the Performance of the Query

  • From BOL

    Logical reads - number of pages read from the data cache

    Physical reads - number of pages read from disk

    To reduce reads you need to look at a couple of things, first being query design, secondly being indexing. If your query is pulling a large number of records that could be filtered by getting a smaller set prior to pulling that data then you can always cut down on reads that way. With improved indexing, specifically with covering indexes, you can reduce the number of pages that are being read as well.

    All of this is just a basic guideline but should be applied to the analysis of all the queries that are running in your production environment. A query like the following (not mine but got this from somewhere else so, no credit here) should help in getting those queries.

    SELECT TOP 20

    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, ((

    CASE qs.statement_end_offset

    WHEN -1 THEN DATALENGTH(qt.text)

    ELSE qs.statement_end_offset

    END - qs.statement_start_offset)/2)+1) AS SQLText

    , qs.execution_count

    , qs.total_logical_reads

    , qs.last_logical_reads

    , qs.min_logical_reads

    , qs.max_logical_reads

    , qs.total_elapsed_time

    , qs.last_elapsed_time

    , qs.min_elapsed_time

    , qs.max_elapsed_time

    , qs.last_execution_time

    , qp.query_plan

    FROM

    sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

    WHERE

    qt.encrypted=0

    ORDER BY

    qs.last_logical_reads DESC

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot