TSQL Tuesday #74: Be The Change

Each month, on the first Tuesday of the month, the announcement for the blog party T-SQL Tuesday comes out. Those that are interested then post their blogs, on the subject selected, on the second Tuesday of the month. If you’ve never heard of T-SQL Tuesday (which would surprise me) it’s a blog party started by Adam Machanic (b/t) over 6 years ago. Robert Davis (b/t), our host this month, has chosen a subject of Be The Change. Specifically data changes.

Now, I like to take the road a little less traveled on these sometimes and as it happens I made a mistake the other day that started me thinking of an appropriate subject. Rather than talk about ways to make data changes I want to talk about the processes around making ad-hoc changes to a system.

What started me thinking about this? We were working on a problem in production. Some code was running slowly and I suspected a bad query plan. I ran sp_recompile on what I suspected was the problem stored procedure. If you’ve never seen this function before it marks a stored procedure (or all procedures associated with a table) to be re-compiled on the next execution.

So what did I do wrong? When I ran this piece of code I didn’t do something very important. I didn’t check with the customer before running it. Possibly not a big deal in all shops but it’s part of our SOP (standard operating procedure).

And what does that have to do with data? Nothing. But it did get me thinking about making ad-hoc changes to a system. In every system I’ve ever worked on there were at least some ad-hoc changes necessary. Usually because of a bug in the code (that was hopefully fixed shortly), sometimes it’s necessary to replace a piece of code that hasn’t been written yet, etc. Either way you are running a piece of ad-hoc code against the database. Note: ad-hoc in this case being code that is not written, formalized and saved into the application code.

What’s the big deal about ad-hoc changes?

• They frequently aren’t tested as careful as application code. In fact I’ll go so far as to say they are almost never tested that well.
• They aren’t as carefully monitored as application code that is going into production.
• They are far more prone to accidents. Running extra code by mistake, forgetting a where clause, etc.

Here’s what I want you to think about. How can you protect yourself and your systems when running ad-hoc changes? Here are some ideas.

• Take some form of backup in case you need to perform an operational recovery.
• Test your code in a non-production environment before running it in production. This one isn’t always possible but still something to keep in mind.
• Make sure that this is the ONLY code in your window or that you are protected by a RETURN or SET EXECUTION OFF at the top of your screen. I have this put in place by default on new query windows. This protects you from running too much code by accident.
• Make a habit of checking what instance you are connected to before running any ad-hoc code. Running code meant for a model or test environment in production can be a very scary thing.
• There is nothing wrong with checking with a co-worker before running your code. Code-reviews are awesome!
• Make sure that your client is aware of what you are doing. This could be the developers of the application, it could be the business itself. Doing something behind the scenes is NEVER a good idea.
• Transactions. There is some good and bad here. If you have to run a big update during a busy time of day then you absolutely cannot use a transaction. You’ll potentially start blocking everyone else. So transactions good, but handle with care.
• Most importantly, follow your SOP! If your company says fill out this form before making a change then make sure that form is filled out. Like them or not these processes are almost always there for a good reason. It might simply be to document what’s happened. Either way if you make a habit of ignoring this stuff you’ll probably be looking for a new job.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tuesday Tagged: ad-hoc queries, microsoft sql server, T-SQL Tuesday