SQL Server Performance Forum – Threads Archive

reads vs. duration

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?

Can you post the code?
Does the tables have relevant indexes?
May be caching issue,http://sqlserver-qa.net/blogs/perft…op-stored-procedures-that-are-recompiled.aspx fyi. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

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.

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

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.

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

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

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

Logical Raulie

]]>