Before you get too excited
This isn’t a dive into any of the new automated tuning features of SQL Server 2017.
I’m interested in them because Microsoft thinks they can put us out of business with them, but…
Fat chance.
See, a lot of the automated performance tuning features assume you EVER HAD GOOD PERFORMANCE IN THE FIRST PLACE.
I mean, sure, it may have seemed okay when your database was 5, 10, or 20 GB, but now it’s 200 GB and things have just been getting worse for months. Or years.
These features are cool, but they don’t go in and fix your crappy code, your wrong settings, or get you off that VM with 8 GB of RAM that your VM Admin swears by the grace of his body pillow girlfriend is all a SQL Server will ever need.
After all, he read a book once.
Best of the worst
Our typical customer is worried about…
Keeping the lights on:
- If they’re on the right hardware
- If they can or should move to the cloud
- If they’re meeting RPO/RTO
- If their HA/DR strategy is correct
Decoding hieroglyphics:
- How do I validate server settings?
- How do I figure out what my problems are?
- How do I find my worst queries?
- How do I tune my indexes?
- Bonus points: How do I interpret output from your free scripts?
Some customers are more savvy, have pains around parameter sniffing that they’ve identified, and want help with those. They’re tired of restarting the server, freeing the proc cache, or recompiling queries. But the thing is, there’s usually so much other stuff that gets uncovered, parameter sniffing getting fixed is almost a side effect of other changes.
Some examples:
- An index adjustment took the bad plan choice away
- It wasn’t parameter sniffing, it was a scalar function
- There was a blocking problem
- Non-SARGable predicates buried four nested views deep
The future
So, while I’m totally keen on automated performance tuning, and SQL Server 2017 being able to compare and correct bad plan choices, I’m still not worried about my job.
We still talk to people on 2008-2012 who are locked in because of vendor requirements, or other reasons, who just aren’t going to see those new features in the near future.
And plus, they still assume performance was good in the first place.
Thanks for reading!
5 Comments. Leave new
Hi Erik – gotta say, I really enjoy your posts. What do you think about systems like SAP Hana? I know that there is definitely a learning curve with a system like that (and large costs associated with it), but we have deployed a small instance and I gotta say…it’s a game changer for us. We’re able to deploy normalized tables with hundreds of millions of records and minimal tuning and get unbelievable response times.
Hey James,
You know, I’d love to have a reason to use HANA for something (I can’t just spin up an instance that size in our AWS lab without a good reason). It seems really cool, and I’ve talked to a couple people using it for GPS-enabled device coordinates ingestion. They’re really happy with it.
I’d assume by the relative quickness that major cloud providers have all introduced instances specifically sized for the technology that it’s catching on pretty well.
Someday, perhaps!
I’m not going to fear Skynet until they can code a tool that can ask and understand the answer to “So, what are you *actually* trying to do with that code?”
” VM Admin swears by the grace of his body pillow girlfriend”
It’s like you can see him.
Well, I sell body pillow girlfriend cameras on the side, so yeah.