SQL Server Performance

reads vs. duration

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by jdubs78, May 17, 2007.

  1. jdubs78 New Member

    i am trying to optimize my database structure. through the use of indexes on joined columns, i can cut the number of reads in half for a given sp but the duration stays the same. why is that?
  2. satya Moderator

  3. jdubs78 New Member

    well there is no code really. it was more of a general question. basically i have a star structure. the parent and child tables have primary keys with clustered indexes. when i create a non clustered index on the joining column on the child table, my reads are roughly half of what they are w/o the non clustered index. however the duration is about the same. i run the dbcc freeproccache command before i run the query.
  4. joechang New Member

    don't get hung up on reads
    there is not a linear correlation between reads and cpu cost

    cpu is more useful, but often has to be averaged over many calls
    also
    the execution plan is key
    always examine the executions

    i do not know why so many people jump to logical reads
    when it has no real value
  5. jdubs78 New Member

    i can't speak for everyone else, but the reason i was looking at reads is because the hard drive is the bottle neck. i thought logically the more reads there are, the more the hard drive is doing, thus the longer the query takes. mainly i was looking at duration. that is my primary goal. i don't care what else is going on really (to an extent of course) as long as the query runs fast. as i said in my first response, my question was a general one. meaning, why is it that reads doesn't necessarily affect duration? i guess that should have been my initial post.
  6. joechang New Member

    a logical read is not a disk read
    thats a physical read

    anyways
    read the top 2 posts in this section
    one of which spells out how to properly diagnose where your resources are going
    otherwise, you are just guessing, and there are many things to guess at
  7. Raulie New Member

    If you want the query duration to run much faster running stop DBCC freeproccache before the query so that it goes to memory instead of disk.

    Raulie



  8. jdubs78 New Member

    so what does the "Reads" column in sql profiler report. Logical or physical?
  9. Raulie New Member

    Logical

    Raulie



Share This Page