When you’re working with resources that are dedicated instances, powerful instances, you can get caught up a bit in the fact that it can take a lot more to trip up those resources than perhaps you’re used to. It’s sort of like flying as the only passenger in an airplane made for commercial flight; there is likely a lot more capacity there than you’re using and it can mean that you feel comfortable spreading out, taking up a row of seats to relax and layout that work you’re working on.
It’s pretty clear that this is happening too with the resources of today – people are developing code that works well in development (a historical issue, but…)and then rolls to production ok. The first bit of time (that could actually be a good bit of time) will work well and things just hum along.
No capacity issues
No storage issues
No performance issues
Then something happens. Could be a really busy day, could be increases over time to the workload. Could be a new workload too. But something happens that applies more pressure to the servers and processes you’re using.
Then you’re faced with some back-looking, more urgent optimization. It depends on your systems and solutions as to what this may entail. The trend right now is to “just upsize the server instance! No problem.” While I’m sure the provider loves this, it’s not necessarily the best course. Sort of kicking the proverbial can down the road. At some point, you’ll have to address what’s really going on.
This is where you can help out as a data platform person. You’ll know the platform and the type of performance you can expect from it. Apply that knowledge to the application early on.
Do the query tuning.
Do the optimization steps.
Do the load testing.
Make sure the application isn’t taking up the full row of seats just because it can. Make sure you know what the likely stressors will be for that application so you can address them early and have a plan, or correct them outright. It can make a huge difference.
One of the things that’s really helped in this area is to review the query plans for the different things that will be going on. It’s not a difficult thing to get to or see, and you should be able to pass through queries while you’re in development, to see what they’ll be doing and passing in, expecting back, etc. It’s an excellent bit of transparency on what SQL Server is going to be doing with your workload.
It’s also one of the first places you’ll want to be looking if there is an issue. You can get the long-running queries all sorts of ways, and from there you can get the execution plan for the queries that stand out as exceptionally problematic.
