Why Logical Reads Are A Bad Metric For Query Tuning In SQL Server

Рет қаралды 1,864

Күн бұрын

Пікірлер: 26

@mrancourt73 7 ай бұрын

Yes, using one sole metric for anything is just losing sight of the big picture. Thanks for the thoughts Erik and Kudos for your posts :)

@ErikDarlingData 7 ай бұрын

Hahaha, yep, that’s something a lot of people don’t take into consideration.

@gustavswanepoel9207 7 ай бұрын

Highly useful information. Thank you for sharing, Erik.

@ErikDarlingData 7 ай бұрын

My kindergarten teacher would be very happy with me, I suppose.

@ghauan 7 ай бұрын

Hope you and Brent still are on good terms 😉. Just saw his think like the engine for all times sake, and I pretty sure he was talking about using the logical reads. 3:19 (not finished with this video atm though)

@ErikDarlingData 7 ай бұрын

Oh I don’t know, I haven’t seen that material in quite a long time.

@ghauan 7 ай бұрын

But you have some good points in the video. But how can we control the physical reads? I think Brent said we could not control the physical reads mutch?

@ErikDarlingData 7 ай бұрын

@@ghauan You can’t control what’s in the buffer pool (unless you’re constantly priming it with some queries), but you can control the size of the buffer pool (add memory), and how efficiently it’s used (query and index tuning). Again, if the workload is I/O bound, I’m looking for ways to fix that by finding queries that do a lot of physical reads.

@BrentOzarUnlimited 7 ай бұрын

We are indeed on great terms! It's funny, now that I'm working more with Postgres, I'm starting to focus more on CPU time consumed, and it's making me question if logical reads is a good idea on SQL Server, too! Your cloud bills don't go down when logical reads go down.

@ErikDarlingData 7 ай бұрын

@@BrentOzarUnlimited *Aside from the $17 you still owe me from that time we went to Arby's.

@andreysamykin1143 7 ай бұрын

But if there's no corresponding data in the buffer, logical reads will cause physical reads in its turn. So, the more we read, the more data should be in the buffer (physical reads). What's your opinion on that?

@ErikDarlingData 7 ай бұрын

Exactly what I say in the video. When that happens you see physical reads. Fixing those is a good idea. If all you’re doing is logical reads, focus on CPU and duration.

@DanielMaenle 7 ай бұрын

Would it make any sense to just cursory look at logical reads to see if those queries could benefit from a more optimal index which would reduce the amount it needs to do? Or modify the query to then get less data. Those are the only two reasons I have reviewed logical reads, but those queries also usually present themselves in other metrics as well.

@ErikDarlingData 7 ай бұрын

Nah, CPU and duration usually tell a better story. Sure, logical reads may generally reduce as you tune things, but it’s not a reliable indicator that something is slow.

@mrfuzztone 7 ай бұрын

People often add too many redundant indexes or too many include columns trying to reduce reads. That ends up causing competition for RAM. I have worked on systems where I reduced the space used by indexes by 50% and the system got faster. Yeah, probably more logical reads out of RAM. Actually, that is one of those things that keeps SQL Server performance people in business. A lot of people also make mistakes with queries that process a lot of rows. They think they need an extra index or more include columns (or worse, more key columns). They usually don't know what a bitmap filter can do.

@ErikDarlingData 7 ай бұрын

Yep, getting rid of unused and overlapping indexes is huge for a lot of systems, especially when they have way too little memory for what they’re doing anyway.

@robj2118 7 ай бұрын

I guess my thinking was logical reads is something I can usually better control by query turning, index tuning, etc. And by reducing logical reads that would (could) in turn reduce physical reads.

@ErikDarlingData 7 ай бұрын

Something you can better control than what?

@robj2118 7 ай бұрын

@@ErikDarlingData I can better control logical reads than physical reads as I can't predict what's cached. Also, it seems some of the queries CPU time versus duration were odd... seeming some queries were single threaded vs multi-threaded. I guess what I am saying how do I tune towards physical reads vs logical reads (w/o upgrading hardware)?

@ErikDarlingData 7 ай бұрын

I think you're misunderstanding me a bit. I'm saying you should tune queries doing the most physical reads on average the same way you'd tune any other query, assuming that you're on an I/O bound server. Those ones need the most help from a workload perspective. Just make sure you're sampling queries from when users are most active.