SQL University – Database Testing and Refactoring week

Welcome back to this week’s installment of SQL University. The topic this time around is a contentious one which is very odd to me. See, most everyone agrees that database testing is necessary, but there is a lot of disagreement on who does the testing, how the testing is to be done, and what is considered an acceptable test. You would think that for a subject that everyone agrees is important there would be some standards set forth, but there is none to be found. My guess is that because people tend to think that their particular situation is unique, but I would argue that most people just don’t understand that difference between doing something and doing something well.

For those that are not aware, refactoring is a fancy word for what the man behind the curtain was doing in the Wizard of Oz. He was turning dials and knobs in an effort to present a display of his (supposed) power. The end user (Dorothy) had no idea about the constant changes being done behind the scenes and for the entire group the end result was a continuous stream of frightening sights and sounds. Now think about refactoring as a way of making changes to a database that improve the end user’s experience without changing the functionality that the end user is expecting. For example, perhaps altering some indexes in order to enhance performance.

Let’s get started.

tempdb

While everyone can agree that database testing is necessary, you will rarely find two shops that perform their testing in the same exact way. Why such disparity? I think a lot of it is simply the result of how things have been done in the past, sadly. Quite often shops are of the mindset “well, this is how we have always done things” and as a result the quality of their work can suffer. Inadequate testing will often result in increased support costs over time and many people can make a case that spending money up front on proper testing actually reduces the overall support costs. (Of course such logic is lost on the people that feel pressure to have something shoved out the door, even if they know it doesn’t work.)

Your role at this level is to raise awareness of the issue. Offer to help people test their new code. Perhaps you could restore a copy of production to a test server, deploy the changes, and then test to see if any issues arise. It may not be much in the way of a proper test/QA cycle, but it sure beats trying to deploy those changes directly to production and crossing your fingers that everything works.

msdb

At this level you need to be more than just aware of the value of testing, you need to anticipate the issues that may arise as a result of the changes being deployed. Start to become familiar with aspects of database design that will not scale. For example, some people love to design their database to use GUIDs as their primary key. If you come across such a design you should immediately recognize the issues that will inevitably arise with the use of GUIDs for this purpose.

Unique constraints are another design feature that may not scale very well. Sure, everything works fine when you have 100 total rows and insert a few rows at a time, but ramp up to a few hundred thousand rows and a few thousand simultaneous inserts and you will see a performance hit as the engine tries to do the work you are asking it to do. I have even seen issues with something as benign as the creation of an indexed view due to the need to have proper ANSI_NULL settings. The point here is that even the smallest of changes can have serious side effects to your normal processing. Awareness is one thing, but being able to immediately recognize the potential side effects is another.

model

If you want to get to this level then you are not only aware of the need for testing as well as the potential problems brought about by various aspects of a database design, you are able to use tools to help you be more efficient in getting the necessary testing completed. The two tools that immediately come to my mind are Benchmark Factory and SQLIO. If you want to get adventurous then you can think about building your own set of tools, perhaps using Powershell in order to develop something custom for your own particular needs.

master

You’ll be here once you have outlined, and deployed, a testing strategy and process that is integrated into your development lifecycle. Bonus points for you if your job title says “DBA” and you are able to implement this successfully in your shop. Not only should your process include a certain amount of rigor, but they should also allow for a variety of scenarios complete with benchmarks gathered along the way in order for you to make an informed decision about the results.

No matter how small the change, proper testing (and proper test plans) are crucial.

resourcedb

Mladen Prajdic has written a series of posts about database testing and refactoring, you should start there. If you want to take a deeper dive, then you can start at this website and even order the book: Refactoring Databases: Evolutionary Database Design.