SQL Server

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.

Read Full Article

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.

Read Full Article

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.

Read Full Article

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?

Query Plan Example

If so, this series might be for you.

Read Full Article

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.

Read Full Article