August 25, 2017 at 3:46 pm
I'm having a technical discussion with a colleague and he keeps insisting that the smallest "retrieval unit" for SQL Server is 64k. Silly me, I thought if I asked for 20 bytes I'd get back 20 bytes. I understand it has to access a page, find the row etc but I thought it's actually only going to retrieve the 20 bytes I asked for plus some overhead depending on whether it's a clustered index seek, a non-clustered index seek etc. Am I completely clueless here? If I turn on Client Statistics and run a simple query to return a few columns from one row, I'm not seeing a minimum of 64k bytes.
"Beliefs" get in the way of learning.
August 25, 2017 at 4:28 pm
Minimum disk read would be 8K, the size of a single SQL page. SQL does indeed organize 8 8K pages into 64K extents, but I don't believe SQL has to read an entire extent to read a specific page.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 25, 2017 at 4:38 pm
No your not crazy, you are looking at the bytes sent to and from the server. You probably have to ask him what he means by retrieval units. SQL Server manages space by extents which are 64K, that may be what he is referring to.
But the Friday afternoon office debates that get poster here sometimes are always interesting.
I take it you showed him the client stats. So now can he prove to you it's 64K?
Just because someone says something doesn't mean it's true and doesn't mean you have to prove them wrong.
Sue
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply