SQL Server Performance

which query's exec. costs are better/more efficient

Discussion in 'SQL Server 2005 General DBA Questions' started by fabi, Feb 28, 2010.

  1. fabi New Member

    Hi,

    Iam examining query's executions costs (SELECT queries) using differentindexes. it is known that we should have the smallest number ofphysical and logical reads. But how important is relation betweenphysical and logical reads?? I have few queries costs that makes meconfused .

    Query 1 using different kind of indexes i've got:

    A)



    physical reads 251

    logical reads 5848

    SQL Server Execution Times:

    CPU time = 1547 ms, elapsed time = 6427 ms.



    B)

    physical reads 273

    logical reads 3695

    SQL Server Execution Times:

    CPU time = 1594 ms, elapsed time = 6873 ms.



    elapsed times very similar but where i have less physical reads i have more logical reads.



    Query 2

    A)

    physical reads 8

    logical reads 1133

    SQL Server Execution Times:

    CPU time = 265 ms, elapsed time = 1179 ms.



    B)

    physical reads 46

    logical reads 576


    SQL Server Execution Times:

    CPU time = 313 ms, elapsed time = 1472 ms.



    elapsed times very similar but where i have less physical reads i have more logical reads.


    Query 3


    A)

    physical reads 806

    logical reads 26265

    SQL Server Execution Times:

    CPU time = 531 ms, elapsed time = 7207 ms.




    B)

    physical reads 574

    logical reads 267375

    SQL Server Execution Times:

    CPU time = 469 ms, elapsed time = 2188 ms


    Herewe have an interesting situation, in B i have less physical reads butlogical reads increases 10 times. Although logical reads increases 10times elapsed time decreases 3times !!

    Could you help me in making decision which costs are better/ more efficient??
  2. satya Moderator

    How good are indexes on these tables?
    I believe you have asked another Q here about covering indexes, so does that condition fall one these options?Going back to basics on logical & physical reads:

    Logical read indicates total number of data pages needed to be accessed from data cache to process query. It is very possible that logical read will access same data pages many times, so count of logical read value may be higher than actual number of pages in a table. Usually the best way to reduce logical read is to apply correct index or to rewrite the query.
    Physical read indicates total number of data pages that are read from disk. In case no data in data cache, the physical read will be equal to number of logical read. And usually it happens for first query request. And for subsequent same query request the number will be substantially decreased because the data pages have been in data cache.
    Having both in hand you should look at Buffer Hit ratio using SYSMON, if that ratio is between 95 to 100 then your SQL Server is performing very well with no issues, so it is better you asses the information
    from physical read and buffer hit ratio to measure performance in server level and logical read to measure individual query level.

    BTW how frequently this query is executed on that database?

Share This Page