THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

Everybody Needs a Test Harness

When you're developing new Transact-SQL code or modifying some existing code, do you just launch directly into programming?

I know that I did just that, for years. It wasn't until I was trying to performance tune some existing code that I realized I hadn't actually taken caching of data and execution plans into account. So all those modified stored procedures that I was so proud of might not actually be faster than the first generation of procedures because I hadn't checked to ensure that I was testing cached programs against uncached programs (and, by extension, the data used by those programs). That's easy enough to fix with a test harness. Test harness were originally an actual, physical harness used by engineers to clamp down parts of an electrical or mechanical device they were prototyping. Ours is no different. It locks down all of the assumptions about our code (like my early, false assumption that I didn't need to clear the caches) and adds a metric or two for good measure - literally - so we can better measure what's happening in that code.

Here's what my test harness looks like:

/* Transact-SQL test harness by Kevin Kline, http://KevinEKline.com, Twitter at kekline */ 
/* Flush dirty pages from the buffer to the database files. */
CHECKPOINT;
/* Flush the data cache and procedure cache, respectively. For DEV environments only! */
DBCC DROPCLEANBUFFERS; 
DBCC FREEPROCCACHE;
/* Enable statistics tracking for IO and timings. Remember, SET commands remain enabled during a session until disabled. */
SET STATISTICS IO ON; 
SET STATISTICS TIME ON;
-- Whatever SQL code you'd like to process goes below.
SELECT SalesOrderID
FROM Sales.SalesOrderHeader H
WHERE CustomerID = 344
GO
SET STATISTICS IO OFF; 
SET STATISTICS TIME OFF;
/* Textual Execution Plans, if desired. 
SET SHOWPLAN_TEXT ON; 
SET SHOWPLAN_TEXT OFF; 
*/

I also like to include the execution plans a lot of the time. You might wonder why I don't save the execution plans for the GUI in SSMS? Well, I'm a big advocate of scripting in general because I like to automate activities. By pulling the execution plans using scripts, I can use SQLCMD to schedule a large number of query executions during the evening and have the results ready for analysis when I come back into the office in the morning. Workin' smarter, not harder, Baby!

So how does this test harness work for you? Do you use other elements in yours? If so, share your experiences here!

Thanks,

-Kevin

-Follow me on Twitter

Published Monday, October 31, 2011 10:17 AM by KKline

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Mark said:

Great post Kevin.  I'd add nothing to the metrics themselves, but it's probably worth recording metrics from a second run with warm caches.

November 1, 2011 12:24 PM
 

Greg M Lucas said:

Like all good ideas, simplicity itself.  Added to my stock of handy scripts and temnplates.  Thanks Kevin

November 8, 2011 8:23 PM
 

David Wiseman said:

You need to be quite careful when optimizing queries to ensure that your performance testing is fair.  Clearing the buffer and procedure cache is one way to ensure a consistent starting point for your baseline and your optimized code.  

It's worth noting that on a production server, it's an unusual situation for a query to run from a cold cache. I agree with Mark's comment about running the tests again from a warm cache.

I generally try to test performance optimizations using a batch of queries - Ideally a realistic workload, possibly something captured from the live system or a good constructed sample.  Before starting each batch (not each individual query), I would clear the buffer & procedure cache.  I'd then run the same batch again without clearing the cache.  This is done for the baseline and then again for the optimized code.  I like to repeat the test for multiple different batches.  This is mostly to avoid the chance that an edge-case query was run as the 1st query in the batch, polluting the cache with an inefficient query plan.

I prefer to use SQL Trace to capture the statistics I'm interested in - Reads, Writes, CPU and Duration.  SQL: Batch Completed and/or RPC:Completed events are sufficient depending on the mechanism used to run the batches.  I'll then aggregate the data, displaying the results in a spreadsheet.  As well as showing total/average duration etc, I also like to include a pivot on the duration group. e.g. 0-500ms,500ms-1sec,1-2sec...etc.

Repeating query executions and/or running a large number of batches can reduce the impact of random anomalies occurring that interfere with the results. Ideally it's best to test in a sterile environment, on a system similar to your production server, using a backup of your production database.

It can take a while to run these sorts of tests, but it can be worth it.  Assuming you've optimized a query without proper testing can be a dangerous thing.  Maybe the query picked up an inefficient query plan due to parameter sniffing and you haven't really optimized anything. This is one of the reasons it's useful to start from a cold cache to ensure fair testing.  We can repeat the test again to get some more realistic/best case scenario results.

I like to use realistic workloads to test performance optimizations to make sure I haven't optimized 1% of the calls to the query and made the other 99% slower.

This sort of testing is generally the last step in my optimization process - proving that my changes have a positive impact on performance.  Prior to that I'll generally work with a much smaller set of query calls.  I'll analyze query plans using SQL Sentry Plan explorer and profile queries executions with more invasive profiler traces (e.g. at statement level to look inside a stored procedures execution.  I also capture other useful data like re-compilations etc).  Once I've got a handle on the performance issue, I'll work on solutions to it.  Testing on a small scale will give me a good idea how effective the performance solution(s) are. Testing on a larger scale gives me a nice set of stats either proving that the solution works or telling me to go back to the drawing board.

November 10, 2011 4:57 PM

Leave a Comment

(required) 
(required) 
Submit

About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS, www.sqlpass.org.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement