August 16, 2016 at 5:32 am
Hi,
If I have an SQL statement, I would like to get the estimated query plan in XML file without SSMS or SQL profiler, is that possible? The scenario is I need to develop an application to analyse the SQL statement's query plan via C#/.NET.
Thanks,
August 16, 2016 at 5:46 am
yujinagata72 (8/16/2016)
Hi,If I have an SQL statement, I would like to get the estimated query plan in XML file without SSMS or SQL profiler, is that possible? The scenario is I need to develop an application to analyse the SQL statement's query plan via C#/.NET.
Thanks,
You could in theory grab it from the plan cache. Why would you want to do it? What do you expect to achieve? There are a number of third-party tools with capabilities which at least overlap and probably cover your requirement. SQL Sentry Plan Explorer is probably the best - it's certainly the best-known. It's also been through a number of revisions over the years (with a significant one rumoured before the end of the month). Building an app to generate a graphic plan from the XML file probably isn't too much of a big deal, but extrapolating anything more from it than existing apps can do would be a nice challenge 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 16, 2016 at 6:25 am
You can capture plans using extended events (or trace, but I wouldn't suggest trace since it's filtering mechanism is so weak and capturing execution plans is so expensive), but be sure you put good filters in place to only capture for the query you're currently interested in. Capturing execution plans in this way can be expensive in terms of load on the system.
Other than that, yeah, query the cache. All plans stored there are technically what are known as "estimated" plans. Just know that an estimated plan is simply an actual plan without a few runtime metrics. They're effectively the same. Also know that querying the cache you may not find all queries run on the system. They can age out of cache, or, some queries are never stored in cache (for example, a query with OPTION RECOMPILE).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply