5 Tips for Running SQL Queries on Production Database (original) (raw)

Did you ever face a situation where some of your innocuous-looking action has caused production issues and that too big time? Well, I hope you have not because it's certainly, not a pleasant experience. One such innocuous-looking action is running SQL queries on production databases. I had that in the past very early in my career where I removed some config as duplicates only to find after a week that it stopped publishing messages to one of the downstream. When you work in complex systems that have so many components, millions of lines of code, thousands of configurations, and many databases with hundreds of tables, you have to be really careful with anything you do.

Often there is no real way to perform production-like testing, hence the best bet is to keep your change as much isolated and limited as possible.

Anyway, all this background because today I am going to share some tips with you guys while querying production or live databases to prevent production issues. Since many Java developers are not SQL experts but they do write SQL queries, stored procedures, and interact with both test and production databases, there is a good chance that their innocuous-looking action can cause production issues.

Last year at the same time, we had one such incident where a developer's SELECT query blocked some processes in production. The innocuous-looking SELECT query holds a lock on one of the tables which was needed by the process of trying to update and insert the data into the same table.

The developer runs the query at the end of the day and forgot about that, only to find the next morning that some essential job hasn't completed and they are running from last night. Eventually, DBAs were involved and they kill the connection which is blocking the job and things were restored back.

5 Things to consider while running SQL queries on production databases

Well, this is one of the extreme cases where the developer forgets to cancel the query when it was taking a long time but the probability of happening something like this is quite high, especially if you have production access and don't know much about locking in the database.

The best thing to do is to improve your knowledge of locking and isolation level but to be safe than sorry, you can also follow the following tips while running SQL queries on production:

1. Always query with the NOLOCK option

This can cause a production issue if some job is also running and trying to update the same table you are querying. By saying NOLOCK you reduce the risk of blocking and deadlock like:

SELECT Id, Name, Address from Employee with (NOLOCK) where Id= 2

When you run your query with NOLOCK hint, it instructs the query engine to not issue shared locks and does not honor exclusive locks. When you use with NOLOCK option, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read.

Dirty reads are possible. Though, worth noting is that this option only applies to the SELECT statement and available for Microsoft SQL Server.

SQL tips for beginners

2. First run your query in backup or secondary Database Server

If possible, always run your query on a backup or secondary server instead of running it on the primary server. Only if you just cannot use secondary because you feel data is not the most up-to-date use primary, but the bottom line is to avoid touching the primary or main server on production hours.

3. Test your queries on UAT/Test Database before running on the Production

This is the same rule which I might have told you before when explaining UNIX commands. Similar to the UNIX command which you should test on Staging boxes before running on production machines, you should also first run your query on QA or UAT environment before running them in production. This not only gives you a good idea of what to expect but also saves you from syntax errors and accidental mistakes in production.

4. Avoid touching the Production database during Live hours

If you are working for a system that has some market hours like stock exchanges which run from morning 9 to 4 PM, then you should avoid touching your production database during that period and only run your queries after market hours.

There is a lot of activity that goes on DB during market hours and there is always a good chance that your innocuous-looking SELECT queries may interfere with them.

5. Have a four-eye check on your SQL query

If you are not working alone and have some team members, you can always ask your colleagues to do a four-eye check on the query you want to run in production. If you can review your queries from DBAs then it's even better.

Running SQL Queries on Production/live Databases - 5 Things to Remember

That's all about some important tips to remember while querying the production database. You should also learn more about how the database executes your SQL query like how the index works, how locking works, table scan, row scan, table-level locking, or row-level locking, etc. If you know those basics well, you can predict your queries' behavior more deterministically and potentially avoid unpleasant surprises.

Other SQL and best practices articles you may like