T-SQL Tuesday #105 The Wall

In my career, there are three categories of brick walls I have seen that stand out. The most common situations are; when you are unable to figure something out but is possible to resolve, when you become blocked because of a person, and finally, when you become blocked because of policy. I’ve been fortunate to work with amazing folks in the past and I can’t say there was a single person trying to block me from achieving a task because of a vendetta or even really ideologies. I would like to consider myself a reasonable person however, so I can typically find a middle ground. At the end of the day, does the solution you present achieve the goals of the organization? That is essentially the end all question, if you can present your reasoning in a way that makes business sense logically, you will rarely run into people who will try and stop you. (Hopefully.)

My biggest enemy is generally myself, it’s too easy to get too close to the problem to see around it. The other issue is the exact opposite of an enemy, but oh man does it feel like it some days. Reading policy documents for hours makes it feel like I’m bleeding out of my eyes as does attending countless change control meetings. While it seems like policy can be a problem (and sometimes too much red tape is a problem) it is also a solution. It is the solution to customer expectations and risk adverseness.

Some of my most terrible shifts at work were because a change flowed through without the proper checks and balances, they are there typically for a reason. These kinds of slip-ups have resulted in circumstances such as: having to rebuild exchange servers, cascading deadlocks throughout production, or a server randomly being assigned IP’s of existing servers and disconnecting them. Frustrating as red tape may be, I will deal with red tape any day of the week before having to work another 36-hour shift rebuilding all of the exchange servers with a CEO asking me when E-Mail will be working again.

So when has the red tape frustrated me and made it difficult to get around? Well, my first week into a new job stands out in my mind. I found some interesting performance issues, proposed a few suggestions and had a proof of concept made. A week later I spent four to eight hours and created a full-fledged fix complete with documentation. The issue? We were receiving 5,000-20,000 deadlocks a day on a process that should take 30 minutes and ran for two to four hours. In my mind, this is a pretty big problem because data consistency was not taken into account regarding the isolation levels and it’s hogging all of our CPU that we can use for other tasks. Not to mention it was impossible to get up to date information, data could never be updated same day and was always one day behind.

How did the business see this problem? Well, we have an existing process that has run for 10+ years and causes few issues. We can’t get up to date information quickly, but a small delay is a relatively small price for us to pay to prioritize other tasks and problems. Where would be the increase to revenue or how could we even draw in new business by implementing this fix? Why should we dedicate resources to testing this and rolling it out?

So what ended up happening was that we basically just had the deadlocks in place for another two years with a complete fix ready to be rolled out.

When you encounter something like this, it is very frustrating and you feel powerless to resolve it. So what CAN you do? I’m a firm believer that by choosing to do nothing or ignore the problem, YOU ARE MAKING A DECISION. You have decided no, we are not going to tackle that problem. By choosing to attempt to tackle the problem, the ball is in your court. You could get shot down and nothing will happen, but this shifts the decision-making process onto the business or appropriate management, not you. You are choosing to bring the problem up to the company and give the business an informed database perspective of how to solve a problem and why it should be solved.

So, here is how we take action!

1. Let’s create a proof of concept. Create supporting documentation and create meetings to demo the concept and show the research you have performed.
2. Illustrate WHY this should be a priority for the business.
   Example: By reducing the deadlocks we can achieve:
   • More CPU time, this can now be associated with index maintenance or other software implementations bringing more life from our servers. More life out of our hardware means money saved, being able to implement new features on the server means fewer costs spent on licensing and hardware.
   • Faster data. We can now offer NEW services and revenue streams that were previously unfathomable. Customers can be offered up to date data at a cost if we wanted.
   • Consistent data and less IO processing. Due to the deadlocks and auditing, we were wreaking havoc on our storage IO. Now the IO throughput is less cluttered and we can perform archival tasks easier letting us shift data to inexpensive disk arrays.
3. Provide the risk and downsides. Yes, there will be a developmental implementation and QA effort. Yes, we may have problems and need to troubleshoot or rollback because there are risks. Here are ways we can mitigate these risks and here are the trade-offs.
4. Be receptive to constructive criticism. Not everyone can be as smart as you are, people are going to push back on your ideas some day. Sometimes you will be right, sometimes you will be wrong… sometimes everyone is wrong. If you are receptive to other ideas you may see an answer you did not think of before and learn something new, you’ll also be seen as a leader and not a dictator. A leader is someone people want to follow willingly, a dictator rules through force and burns bridges. There is a reason people flock to companies with strong and encouraging leadership.
5. Put the decision in the higher ups hands. Once you have given the company everything they need to succeed, it is ultimately up to them to decide if it is best for the business. You have done your part, you have become the advocate for the database and given the pros/cons/risks/benefits. If you decided not to do this, it would have been the same as if the company just told you no. You are in control of having an opportunity and making your case. Opportunities don’t always work out, and that’s ok.

It is possible to commit no mistakes and still lose. That is not a weakness; that is life. – Jean-Luc Picard

http://memory-alpha.wikia.com/wiki/Peak_Performance_(episode)

Well, what if… what if it’s not them, it’s you? What do you do when YOU are the roadblock?

A while back I had to find and replace multiple queries in our SSIS catalog which contained ~400 DTSX files. Unbeknownst to me, there is a great find any file with a feature in visual studios. I did some looking around, but no one blogged about how to use this commonly known feature, it was “common knowledge” which happened to be uncommon to me. So if I can’t achieve X using Y, I went ahead and got myself a Z. I decided to build my own tool to perform my own functionality and give me what I want. I tried shredding the XML which I found almost as fun as reading policy documentation and ended up building a Powershell script to achieve what I wanted.

What I probably should have done was ask for help. I’m no stranger to asking for help, but I’m the kind of person to try something a hundred times before asking someone else. Later on, one of my co-workers told me about that neat commonly known feature and it worked out perfect. So asking for help is always an option, but make sure you are asking for help in a manner that lets the helper actually help you. I’m not going to dive too far into this, I think Stack Exchange already has an excellent post on how to achieve this: Stack Exchange – How to ask a good question.

Well, if I consider asking someone for help as a closer to last resort, what else should you do?

1. Sleep on it. (As long as it is not a time-sensitive matter!)
2. Exercise.
3. Shower.

Getting a fresh night’s sleep always helps me get things working again. Sometimes I’ll actually dream up the answer since I tend to obsess about the problem until I know the answer. Most of my answers have come to me while I’m exercising though, as crazy as that sounds. Almost all of my biggest accomplishments came to me mid-run or bike ride, usually resulting in me screaming ‘AH HA!’ randomly on a public path and pulling off to take notes on my phone so I don’t forget. (I tend to get a lot of weird looks when this happens.) Sometimes I’ll get an answer that comes to me mid-shower too, even though that’s sort of an inconvenient place to take technical notes.

Other methods to solving problems:

1. Taking a break.
2. Explaining it.
3. Explaining it to someone else.

A lot of other times I’ll be able to figure out the problem on another day after bashing my head against the wall. Coming back with a fresh perspective (like sleeping on it) can help a lot.

Explaining the process helps me greatly as well and because it does, I keep an assortment of “rubber ducks” on my desk at all times.

When you talk through the process, it forces your brain to work a little differently. When I read over the same process a hundred times, my brain may think it knows the words or interprets things that may not really be there or adds things in. By HAVING to walk from start to finish and declaratively explain the process, I’ll sometimes find issues or business logic problems that I would not have found otherwise.

Making someone else your rubber duck is the next step up, I’d recommend starting with your inanimate object first so you walk through all the low hanging fruit. The person you talk to can interact, ask questions, and raise problems when they are found. They may have additional experience and knowledge you don’t have so they may catch things you would never catch.

Hopefully, this article serves as a sledgehammer for your brick walls. Happy boundary-breaking! Thank you, Wayne Sheffield, for this topic idea. It was interesting to think and flesh out ideas about “the wall” and I’m excited to read about how everyone else handles it!

If you are interested in learning more about T-SQL Tuesday, you can check it out at http://tsqltuesday.com/