Friday Links 0.0.19 - Paste The Plan

This is based on an email I send my .NET team at work

Happy Friday,

Paste The Plan

This tool lets you get a query plan from SQL Server Management Studio and share it on the web.

Sometimes you want to share a query plan to a friend to help figure out how to tune the query or apply a good indexing strategy. If they can’t step by your desk, it can be annoying to send screenshots. or tell them the query and let them run it, or try to describe what’s going on over the phone.

In SSMS, you can get the query plan as XML and then paste it into the box. Then you get something like this:

Keep in mind that all query plans on the site are public. Anyone with the URL can view it, and the most recent 20 plans are listed on a page. In fact, that’s where I got that example above. If there’s any privileged client information in your database schema, you shouldn’t use the tool.

Or at least anonymize the schema parts.

How do I view the SQL generated by Entity Framework?

That’s all great, but I’m using Entity Framework: how do I get the SQL command so I can run it in SSMS and retrieve the query plan?

You can always use a profiler like Glimpse, or MiniProfiler, or Prefix. But often you don’t have those installed and you just want to grab one troublesome query, not spend time fiddling with NuGet packages.

The method I’ve generally used is to set the Log property on the Database object. It’s just a lambda that takes a string of SQL and lets you log it however you please.

_dbContext.Database.Log = sql => Debug.WriteLine(sql);

Here we write it to the Debug stream, so you can see it right in Visual Studio if you’re running with the debugger attached. You could also dump it to a file or send it to your logging framework. Whatever makes you happiest.

There’s a lot of other interesting approaches in that Stack Overflow question:

  • ToTraceString() with some fun casting
  • ToString() in EF6+
  • Interceptors

HTML Query Plan

If you’re interested in how Paste the Plan works, you can look at the source code on GitHub, at least for the client side display generation.

It’s using some really fancy XSLT to turn the plan XML into interactive divs and boxes. I didn’t realize you could do so much with XSLT.