Testing for Performance

You know that you should be testing your code. You even know that you should be testing your SQL. But why? We need to make sure that changes to our code are safe, prevent regressions, and that we catch edge cases. But are you testing your code for performance? Changes to code can make your code faster or slower, depending on indexing as well as user defined functions and built-in functions. Different computations can result in different in different execution plans. If changes to your code can cause drastic changes to your application performance, why aren’t you monitoring the performance of your code? Test frameworks, like T-SQL Unit, make it possible to wrap the execution of your stored procedures in other processes. By taking advantage of these hooks it’s possible to time the execution of each procedure and record the results in a table (possibly even correlating each run to the appropriate version from source control). You can see how query performance changes over time. Testing your code is important – you can prevent changes from causing both logical and performance problems.