In Process
One question I get quite a bit is how much I look queries that do a lot of logical reads, and the answer is: not very often.
The primary metric that I look at is CPU, both total and average. If you look at that, you’ll most likely also find the type of queries that do a lot of reads because they’re missing a better index somewhere along the way
If queries run really fast, I don’t really care what they’re doing for logical reads, which are all from pages in memory anyway.
It may be useful to look at in parameter sniffing scenarios, because a query might sometimes do a lot of reads, and sometimes not. But at the same time, that query will also sometimes use a lot of CPU, and sometimes not.
And uh, no, I don’t look at it as a sign that I have to defragment indexes. At least not based on what index maintenance scripts measure.
Phys Ed
Queries — and servers in general — that have a high number of physical reads are a different story.
Looking at these, sure, you might find queries that are missing an index, but most likely what you’re going to find is a server that’s a bit malnourished in the RAM department. Physical reads come from disk, and if your queries are constantly piling these up, then you’re gonna have some stuff to look at.
- Do you have anywhere near enough memory to cache your most frequently accessed objects in memory?
- Do you have queries coming along asking for enormous memory grants that clear out swaths of the buffer pool?
- Do you have too many unnecessary indexes competing for space in the buffer pool and knocking each other out?
Figuring that stuff out can be tough, but can be made easier with a good monitoring tool.
Literacy
One thing that will separate servers where queries do a lot of logical reads from servers that do a lot of physical reads is wait stats.
Servers where queries do a lot of reading pages from disk into memory — physical reads — are going to have higher waits on PAGEIOLATCH_SH and PAGEIOLATCH_EX.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.