18

Does anyone know of a tool that can be used to compare (relatively complex) query plans? I'm not looking for a guide to query plans, but just a tool that enables me to quickly see e.g. the different index use.

EDIT: just to make it clear, I'm not looking for information about the plan, but a tool that can quickly point out the differences between two plans (I know, I could do that myself, but the size of the plan(s) make that difficult).

davek
  • 22,499
  • 9
  • 75
  • 95
  • well, maybe this guide (http://www.sql-server-performance.com/tips/query_execution_plan_analysis_p1.aspx) can help you a bit – Dimi Takis Nov 06 '09 at 09:54
  • @Greco: thanks for the link, but I'm not looking for a quide, but a tool to compare different plans. – davek Nov 06 '09 at 10:02
  • Just found this, which is a start: http://www.diffnow.com/. Paste your XML files in and compare. – Nick.Mc Jan 13 '14 at 05:33
  • Recent versions of SQL Server Management Studio (SSMS) contain tool for comparing showplans side-by-side. Execute one query and save Execution Plan sqlplan. Now run the other query, right click somewhere inside execution plan window and click Compare showplan and load the file from previous step. Both plans are displayed together and SSMS highlights identical and different parts of plan. – Salman A Feb 15 '18 at 09:23

4 Answers4

10

I'm not aware of a ready-made tool for this, just if you use SET SHOWPLAN_XML ON to get the plans in XML format you can pretty-format them in Visual Studio, and then compare with your favourite text comparison tool.

And you can also right click on the plan in SSMS and choose Save Execution Plan as... to save to a file.

devstuff
  • 8,277
  • 1
  • 27
  • 33
4

There is a tool called PICASSO - http://dsl.serc.iisc.ernet.in/projects/PICASSO/index.html which has an option to compare 2 query plans, provided the queries are the same but with different selectivities.

The to generate the plan differences can be used in your case as it is freely available.

Anonymous
  • 41
  • 1
3

I find that SQL Sentry Plan Explorer (free) is a useful tool analyzing queries, and also comparing them, as highlighted in this blog post.

Community
  • 1
  • 1
jaraics
  • 4,239
  • 3
  • 30
  • 35
0

I generally just compare execution plans with a text compare tool - I also find that for complex plans its useful to look at the raw Xml of the plan just for the ability to search for an index / table to see where it appears in the plan.

Justin
  • 84,773
  • 49
  • 224
  • 367