Performance & Scalability
In T-SQL Query Plan Analysis Prelude we looked at some common reasons why queries can sometimes go on a snail speed contest and that query plans are one of the ways we can know how to transform those carefree molluscs into caffeinated cheetahs.
So how exactly can query plans help us do that? Are they like magic wands that turn over-ripe pumpkins into dubious-expiry carriages and make used teeth disappear in return for equally used pennies?
Well… Hold that thought for a minute.
Have you ever found yourself pulling your own hair with both hands, desperate to understand why that stubborn T-SQL query runs so desperately slow? Were you intrigued by the cool if complex visuals of its query plan? Did you try to turn its knowledge into insight but couldn’t figure out a Hash Match from a Nested Loop?
If so, this series might be for you.
Sometimes I feel really lazy. I’m just tinkering with a proof of concept database, a decent amount of test data, I just want to do some moderately fast querying over it and don’t really feel like going about creating proper indexes for everything just yet. I really only want to slap a ColumnStore on everything at once so I can get on with work and run some fast questions over the data.
Luckily, that’s what the cheat script below is for.
At some point I was involved in developing some more-than-trivial code to be run as a Stored Procedure in SSAS. This was “more-than-trivial” because there were some separate components that needed to be coded independently and properly unit tested. Notice the use of “properly” in the previous sentence. This entails the correct isolation of dependencies for the objects being tested, so we stay in the realm of unit testing as opposed to integration testing. To do this we need some basic Inversion of Control plumbing put in place, so we can isolate these dependencies during unit testing, but still use the correct objects at run-time. This comes with the added bonus that our code gets naturally decoupled, so we can change or swap implementations of specific objects in the future.
On getting an ETL performance testing system of some sort up and running, one of the Socratic questions that arises at some point is:
“When we run a workload, how much data is there in the database and how does it affect the workload itself?”
OK, so not as Socratic as “What is justice?”, as we can actually measure this one.
