How To Get Answers To SQL Server Performance Questions

Exhaustion

It doesn’t matter where you’re posting or what your problem is, there are specific things that people will need to get you an answer.

What they need is often referred to as a reproducible example, or a MCVE. For SQL Server questions, that could include:

The query plan (actual is better than cached/estimated)
The query itself (please format it nicely)
Indexes involved (go figure; these are important)
Sample data (not your actual data, just enough of a resemblance to make it work)
Expected results (this is more important for getting help writing a query, but can be useful for perf questions too)

What no one wants to get into when it comes to performance questions is a giant wall-of-text word problem.

You may be the most eloquent question-asker in the known universe, but having the above items is worth hundreds of millions of words.

Get That Query Plan

Getting a query plan doesn’t have to be hard. What you should aim for is the actual plan, though. Estimated plans leave too much up to the imagination.

You can do it in SSMS by hitting this button, then running your query. Don’t bother with stuff like client statistics. Ain’t no one got time for that, as they say.

The thing is, you may be dealing with a stored procedure or more complex set of queries, and you’re not sure how to just capture the plans for the ill-performers.

Good news, there. You can use my free script sp_HumanEvents and follow the instructions here to do that.

Once you’ve got the plan, DON’T POST A PICTURE OF IT. People who post pictures of query plans on Q&A sites are immediately eaten by an ethereal sinkhole.

You can:

Paste The Plan
Gist The Plan
Choose your own method

What folks need is the plan XML, which you can get by right clicking on the plan you care about:

Textual Healing

Of course, the query plan is useful for most things. It’s useful to have the query as well, though.

Sometimes you get lucky and it’s shorter than the limit imposed in the query plan XML, other times you need to get the text elsewhere.

You can capture the query text in several ways, too:

sp_HumanEvents, like above
sp_WhoIsActive
sp_helptext, if it’s a proc/function, etc.

All of these things allow you to get the query, but now you need to make it readable for people. No, I don’t mean putting it in a CTE. I mean formatting, baby.

Generate Table And Index

The first two items above should be enough to get you some advice on why your query is slow, but some folks might also want to see what’s going on underneath.

It’s relatively easy to script out a table definition and also get any indexes attached via SSMS.

SQL Server SSMS Script Table — backstreets

Often, column data types and current indexing tell a part of the story that isn’t captured in just the plan or the text.

Thoughtful

I’m not going to talk about anonymizing stuff here. I know that Plan Explorer claims to do it, but it does not fully anonymize things.

You’ll still find things like statistics names, and linked server details remain intact.

If you need to generate test data, here are some ways to do it:

One other thing I want to make sure is included here, is don’t say things like “I have a problem with this query”. Be specific. If you’re getting an error message, post the error message. if you’re getting incorrect results, post those and the correct results and explain why they’re wrong.

No one can see what’s on your screen. If you want help for free, you’re gonna have to do the work.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.