Dead Horses
I read a lot of SQL blogs, both new and old. What’s striking is how many blogs seem to cover the same subjects from different angles, over and over again.
This isn’t to knock anyone’s blogging at all — but what I do want to do is try to proffer an explanation as to why this happens.
Many SQL bloggers are consultants. That means we work with (hopefully) many clients, and end up seeing the same very basic problems over and over again.
For FTEs who blog, this can still happen. They may be overseeing developers who often forget lessons learned.
Of course, some FTEs get to deal with lots of very specific, or brand new problems, and have some leisure and luxury to really explore them.
Michael J. Swart and Joe Obbish are fantastic examples of this, but there are even themes within their blogs.
Michael talks a lot about matters of concurrency and blocking. Joe talks a lot about column store, and optimizer issues.
What’s With Developers?
When new developers start, they often
- Have minimal SQL Server experience
- Have bad SQL Server experience
- Have never had SQL Server training
That means that existing code is the standard they look to when they’re writing new code. Any bad habits you’ve got in there become part of your code’s culture, and people will repeat them.
I LEARNED IT FROM WATCHING YOU
If you’ve got any code smells, you can bet that developers will pick up on them for all the wrong reasons.
Functions, non-SARGable queries, poorly written dynamic SQL, bad hints, local variables, table variables — you name it.
That doesn’t rule out finding some bad or old advice out there on the internet, though. There’s plenty of that to go around.
Good DBAs often get called Bad Names
They need to enforce standards that make life difficult for other people, who are often developers.
Stuff that’s easy or convenient for devs is often quite difficult and inconvenient for SQL Server
- No, you can’t use that function
- No, you can’t add a 50 column index
- No, you can’t just join on LEFT(REPLACE(REPLACE(SUBSTRING(CASE WHEN…
For every story you hear about a grumpy DBA, you’ve probably got a person who has been fixing the same type of problems for many years.
By the time developers listen to them, they’re off to a new job. Now grumpy DBA has to start over with whomever replaces them.
Low Standards, No Standards, Bad Standards
Defeating bad coding culture needs a two-pronged approach
- Fix old code so that bad ideas don’t get enshrined as “the way its done”
- Review new code for relapses into bad practices
Another worthwhile approach is to get developers appropriate training or resources, so that they can recognize bad patterns on their own.
Often, that kind of empowerment is more productive than having them report all their code to a figurehead for review.
Thanks for reading!
Brent says: when I was getting started, I repeated a lot of bad practices when I was handed code to start with. “Here, always start with this stored procedure template, it’s what we’ve been using for years.” It was just the default, and no one ever questioned why – me included.
23 Comments. Leave new
I’ve definitely been guilty of following bad patterns because they seem easy, and the codebase is already a mess. I’ve read folks over the years saying how this is the Broken Windows Theory applied to software development: https://blog.codinghorror.com/the-broken-window-theory/ I love the “fix old code” and “review new code” advice for the exact reasons you mention!
Thanks for the mention!
To stretch the analogy, the thing about beating dead horses is that they don’t seem to stay dead. They’re like undead zombie horses or something. Every time a concurrency issue rears up, I’m like “You again?!”
I’m often surprised that there’s something new to learn about a topic I thought had already been beaten to death.
Like you said… developers who are new to a platform will look at existing code for examples of code that has a sort of stamp of approval. You’ve reminded me today that refactoring and removing antipatterns in code is valuable as its own goal.
> the thing about beating dead horses is that they don’t seem to stay dead.
excellent turn of phrase. consider it stolen!
Some are also trapped in the “it didn’t give an error so it works” dogma. So it works until it hurts too bad (to slow) to run. It’s a tale as old as time.
Now we just need some guidance on how you go about convincing business to approve dedicating resources to refactoring…
Indeed. Minimal viable product. Refactoring? Lowest priority in the backlog.
Don’t wait until you see bad code, either new or old. Document what good code looks like and clearly communicate it! My company hired a couple of dozen fresh-out-of-the-box young UI developers in the last year, so I just spent two days writing-up all of the best/standard practices I could think of that should be communicated to every developer. I also documented a development process that involves the DBA at design time, during a code review after unit-testing, and before deployment to production.
Are you by chance able/willing to share this best/standard practices document? I am guilty of looking on the internet to figure out how to resolve a problem, only learning too late that it is not the best way to do it!
I’ve seen plenty of coding standards for C# and other languages. But I’ve rarely seen a coding standard / best practices introduced to the team for SQL. Would be to nice to have a short summary doc with examples to review with new team members.
It’s great fun when the “developer” is your boss. “Of course, a multi-layer nested view with multiple functions and 100 joins will not run well. An index will not fix it. It needs to be rewritten.” “The coding standards are right there. Please follow them.”
Of course, he avoids code reviews, and the rare time he’ll let me review, ignores what I ask him to fix.
The answer always is: “We’ll fix it later, because deadline.”
When I review code I include an explanation, to show why I don’ t like a particular syntax or usage. Sometimes this involves creating a document to be passed around.
Proving that a particular approach is better than another, in an approachable manner, can be quite effective. In addition, when the people that have learned from this move on,they take that knowledge with them and hopefully it will help elsewhere. And the replacement will have a list of documents that the others in the team can show them.
I was a developer for more years that I’ve been a DBA, so I know that sometimes it’s just lack of knowledge and having somebody explain it well is a pleasure.
I think there is often a problem of lack of investment in training and lack of time to improve bad code.
There are always more pressing things than rewriting a query that works OK now but might become slow one day. Of course when it does become slow it’s a big problem and everything stops while it gets fixed. Sadly managers who are see the value on fixing potential issues are few and far between.
On the other hand I don’t believe that table variables or even cursors are automatically bad – its about how and why they are used. Surprised to see local variables on the bogey list – can I ask why and in what context?
Jonathan — I promise, site search is your friend: https://www.brentozar.com/archive/2014/06/tuning-stored-procedures-local-variables-problems/
Good article and in that context I see the point.
I’ve found that database platforms are divided by a common language. My DB career started with SQL Server but has diversified somewhat. I’ve come to appreciate the effort that Microsoft have put into each release and the improvements in the query optimisation engine.
It is easy to forget that the SQL Server engine is remarkably tolerant of queries written in a number of ways. Other DB platforms are more rigid or take different approaches to choosing appropriate indexes and execution plans.
Even so, someone can be an experienced DBA on another platform and get tripped up by applying that platform paradigm on SQL Server
This is very true, though hard to avoid when you inherited millions of lines of SQL code full of scalar functions, weird looping nested views and heaven alone knows what else. We have a set of standards but it has taken many years trying to un-teach some of my colleagues that the advice in them was often just bad; f.e. Do not use CURSORS, you can use a WHILE loop instead…
I keep on fighting the fight though….
“Do not use CURSORS, you can use a WHILE loop instead…”
Are you sure about that? 🙂
reply to self, don’t read too fast… Yes that’s another SQL Server ‘myth’.
Great point, it’s a very forgiving environment. Cuddly… like a teddy bear :). I started in mainframes about 7,000 years ago and they invested a lot of money training me, restricting what I could code and where, and militantly enforcing standards. In short, a very stable but inflexible system. The latter half of my career in relational databases has been the opposite experience. Where’s the Goldilocks zone?
A search on a job posting website for a large metropolitan area shows 5,300 hits for “software engineer”. For “database engineer” it returned only 520 hits. Not many IT professionals seek to self identify as a database engineer. The task of coding SQL or modeling tables often falls to an application developer or database administrators who would rather be doing something else. SQL programming suffers from the same plight as HTML programming or source version control; it’s an integration language with a very large user base of occasional practitioners, but few master it.
Eric – I’d flip that around and say you just don’t need that many database engineers. If I had to guess, I’d say it’s not unusual to see 10 (or much more) developers for every DBA or database engineer in a company.
It’s not that different from saying you only need 1 architect for every 100 construction workers, or 1 maintenance engineer for every 50 hotel rooms. They’re just different ratios of work.
I agree that’s why in many dev teams there is no SQL Server specialist. This can lead to problems like an over-reliance on Entity Framework/Linq etc for example
As a developer I have had some SQL training many years ago. I have one single rule of thumb with SQL. Keep it simple. If it is a complex data issue break it down to multiple simple things. What is true of code and is true of SQL as well is “don’t code to prove how clever you are!”. Better to use temp tables/table variables to build a new data structure than trying to write enormous joins with complex structures to get it out in one hit! If I have a complex problem or performance issue I do something almost unheard of in development. I talk to the dba. You can be suprised how much pain that can save.