Blind Faith versus Test Oriented programming – SQL Antipattern #004

Working as DBA I  have many opportunities to see blind faith programming in action. Blind faith programming is the practise of migrating a piece of code solution into a Production environment without having tested. The idea is also applied to bug fixes and other systems management processes.

Blind faith is a SQL Antipattern . Read more on how to spot a SQL Antipattern

Placing code or a code fix straight into Production is a dangerous practise. A developer who uses the narrow criteria of “it compiles and functions” creates an environment that could eventually create an outage.9 out of 10 problems I analyse are the result of a build of many smaller issues.

A simple example would be to apply a sql code change. It may fix the immediate data requirement for the end user , but the code changed in such a way to now force recompiles. As the user requests increase for the code increases, so do the execution plan recompiles.  Recompiles are an expensive task and eventually bring the system to a halt.

A Test-driven approach generally leads to a more stable and responsive database server. Using non-production environments , allowing code to be tested in a QA layer – that mimics a Production environment can yield great results/

Performance Tuning using Measuring is an objective approach.When  I’m performance tuning – I try to ask questions focused on how long tasks are taking and how long the task should take. Asking the right question is critical in giving  the customer and me a method to measure any improvements.

The challenge is to test any changes in a lower environment and then move to Production. Programatically  the changes may work , but unless the organisation has invested in “like for like” hardware and IO paths,  a degree of blind faith is required.   

But the overall approach is to test first , allowing the criteria of success for testing to guide decisions on the code. Performance Tuning – The wrong way   emphasises the need to measure and avoid guesswork .

Query Baseline testing is another way to safeguard against blind-faith changes. The Query baseline testing is a technique to buffer against inconsistent testing. When testing a code change, testing on a consistent environment is key to understanding the change impact.