Automated query performance test

Just got back from San José and San Fransisco!
Went there for two weeks to work on my project for LumiLEDs (Philips Lightning).

A great view on the Golden Gate Bridge!

 
I did something new: needed to test my new Stored Procedures (SP’s) on performance and make an comparison between the ‘new’ and the ‘old’ queries.
For this blog post I rewrote my original code to use AdventureWorksLT2008R2, but it’s easy to rewrite it for AdventureWorksLT.

Like a lot of things you start with an idea:

The basics:
Test two scenarios (S1 – old vs. S2 – new) on performance with a common set of ID’s (TestIDs table) and log the results (TestResults table). Each scenario has three queries/SP’s (Q1 to Q3) with the same resultset, but in this example they also share a common query.

The objects:

 
For this test I made two tables:
TestIDs, which contains a common set of IDs to test:

TestResults, well….for the results 🙂

 
Each scenario has three queries (Q1 to Q3) to test, so in total I’ve made six SP’s.
For each scenario I made a SP to run the individual SP’s with a cursor called ‘sp_TestScript_S#‘.

 
How to start:
Execute the generated script to create all the objects:
Test_scripts

Don’t forget to install/attach AdventureWorksLT(2008R2).

Execute the SP called: dbo.sp_Populate_tbl_TestIDs
The table TestIDs should be filled with ID’s by now.

Now you need to execute both SP’s (‘sp_TestScript_S#‘) at the same time.
You may want to use SQL Agent Jobs and schedule them.

 
The results:
As soon as the SQL Agent Jobs are finished, have a look at the results.
Execute the SP called dbo.sp_TestResults and analyse the results:

For a more detailed overview execute the SP called dbo.sp_TestResult_Details.
In the preview below, I zoomed in on ‘sp_S2_Q3_ProductModelID’:

 
Conclusion:
In my development environment I first tested my new queries and took the ID’s with the longest processing time or the highest result count.
 
You may want to combine:
1000 x Max(ProcessTime)
+
1000 x Max(ResultCount)

Next: use these ID’s to fill up the TestIDs table (sort of a worst case scenario) and run both SQL Agent Jobs (S1 and S2) simultaneously.
 
Have fun with testing! 😀

Advertisements