0

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?

1 Answer 1

2

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.

4
  • Thanks for taking the time to give such an exhaustive answer. I'll take a look at that article, but basically the process relies on my judgement as it is today (and the tools already provided by SQL Server). The weak spot (regarding my question) in considering the results provided by SQL Server is that it just calculates the effort and resources it's going to spend based on the given query. SQL Server assumes a RBAR is ok and might even tell me how to improve my RBAR, but it won't suggest a different method that should be used. But I totally understand that I might be asking too much...
    – Ronaldo
    Commented Dec 7, 2021 at 14:26
  • @Ronaldo No problem! If by RBAR you mean row by agonizing row, I'm not quite sure I follow. SQL Server is relational, so by definition it doesn't solve problems with a RBAR approach unless the developer explicitly codes their SQL to do so. Basically one of the best things one can do is educate their developers on how to write and test for performant SQL code (e.g. fundamentals and then using execution plans, time and I/O statistics, and wait statistics as tools to analyze the performance of their queries, as they're writing them).
    – J.D.
    Commented Dec 9, 2021 at 3:46
  • J.D. That's exactly what I meant by RBAR. Funny I mentioned SQL Server would not suggest to improve a badly written query, see the error I caught today: The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information. It has already been fixed, just to share my pain hahaha.
    – Ronaldo
    Commented Dec 9, 2021 at 19:49
  • @Ronaldo lol yea that's interesting, can't say I recall ever running into that before. Sounds like some better design patterns might be needed if you guys are running into that limitation or are trying to run SQL Server on a TI-83+ calculator. 😉
    – J.D.
    Commented Dec 9, 2021 at 22:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.