You say po-tay-toh; I say physical reads

Consider the following hypothetical conversation between two DBAs.

• Alpha: Hey, take a look at the query running in prod.  It’s generating a ton of reads.
• Omega: 40 thousand reads?  That doesn’t seem so high.
• Alpha: Dude, you need your eyes checked – it’s generated over 20 million.
• Omega: No, I have it right here 40 thousand.
• Alpha: Session ID 100?
• Omega: Yes, it’s the only thing running – 40 thousand.  5 zeros.
• Alpha: Mine says 20 million
• Omega: Well, you’re wrong – I have it right here.
• Alpha: How are you getting that?  sp_whoisactive?
• Omega: No, I’m using sys.dm_exec_requests.
• Alpha: Let me try.  Hmmm, you’re right exec_requests is showing different numbers.
• Omega: Well, obviously Microsoft is wrong – Adam Machanic knows his stuff.
• Alpha: What are you getting as a wait type?
• Omega: PAGEIOLATCH
• Alpha: IO?? Mine says CXPACKET

While this was a hypothetical conversation – it’s a very real scenario inspired by a query I ran this morning on a very busy server.

The conversation above isn’t to point out that one tool is wrong and one is right – it’s to illustrate a common problem in geek circles.  Techno-term overlap and overconfidence in our own understanding.

If you are going to trouble-shoot a problem, head up a root cause analysis, justify a hardware purchase, attempt to appear intelligent in front of your peers, or argue with a co-worker on tool metrics.  First – know what your tool is telling you and then make sure you’re audience is listening in the same language you are speaking.

The confusion above is centered around two different tools using the same term to identify two different things and the confidence of each person in what “reads” means.  The DMV sys.dm_exec_requests uses the term reads to signify “physical reads” and qualifies “logical reads” in the query results as logical_reads.  While sp_WhoIsActive is the reverse.  It uses the term reads to refer to logical_reads and instead qualifies physical reads as such in it’s own query results.

BTW, I totally get why Adam Machanic lists it out this way.  SQL Server reads are ALWAYS logical_reads.  A query may or may not generate a physical_read to load a page into memory if it isn’t already there but it will always read the page out of memory.  So when talking reads I tend to think of logical reads as well.

As for the wait information discrepancy – sp_WhoIsActive goes deep into the OS scheduler and related DMVs to look up thread related information waits and tasks.  That’s not to say they disagree – it’s most likely a timing issue – wait types switch all the time – or the fact that Adam is digging deeper into threads as opposed to the exec_request DMV.  But I am guessing now, and I am not afraid to admit it (overconfidence in our own understanding).

The point I’m trying to make is this.  Be careful when talking tech.  Make sure your audience is on the same page as you – and be very clear when you are making educated guesses.  One of the most important skills I look for when interviewing tech talent is communication skill.  I love being around people who when I ask “what’s the total number of reads on that query” will respond with “logical or physical” or when I ask “Why is CXPacket so high?”, will respond with – “I’m more curious about what is holding up that first thread?”.  My favorite answer of all however??  It’s when they say … “hmmm, I’m not sure – let me research and get back to you”… as long as I actually do hear back from them.