How many times have you found a query to be sufficiently performant when testing against sanitized data, only to see it stall once in production? It happens all the time, due to differences between the environments such as workload and volume of data. This may lead you to try out your query in production. After all, the fastest way to tune a query for production is on the production server, is it not? While correct, there are many dangers awaiting those foolish enough to tempt fate with such a cavalier disregard for safeguards and protocols. In this blog, we'll explore some of the risks associated with testing queries in production.
Some Risks to Consider
Those who do would be wise to remember that the point of a "test" is that you are testing something that runs the risk of "Bad Things" happening. You might not be able to think of any risks, but that's because you don't - and can't - know what those are...until you run your tests. Some of the Bad Things that can happen include:
Logical Data Corruption
In the case of INSERT and UPDATE statements, these are by their very nature likely to create records with spurious or malformed data, or that doesn't implement referential integrity correctly. Moreover, the bad data can break logical assumptions that previously tested and well-behaved applications hold about the data. Should an application encounter a faulty record, it may result in anything from "wrong answers" to crashing your entire site until the corruption is identified and manually fixed.
Performance Degradation
A common scenario is that your test app is doing a table scan that you didn't identify in your dev instance because it only contains about 10,000 records, compared to the 100 million in production. Once your application commences a table scan on one or more core tables, your prod database can become deadlocked until you kill the queries one-by-one. Worse still, once your app has used up all the available database connections, you won't even be able to open a command shell on the database to kill the queries.
Locking Issues
If you were to forget to COMMIT a transaction, you could wind up locking half the database because you have uncommitted multi-statement transactions sitting in your database connection pool. As a result, the customer operations could time out and randomly deadlock.
End Users See Wrong Data
Bad data is a problem at many levels. The badness can range from selling inventory that you don't have to having user accounts without passwords that open you to hacks. A related concern is forgetting to delete test users. Since you're probably the only one who knows about them, they're likely to stick around until a hacker manages to discover your "test123" user with the "password123" password.
Unknown and Undefined Risks
The are countless other scenarios of disaster and destruction that are limited only by your imagination or those of higher powers who enjoy watching us suffer and struggle...
Conclusion
Never think that because you "understand" your code that bad things can't happen. Down that path lies madness. You may get away with your shenanigans for a while, maybe even a good while. Nonetheless, the day will come when things go badly, and when they do, they will go very badly.
