Welcome to another edition of T-SQL Tuesday!
This month’s edition is hosted Deborah Melkin (b|t). Deb asks for each of us to share our personal infamous “rant” as we database professionals all have strong opinions about many things!
What’s Your Rant Andy?
Those of you who know me might think this post will wind up being about…
- NCHAR/NVARCHAR vs CHAR/VARCHAR – aka Unicode or non-Unicode
- Leading or trailing commas in SELECT statements?
- All flash or spinning rust?
- Coffee – with creamer or black?
But it won’t be about any of those! Instead, as I often like to do with T-SQL Tuesday themes, I’m going to take a slight twist and talk about what happens after you make a potentially controversial decision.
When X, then Y…
Consequences… ramifications… or afterclaps (my new favorite thesaurus-supplied alternate)… we’ve all had to pay the price of decisions that were made long ago. But sometimes we find ourselves having to make a less-than-desirable decision with only losing options to choose from.
I once worked for an Entity Framework SaaS company that was having horrific T-SQL performance issues. We put out a major release one weekend and spent the subsequent week buried in horrible performance hell. Because all T-SQL code was generated from Entity Framework (stored procedures were banned by a development exec prior to me joining the company), there were no easy fixes that could be implemented (and a Production release typically took 12 – 36 hours, I wish I was joking).
The manager of infrastructure had heard about SQL Server’s missing index recommendations:
- Infra Mgr: Hey Ops DBA (I was a DB Dev). Are there any missing index recommendations in Production?
- Ops DBA: Yeah, over 200.
- Infra Mgr: Creating indexes doesn’t require us to do a Production Release. We can apply these overnight when client use is minimal.
APPLY THEM ALL!!!! - Me: NOOOOOOO!!!!!
I did my best to explain why this was an absolutely horrific idea. But the Infrastructure Manager was desperate for any kind of relief from their performance woes and did not care to listen about how this could make things even worse. I was sidelined and the Ops DBA was ordered to script and implement each and every one.
And what was the afterclap? <giggle>
A couple of our key, gigantic core tables now had over 30 non-clustered indexes on them. Many of which were redundant and/or duplicate indexes as one might expect. A few queries were improved but backup times went through the roof, nightly ETL execution times doubled, and the Production database became horrifically bloated. A month later, I was finally given authorization to go through an index consolidation and clean up effort, which took me another month of workload analysis and index prioritization/consolidation.
… Then Y… but Why?
In the end, the Infrastructure Manager made a less than ideal decision, yes. But that is not what I have a problem with. What I have a problem with is that he refused to listen and understand the ramifications of the decision. He acted in a willfully ignorant manner, choosing not to listen to his subject matter experts, and instead grasp at straws in the hopes of resolving the issues. And the cost in the end was high in terms of cleaning up the bigger mess that was left behind.
Moral of the Story
So what’s the moral of the story here?
I want to encourage you to try and listen and understand the consequences of choosing X over Y. If you really want to save a few bucks and buy spinning disk instead of an all-flash SAN, understand and acknowledge the short term and long term consequences. And sometimes you do need to choose the less-than-ideal option. But if you must, I say do so with a full understanding of the afterclap.
Thanks for reading!
Every Byte Counts 