Azure Data Studio is Microsoft’s cross-platform tool for T-SQL developers. It’s still relatively young, so it has some missing areas of functionality.
When you run DMV queries to look at query plans, they show up as XML, which isn’t very friendly. For example, here I’m running sp_BlitzCache in my Azure SQL DB:
It works, but when you click on a query plan, you get sadness:
Raw unformatted XML – well, that’s nobody’s idea of a good time. What you need is the Paste the Plan extension by @SysAdminDrew.
Download the latest version (VSIX) here, or find it in the extension gallery.
Open the command palette (control-shift-p on Windows, command-shift-p on Mac) and bring up Extensions, Install from VSIX. Point it at the VSIX file you just downloaded. Azure Data Studio will reload itself to enable the extension.
Then, whenever you’re looking at an ugly XML extension plan window, hit control/command-shift-p, Paste the Plan, Open in Browser. Your plan will be uploaded to Paste the Plan, and you can see it in a web browser.
Here’s an animated gif of it in action – click to view it:
Warning: your plan will be on the Internet. If you want to visualize plans locally, you’ll need to wait for Microsoft to implement that feature. You should read and understand Paste the Plan’s privacy page fully before using it.
Enjoy, and thanks again to Drew for writing it! You should follow @SysAdminDrew on Twitter, and check out his other Github repos.
7 Comments. Leave new
would it not be much simpler (and more secure because of the plan-in-the-internet), if I just copy the XML into my clipboard and paste it into the free SentryOne PlanExplorer?
Of course you would have to install it once, but locally (= again increased security, because you do not know about security flaws (or unintended backdors) in the Azure-Addin).
That’s interesting – most folks would be more concerned about back doors in a closed source app, not an open source app.
But I totally agree about the local nature of PE – if you want to make sure the plan stays private, definitely use PE. It’s just that a lot of folks use PTP to ask for public help with their query plans. (Drew wrote this when taking one of our training classes – students use PasteThePlan to turn in their query tuning homework.)
Use whatever one works best for you. It’s great to have choices!
Unless I run it locally, I don’t think the fact that Paste the Plan is built on open source tools is relevant to its security. So, it’s really a difference between trusting Brent vs trusting SentryOne (Note that SentryOne inherits a bunch of trust from Brent due to posts like https://www.brentozar.com/archive/2017/08/things-sql-server-monitoring-software-tell/ ).
However, since paste the plan explicitly states that it is not private, trust is irrelevant (either I trust you that it’s public or I don’t trust you and thus I must treat it as public).
Admittedly, SentryOne is a bit riskier (easier to be compromised by a desktop application), but I’m willing to trust SentryOne not to have accidentally or intentionally compromised their installer.
Personally, I normally use a homebrew adaptation of a third-party javascript visualizer, hosted on one of the machines on my network.
Brian – sorry, I’m not being clear.
The Azure Data Studio extension to upload plans to PTP is open source. That’s the part that runs on your machine.
PasteThePlan.com doesn’t run on your infrastructure. It’s closed source, but you’re just interacting with it by uploading your plans to it. The plans you upload are inherently public. If you’re worried about your uploaded plans being public, that’s kinda like saying, “I’m worried about someone reading the comments I post here on this blog post.” It’s a public site.
I am using the version 1.2.4 and when I click on “explain” it show me the query plan
Right, but that doesn’t solve the problem explained in the post. Give ‘er another read.
I found this great extension https://github.com/showplan/azure-data-studio-queryplan-show.
Local and very detailed explain plan.