Visualize SQL XML Execution Plan using HTML

I’ve always used the Microsoft SQL Management Studio Execution plan feature to visualize the query plan. However today I was experimenting with the SQL XML showplan statement to see if I could learn anything more about execution plans by looking more closely at the code. As soon as I saw the XML it occurred to me that I could use XSLT to convert the output to HTML similar to the way that the SQL Management studio did it. I was curious to try, but before I writing my own, I checked to see if anyone else did it already and found this on github:

JustinPealing/html-query-plan

So, to demonstrate I created a simple SQL using temp tables so that I could perform some very basic tests.

IF OBJECT_ID('tempdb..#header') IS NOT NULL DROP TABLE #header
IF OBJECT_ID('tempdb..#detail') IS NOT NULL DROP TABLE #detail

set nocount on

create table #header
(
 order_no int,
 name  varchar(30),
 email varchar(255),
)

 create table #detail
(
 order_no int,
 item  varchar(30),
 item_code  varchar(30)
 )

Insert some sample data

 insert into #header (order_no,name,email) values (121,'Joe Blogs','j.bloggs@bar.com')
 insert into #header (order_no,name,email) values (122,'Bill Smith','B.Smith@foo.com')

 insert into #detail (order_no,item,item_code) values (121,'Item 1','IT-001')
 insert into #detail (order_no,item,item_code) values (121,'Item 1','IT-002')
 insert into #detail (order_no,item,item_code) values (121,'Item 1','EV-003')
 
 insert into #detail (order_no,item,item_code) values (122,'Item 1','IT-001')
 insert into #detail (order_no,item,item_code) values (122,'Item 2','IT-002')
 insert into #detail (order_no,item,item_code) values (122,'Item 3','IT-003')

Turn on the SHOWPLAN_XML in SQL and run query to produce the XML.

SET SHOWPLAN_XML ON;
go
 SELECT
      h.order_no
    , h.name
    , h.email
    , d.item
    , d.item_code
FROM #header h
JOIN #detail AS d ON h.order_no = d.order_no
WHERE NOT EXISTS (
      SELECT NULL
      FROM #detail od
      WHERE od.item_code = 'EV-003'
      AND h.order_no = od.order_no
      )
GO
SET SHOWPLAN_XML Off;
GO

Then copy and paste the output XML data into a file and upload to the demo page that uses Justins HTML query plan visualizer library.

HTML XML Upload demo

Leave a Reply Cancel reply