In the previous articles we took a look at some simple ways to hunt for those valuable query plans and then at how we can accomplish the same using SQL Trace. There is yet another way of discovering and extracting query plans from an in-use SQL Server and that is to use Extended Events.
In 8 Ways To Hunt T-SQL Query Plans, we looked at some simple ways to get our hands on those valuable query plans. However, sometimes we might want to record query plans as part of a bigger effort to profile and troubleshoot a server, or perhaps optimize it for a given workload.
When this need arises, there are two additional tools we can make use of:
- SQL Trace
- Extended Events
In this pumpkin we’ll take a look at how we can use SQL Trace to accomplish this goal.
In How Can Query Plans Help Us? we looked at how query plans can be made to help us troubleshoot a runaway query. Then in Are Query Plans All We Need? we looked at other tools we need to keep ready in our performance tuning utility belt. With that in mind, it is now time to understand how we can capture query plans to begin with.
So Where Do We Start?
To understand how we can capture query plans, we must first understand what types of query plans are there to capture. Luckily, there is only two of them.
In How Can Query Plans Help Us?, we looked at the nature of T-SQL and then at some of the benefits that understanding query plans can bring us. But are query plans a silver bullet? Can we overcome all optimization challenges with query plans only or are there other tools we need on our utility belt?
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.