I’ve received interesting question/requirement few days ago:

“… When viewing a query plan graphically, we usually have to hunt for the most expensive operator.  In some queries, it’s hard to spot ‘em due to the high number of operators.  It would be cool to have a keyboard command that would jump to the most expensive operator, then the next most expensive, and so on. …”

I quite like this idea because from time to time I hit this issue as well. I was playing it with half an hour and found out that it is quite possible by doing this:

SET SHOWPLAN_XML ON
GO
-- query to investigate
SELECT * FROM sys.databases WHERE name = 'master'
GO
SET SHOWPLAN_XML OFF

It produces result containing full XML of query plan which you can freely parse directly in T-SQL or using SQL CLR. XSD schemas for that is available on Microsoft web here. By “XPathing” it, you can select e.g. TOP 10 most extensive operators and show it automatically to user as ordinary table. I was googling little bit and found this post by famous Pinal Dave. It schematically describes how to parse this XML and is very good start.

I was thinking about developing mighty stored procedure which would take your query as dynamic SQL string and would display top 10 bottlenecks in grid. You could bind this procedure to keyboard shortcut in SSMS and have quite nice performance tuning solution. Clean and fast.

It is not that easy because you can’t use SET SHOWPLAN_XML within stored procedure (it needs separate batch). I googled more and found sys.dm_exec_query_plan function which takes plan_handle argument and displays exactly the same as SET SHOWPLAN_XML (and you can use it within procedure). You can pull plan_handle value from sys.dm_exec_requests view which contains every request made to SQL Server instance. It needs more investigation and tuning but it could bring this mighty procedure alive.

I am wondering if you are interested in this solution or if you would use it only rarely. Please vote here.