This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(105.60000000000001, 10%, 20%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EVE%3C/text%3E%3C/svg%3E)
Hello,
I'm quite new in this area ... hence, apologize for the 'silly question'.
I am using SSMS v18.12.1
My goal is to analyze slow performance in web-based application connected to MS SQL server.
I tried to run 'slow' query in SSMS and then look into 'Actual Execution Plan' as a step 1. As a step 2 ... I executed the same operation/transaction from my web-based application and collected data using SSMS Profiler. From Profiler I also looked in 'Execution Plan'. Eventually I wanted to compare those 2 Plans ... but noticed that it's not very 'friendly' for me and 'comparison of Plans' did not help me.
One recommended me to try SQL Sentry Plan Explorer. I added it to Tools in SSMS and tried to use as well. First, I did not find the feature for Plan Comparison in 'Plan Explorer'. Second, it looks a little bit more user-friendly than SSMS for Plan analysis.
Hence, here are my questions:
First of all, you are using an old version of SSMS. You can get the latest version here.
I'm afraid that there are no clearcut answers to your questions. What is the best is very much a matter of preference. When Plan Explorer first appeared, it came with some fresh ideas, but personally I think SSMS has improved over the years, and I find that I don't use Plan Explorer very often. But I am sure that there are people out there with a dissenting opinion.
When it comes to comparing plans, I have not found the plan-comparison feature in SSMS to be of much value. But again that is a matter of preference. If I want to compare plans, I usually compare the raw XML in Beyond Compare. That is something which only makes sense when plans are very similar, but I want to find subtle differences.
As for being on the right track, I am not sure what your problem is. But if your problem is that a query is slow in the application, and runs faster in SSMS, you may be interested in an article on my web site: Slow in the Application, Fast in SSMS? which give you tips for this situation.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(294.40000000000003, 94%, 38%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES%3C/text%3E%3C/svg%3E)
...maybe I can't compare .xml versions from Profiler vs. SQL SSMS?
...maybe I can't compare .xml versions from Profiler vs. SQL SSMS?
I have not actually collected the XML from Profile before. When I text now, I find that the XML is a single long line. That is not so practical.
But I tested with saving the trace to a table and then I ran
SELECT try_convert(xml, TextData) FROM trace
And when I opened the XML in SSMS, it seemed to be the same format that SSMS produces.
But comparing in XML is only meaningful is plans are very similar.
Now the issue is that this .xml file has too many data collected, and it does not make sense to compare it with the one from SSMS.
Likely I need extract only data related to transaction that I ran? How could I do it?
Thanks
First of all, you should be very restrictive with using Profiler in production environments. If there are plenty of events, Profiler itself can slow down the system. You need to have a very good filter to restrict the number of events.
However, if you enable tracing of actual execution plans, this affects all processes, even if you have a filter, because all queries now need to generate that XML. How much damage it causes depends on the workload. An OLTP workload with a lot of small short statements is particularly sensitive.
You should never direct the trace directly to a table. That is a real go-slower button. But once you have a trace in the Profiler UI, you can save that output to table from the File->Save As menu.
For more extensive tracing, it is better to run the trace server-side. You can set up the trace in Profiler, but immediately stop it and the select File->Export. You can then read the trace with sys.fn_trace_gettable.
Thanks
Yes, if you have a trace with many plans, it can take quite some time to point the right one.
And remember, comparing the XML only makes sense if the plans are quite similar to each other.
Hi @Voldman, Eliya,
The SSMS Execution Plan is suitable for quickly examining the execution logic of simple queries or serving as a foundational tool for daily development debugging. Sentry Plan Explorer, on the other hand, is better suited for in-depth performance tuning scenarios, particularly when analyzing complex queries, stored procedures, or parameter sniffing issues.
The built-in execution plan tool in SSMS is an essential foundational feature, while Sentry Plan Explorer acts as a professional-grade performance analysis extension.
These two tools can be used in combination: first use SSMS to rapidly identify the problem framework, then employ Plan Explorer to delve into detailed analysis and optimization strategies. For DBAs or developers requiring frequent tuning, Plan Explorer's automated analysis and visual capabilities can significantly enhance efficiency in query optimization workflows.
Regards，
Zoe Hui
If the answer is helpful, please click "Accept Answer" and upvote it.