0

I have a pretty simple web app performing CRUD and search on a small dataset (maybe 200K). With everything appropriate (I believe) indexed.

How do I figure out the correct Compute setting? I first did Standard DTU - and that would time out on queries that returned 50 - 150 rows of data. These queries did pull in rows from additional tables (via Entity Framework relationships) but the total DB hit was minimal.

I switched to vCore General Purpose and that runs fine.

But randomly selecting settings until something works ok today - not a great approach. So how do I figure out what Compute setting will deliver what I need?

5
  • 1
    I agree randomly selecting settings is not a good approach. Did you do any analysis of why it was timing out in the Standard DTU case? Was it maxing out DTU? If so what specific component of it? At the moment it isn't even clear that you were hitting resource limits that were resolved by the move vs some other explanation such as parameter sniffing causing different execution plans Commented Jan 15 at 12:57
  • To Martin's point, it would've been helpful and interesting to see the execution plan of the queries timing out before you changed server provisionings.
    – J.D.
    Commented Jan 15 at 13:49
  • @MartinSmith How do I analyze what specifically caused the query timeout? And more generally, what should I be tracking/measuring to determine what Compute settings I should use? TIA Commented Jan 15 at 16:04
  • @J.D. Any guidance on how I can track the execution plan of the queries, and what action(s) I should take from what I learn there will be greatly appreciated. TIA Commented Jan 15 at 16:06
  • 1
    @DavidThielen There's a few tools to help you get that info. Firstly if you're able to run the query to completion manually in SSMS, then you can display the actual execution plan with it. Otherwise if it's just hanging for an intolerable amount of time, you can use a tool like sp_WhoIsActive @get_plans = 1 to get the live query plan which will at least show you what operation is taking a while and point you towards the problem.
    – J.D.
    Commented Jan 15 at 17:15

1 Answer 1

2

There is nothing you can do to figure out what are the correct compute settings for a new application.

I usually address this by creating a test environment and then trying to run some test workloads. This way, I can build a baseline for performance and I can also engage end -users to see if they are satisfied with the current configuration.

You could be more specific if you had to move an existing workload to Azure. You could do a discovery and evaluation analysis to gather the current state and evaluate it to identify the compute resources needed.

Please take a look at this article written by Erin Stellato:

It is an example of how to face this kind of things.

0

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.