Text-Based Execution Plans

Erik Darling looks at the old SET STATISTICS PROFILE command:

Before you think this is to perf tuning what boxed wine is to pest extermination; it’s not. It’s another tool that has pros and cons. The plan cache is cool too, but cached plans don’t have all the information that actual plans do. You can run Traces or Profiler or Extended Events, but they all sort of have their own caveats, gotchas, and overhead. If you don’t have a monitoring tool, though, what are you left with?

Let’s take a look at what you can do with STATISTICS PROFILE, and then the (rather obvious) limitations. Here’s the setup and a simple query.

I’ll admit that outside of learning what they are, I’ve never used text execution plans.  I’ll read the XML, view the graphical results, pipe them out to SentryOne Plan Explorer (formerly SQL Sentry), etc.  But the text plans never held much allure for me.

Related Posts

All Execution Plans Are Estimates

Grant Fritchey drops a bomb on us: All these resources, yet, for any given query, all the plans will be identical (assuming no recompile at work). Why? Because they’re all the same plan. Each and every one of them is an estimated plan. Only an estimated plan. This is why the estimated costs stay the […]

Read More

The Optimal Kafka Message Size

Guy Shilo wants to figure out the right chunk size for a Kafka message: I wrote a python program that runs a producer and a consumer for 30 minutes with different message sizes and measures how many messages per second it can deliver, or the Kafka cluster throughput. I did not care about the message […]

Read More


October 2016
« Sep Nov »