Ad-hoc Changes


January 12, 2016 by Kenneth Fisher

TSQL Tuesday #74: Be The Change

T-SQL Tuesday 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.

4 thoughts on “Ad-hoc Changes

  1. […] Kenneth Fisher has some tips to reduce the risk of running ad hoc queries: […]

  2. Brian Jones says:

    Interesting article, and some good thoughts there. I don’t normally comment on posts however I felt there were some tips I’d like to add.

    Firstly, if you create all of your possible connections in the registered server tab in SSMS then this has a cool feature that allows you to colour your connection. Choose two clashing colour groups, one for production and one for non-production (or more if you want to single out dev, test, staging etc). For instance I colour production red shades, dev green shades. It’s a clear warning that hits you in the face.

    Secondly, open up multiple copies of SSMS. Then use one for production only servers and the other for dev only servers. That way you won’t by accident open a new query window, run some code and realise you’ve applied it to a production server by accident. Sounds stupid, but you’ll feel stupid if you do it in error.

    The point on transactions was interesting. I think I disagree with your point (or rather my interpretation of your point). Firstly, if an update warrants transactions (i.e. data integrity etc) then it must have them. Not having them for fear of locking people out is definitely not a good reason and is the road to database recovery/data loss. If the update is that large that it may cause issues, then it has to be run out of business hours. If that’s not possible, then break it down into smaller chunks and therefor smaller transactions. Ones that lock for less than 30 seconds (default timeout) is the aim. Then it can be run during working hours. I would recommend this is done as a matter of course (smaller transactions) anyway; a large update will put pressure on your log file and may even cause it to run out of disk space. It also seems to get slower the bigger the transaction, so this will give you back some time when you need it most.

    Backups are very important for data or ddl changes and for a change that ultimately could affect the data (e.g. an SP which updates, inserts or deletes). Not sure I’d bother for an SP that just selects data.

    Otherwise good points made, I hope mine help aswell!

    • Excellent points. I don’t generally worry about the color of the connection types so much because I do a lot of changing connections but that’s just me. It is still a good suggestion however.

      I have the same problem with multiple copies of SSMS (although I do it frequently anyway). Not to mention I sometimes get confused between them. I have however started using my Admin account (with additional permissions) on a different version of SSMS. What might work really well is having an AD account that is for production only (and your regular account doesn’t have permissions). Then doing a RUNAS on a copy of SSMS that you only use for production. You could even tell the difference if you made that copy of SSMS have a different background color for the query windows to make it even more obvious.

      I would NEVER suggest not using transaction when needed. My point was more directed at creating a transaction just so you had the option of rolling back and ad-hoc update. So you create the transaction, perform your update, check your data, then commit. Thanks for the comment!

  3. sqlsoldier says:

    Thanks for participating. And thanks for the guidelines for making ad hoc changes in production.

Leave a Reply to Kenneth Fisher Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: