Set Statistics… Profile?

Forgotten, But Not Gone

Two of the best built-in tools to figure out if your query tuning efforts are headed in the right direction are SET STATISTICS TIME ON and SET STATISTICS IO ON. If you’re really fancy, you can use SET STATISTICS TIME, IO ON. By fancy I mean lazy. By lazy I mean efficient. You get the idea. Magic.

They’re so cool that our very own Richard Q. Rump created a website to make better sense of them. This might be a lot to take in if you’ve never used those commands. Most query tuning efforts focus on how many hours/minutes/seconds/milliseconds SSMS says your query runs for. And that’s fine (sort of. sometimes.)! At least you’re tuning queries. Maybe even indexes. You miraculous go-getter, you!

But if you want more information, those commands rule. You’ll probably also start looking at execution plans. You may even notice that when you get an execution plan, STATISTICS TIME  takes a little longer. Well, graphics aren’t free. Neither is XML. Someone has to sit in Azure and read the XML and draw all that stuff. Seriously. Nothing else happens in Azure.

Execution plans have tons of good information, and make it super easy to see which parts of your plan are icky, get missing index requests for a particular run of a query, and all that. But some stuff that the graphical plans have is a pain in the butt to look at and remember.

How many estimated vs. actual rows were on that Merge Join?

What was that Sort sorting?

Which columns were output by that Key Lookup?

Tool tips kind of disappear the second you breathe near your mouse, which isn’t great. If you’ve spent long hours yearning for something that looks a lot like EXPLAIN plans in Oracle, Postgres, and (maybe MySQL? I don’t know. Can anyone connect to it? I’m not sure what PHP is.)

Which brings us to our titular line: SET STATISTICS PROFILE ON

Stylin and Profilin

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.

And here’s what we get back. It’s a bit much all at once, text-wise. We’ll look more closely at things, don’t worry.

But some familiar things should pop out at you. Estimates and Actuals. Costs. Physical and Logical operations. Fun. Yes. If you want a definition of all the columns, follow the BOL black hole to the SHOWPLAN_ALL page.

So you get some of the Estimated and some of the Actual. But the nice thing is that it’s all laid out for you. If there were a huge skew in estimated rows or executions, you’d immediately be able to see it. You could even potentially follow the bad estimate through your plan to see how other things go bad.

Looking a little more closely at the Physical Operations we get some more good information that we’d have to look at in the properties of an actual factual graphical plan.

Because it’s apparently Paul White week here (well, what week isn’t?) If you read our weekly links, you may have seen this article which explains what Nested Loop Prefetching is.

But What If We Crappify That?

I read you reading my mind that I was reading too.

Now we get all sorts of nonsense, but also out first shortcoming. I mean, aside from kind of confusing, text-in-a-grid results. Stick with me here.

What’s the shortcoming? Well, the Warnings column is NULL. If you’ve been staring at SQL long enough, you should know that the convert to a SQL_VARIANT causes implicit conversion, which modern versions of SQL Server warn about. The estimates and actuals are all screwed up. The number looks like you did a bad job importing an Excel file. The plan now goes parallel and does all sorts of Hashing and Filtering and other horrible stuff.

Over in Graphical Plan land, we see the warning and the implicit conversion that you’d jump over your own mother to fix.

What About Missing Indexes?

No. Nope. They don’t show up here. Which can be a good thing, because the missing index requests are ABSOLUTELY INSANE sometimes.

But since you asked, here. It’s not exactly the most prolific missing index request, but it also exposes something sort of annoying about graphical plans, which you may or may not have noticed.

Here’s what we get back from STATISTICS PROFILE

No missing index request, but over where the pictures are pretty…

But here’s the annoying part about even graphical plans! They don’t show you all, or even the best, missing index request in the XML.

Sometimes Helpful

Again, this isn’t always good, great, or helpful. It’s just another potential tool for troubleshooting a query. Yes, you can turn this on along with the other options with SET STATISTICS TIME, IO, PROFILE ON. It’s not a bad way to get some more information out of SQL Server, especially if keeping the ins and outs of a graphical plan in your head is difficult.

Thanks for reading!