Debugging The Right Problem17 October 2017
“Continuous effort - not strength or intelligence - is the key to unlocking our potential.” - Winston Churchill
Sometimes it helps to have experience.
A developer was having an issue with a SQL query running over a large data set. The query was straightforward and on their machine, it ran on under a second. On a similarly sized data set in the Staging environment, it took 50. That’s quite the difference.
The developer had look at all the obvious things. Reviewed the execution plan and tested the code that executed the query. They talked me through all that they had done and to be honest it was the exact approach I may have taken.
Yet, the one thing that stood out was the fact that the query was running on a new Staging database on Azure. So I suggested that we look at its configuration. Th developer was as surprised as me to see that the database had been restored on a Basic Azure pricing tier. Thus, the amount of DTU’s available for processing the query was limited to 5. We scaled the database up to a standard tier with an increased number of DTU’s that matched Production. Our query now matched the performance expected on running it.
Sometimes when looking at issues it is easy to assume it is the code that is at fault. It’s the most logical. Although I would argue that if you have tests based on specifications your code should rarely be the issue. Many a developer has been tricked by a SQL query that isn’t quite thought through but which works on a sample data set only to fail in production. But, it can be an environment issue or setting or something you would not immediately think of. This is where talking the issue through with someone will save you a lot of time. We stress to our less experienced developers that they should seek help with any issue if they have struggled for more than 15 minutes. Talking the issue through with someone (or a rubber duck) results in a faster solution.