T-SQL Tuesday 105: Brick Wall

Words: 579

Time to read: ~ 3 minutes

Welcome to another edition of T-SQL Tuesday! Amazing to think that we are properly into triple digits of this blog hosting party.

This T-SQL Tuesday is hosted by Wayne Sheffield ( blog | twitter ) and he has asked us to talk about

[…] a time when you ran up against your own brick wall, and how you worked it out or dealt with it.

This is going to be a strange post…

…because it’s not really going to be about SQL Server. It is going to be about my current brick wall though so I feel it counts.

Currently, my brick wall is with people’s attitudes., mainly in an unwillingness to learn or try new ways because they believe that they are right or know better.

Apologies to all if this comes across as complaining; I’m not fond of that.

I recently moved…

…jobs (and immigrated back to Ireland from the UK) to take up a DBA position for a company that had never had a DBA before. I mention this because it lays across two points:

1. There is a lot of administration to do with backups, restores, monitoring, documentation, etc.
2. All SQL queries have been done by the developers.

The Brick Wall…

…is that these SQL queries are written in a style that I called “C# style” i.e. lots of loops and WHILE blocks.

So in between creating an inventory list and checking the recovery plans on the different servers, when I get called over for a query timeout that’s “happening more and more”, I’m going to suggest a set-based solution.

But here’s the kicker, the conversation gist goes something like…

• Try it this set based way, it’s going to be faster.
• No it’s not. The while loop is going to be just as fast.
• Eh what? It’s going to need to parse and compile the query for every one of those rows, that’s going to slow things down.
• No it’s not. There won’t be any difference, it must be something else that’s the problem.

*  another “incident” happens  *

• Look, just try it the set based way. If you want, give me an hour and I’ll show you the differences in executions.
• Fine, go for it. We’re going to look for actual root causes.
• Okay, but I’m pretty sure that’s the root cause…

*  an hour later  *

• Here you go, see the difference?
• That? That’s barely anything, that’s just discrepancies in the test. Our way will speed up with sufficient data.
• Okay, I’ll increase the test size…

*  an hour later  *

• There you go, see the difference has gotten even bigger now.
• You’ve just added the same rows over and over again. No customer data is going to be like that. If it was actual data, then they’d be the same.
• …you figure out that root cause yet?
• We’re still working on it.

There’s problems on both sides here…

…on one side with the refusal to believe and the other side with the inability to convince.

And so the brick wall stands, firm and stout.

I don’t actually know how to get my point across so if anyone has encountered something like this before share the knowledge of how you fixed this, please!

This, combined with working on Administration and no visibility on the queries being developed, means I’m going to have a lot of queries with low performance to watch for in the future.

Luckily I have “set up performance monitoring” next on my to-do list.

Fun times!