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! 😀
Clint Huijbers' Blog 