Is there a formula to calculate the max acceptable number of reads a query should have?

Question

How can I measure how far a query is from the threshold of acceptable (regarding performance) based on the number of reads reported after its execution?

Example: Just to make myself clear, if we were talking about miles-per-gallon of a car of which the gas mileage is 40 mpg and we must drive a distance of 40 miles, the threshold for the optimal performance is 1 gallon to travel that distance. Since not everyone drives like Max Verstappen or Lewis Hamilton, it's acceptable if you spend 1.3 or even 2 gallons to make that distance if you're a bad driver. But if you spend 30 gallons to get to a place that is 40 miles away, I can say you're certainly going the wrong way.

Background: sometimes I run sp_whoisactive just to find a query reporting something like 1,248,909 reads (since each page is 8 KB, that means around 9.7 GB of data has been processed from a database of 4.4 GB). That means one query read the equivalent to the whole database twice. When I see something like that, my gut tells me that something is wrong, but the developer sometimes argues that "it's a complex query and it was expected to behave like that". Then I have to improve the query to prove that even though a complex query can consume more resources, there's a threshold that shows you're off the track.

Is there a formula to calculate it based on the number of tables involved, types of joins, use of functions and so on? If not, is there a rule of thumb that could be used to make a logical argument?

Answer 1

Unfortunately no, I don't think you'll find a formula or even a rule of thumb. It's just too various and abstract to have a standard to go by, because it varies on a case by case basis. For query A, 1,248,909 reads for 9.7 GB of data in a database that's 4.4 GB big could be totally correct, whereas query B with the same exact metrics could be a cause for concern and case for optimization. Only you as the DBA / database developer / quasi-database dude can really use your intimate knowledge of the database and consuming applications, and intuition, to determine if an individual query is utilizing a valid amount of resources.

That being said, I would generally be suspect of large amounts of data processing queries especially in such a small database, such as the aforementioned query A above. So I would consider looking into examples such as that, but you also have to consider what is such a query's performance implications on the rest of the server. I.e. is it consuming resources from other queries on a busy server? Is it excessively blocking other queries / locking the underlying tables involved? Does it have a very slow runtime or does it come back in a very acceptable timeframe? If it passes the test on those kinds of questions, while it might be worth logging for future reference, it may not be worth your time to try to optimize now as there's usually bigger fish to fry with other actual performance issues on the server (either other queries actually causing issues or database or server tuning that can be done).

An aside, I'll add that one way you can determine if certain queries are consuming more resources than necessary is by looking at the Requested Memory and Granted Memory vs the Used Memory of your queries. It should be part of the actual execution plan, and SentryOne's Plan Explorer tools allows you to highlight an individual operator (though SSMS may also show you on highlight?) and see those metrics of that operator. Generally I'll find if I have a cardinality estimate issue in my execution plan, where it believes a significant amount of more rows will be returned than actually are, the amount of Requested Memory and Granted Memory significantly exceed the Used Memory. That means not only was an unnecessary amount of resources allocated away from the server to this query, but this query itself had to wait additional time for those resources to be allocated. You can also run into the opposite issue with a query running slower because it underestimated how much Memory it needed so it requested too little from the server. Brent Ozar has a good article An Introduction to Query Memory that goes into further details on those Memory metrics.