GilaMonster (12/10/2012)
Jeff Moden (12/9/2012)
GilaMonster (12/9/2012)
No.Page reads != fragmentation.
Do you consider page splits to be a form of fragmentation?
Page splits may cause logical fragmentation (or may not, depending which pages split).
Memory\Page reads/sec (the perfmon counter) is pages (windows memory pages) read into memory from disk because they're referenced in virtual memory but not present in physical memory, ie hard page faults, and isn't even a SQL specific counter. Probably SQL is either being swapped to the page file because there's other stuff on the server using memory or something else on the server is being swapped to the page file
Ah... thanks Gail. Now I see where you're coming from on the "Page reads != fragmentation" thing.
I was questioning that because page splits (which I think of as a sort of "physical fragmentation") can cause relatively large numbers of pages to be read during any form of a scan. In that case, the number of page reads has a direct correlation to the number of and "age" (they empty space may have be inserted to) of page splits because the scans have to read more pages to get the same info as if the pages weren't split.
And sorry for writing about something you already know. Just 'splainin' to myself out loud.
--Jeff Moden
Change is inevitable... Change for the better is not.