Since you already have a query to get you the super-most-costliest queries on the server, why not just have it save the plans from each row, as you get the results? Not possible, you say? Sure it is :-) Just create a SQLCLR scalar function, such as the following, and add it to your query:
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = false, IsPrecise = true)]
public static SqlString SaveXmlToFile([SqlFacet(MaxSize = 4000)] SqlString FilePath,
SqlXml XmlData)
{
try
{
File.WriteAllText(FilePath.Value, XmlData.Value, Encoding.Unicode);
}
catch (Exception __Exception)
{
return __Exception.Message;
}
return String.Empty;
}
And there is no need to do any NULL checking of the input parameters via .IsNull() since I am using the RETURNS NULL ON NULL INPUT option:
CREATE FUNCTION [dbo].[SaveXmlToFile](@FilePath NVARCHAR(4000), @XmlData XML)
RETURNS NVARCHAR(4000)
WITH EXECUTE AS CALLER,
RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME [SomeAssemblyName].[FileUtils].[SaveXmlToFile];
Then you can use like this:
SELECT *, Test.dbo.SaveXmlToFile(N'C:\TEMP\XmlQueryPlans\'
+ CONVERT(NVARCHAR(50), qstat.query_plan_hash, 2)
+ N'_'
+ CONVERT(NVARCHAR(10), qstat.statement_start_offset)
+ N'-'
+ CASE qstat.statement_end_offset
WHEN -1 THEN N'InfinityAndBeyond'
ELSE CONVERT(NVARCHAR(10), qstat.statement_end_offset)
END
+ N'.sqlplan', qplan.query_plan)
FROM sys.dm_exec_query_stats qstat
OUTER APPLY sys.dm_exec_query_plan(qstat.plan_handle) qplan;
Since this is a function that can work in any query, it can be automated, unlike an SSMS plugin.
And it is more flexible than embedding a query into a program that will save the plans (though I will say that the PowerShell suggestion from Steve's answer is the next best option).
And, it isn't specific to saving query plans. It can save any XML field or variable, so it can be used in several other places as well :-).
A few easy steps to get the above SQLCLR function working (and pretty much any Assembly you create that needs EXTERNAL_ACCESS or UNSAFE):
The assembly needs to be signed. In Visual Studio, go to Project Properties -> SQLCLR tab -> Signing... button.
"CLR Integration" needs to be enabled:
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
Create an Asymmetric Key in [master] from the DLL:
USE [master];
CREATE ASYMMETRIC KEY [KeyName]
FROM EXECUTABLE FILE = 'Path\to\SomeAssemblyName.dll';
Create a Login [master] from the DLL:
CREATE LOGIN [SomeLoginName]
FROM ASYMMETRIC KEY [KeyName];
Grant the Key-based Login the appropriate permission:
GRANT EXTERNAL ACCESS ASSEMBLY TO [SomeLoginName];
Please notice how none of those steps was to turn the database property of TRUSTWORTHY to ON!!!
Now, if you are interested in profiling a particular query or set of queries (to compare against each other), then check out my answer on a related question on StackOverflow:
Programatically read SQL Server's query plan suggested indexes for a specific execution of SQL?
UPDATE:
@Kin's answer reminded me that there is additional information, beyond the plan itself, that would likely be very useful to save with the plan. At the very least there are all of the additional fields in sys.dm_exec_query_stats, and there is also a sys.dm_exec_plan_attributes DMV that is quite interesting.
If you want to store all of this data into a utility database as Kin is suggesting then it should be fairly straight forward as to how to get the one row per plan of query stats and multiple rows per plan of plan attributes into one or more tables. But if you still want to export the plans, is it possible to also capture this information and keep it with the query plan so you don't have to look at multiple files or whatever? The answer: Yup. We can actually graft the fields from sys.dm_exec_query_stats, on the same row as the current plan, and all of the rows from sys.dm_exec_plan_attributes for the current plan, into the Query Plan XML such that it:
- is still a single file, and
- opens up just the same in SSMS and SQL Sentry Plan Explorer
SELECT *, Test.dbo.SaveXmlToFile(N'C:\TEMP\XmlQueryPlans\'
+ CONVERT(NVARCHAR(50), qstat.query_plan_hash, 2)
+ N'_'
+ CONVERT(NVARCHAR(10), qstat.statement_start_offset)
+ N'-'
+ CASE qstat.statement_end_offset
WHEN -1 THEN N'InfinityAndBeyond'
ELSE CONVERT(NVARCHAR(10), qstat.statement_end_offset)
END
+ N'.sqlplan', STUFF(
tplan.query_plan,
CHARINDEX(N'<BatchSequence>', tplan.query_plan),
0,
(
SELECT stat.data + attrs.data
FROM (
SELECT qstat.*
FOR XML RAW('QueryStats'), BINARY BASE64
) stat(data)
CROSS JOIN (
SELECT * FROM sys.dm_exec_plan_attributes(qstat.plan_handle)
FOR XML RAW('Attr'), ROOT('PlanAttributes'), BINARY BASE64
) attrs(data)
)
)
) AS [SavingXmlToFile]
FROM sys.dm_exec_query_stats qstat
OUTER APPLY sys.dm_exec_text_query_plan(qstat.plan_handle, 0, -1) tplan;
The resulting XML looks like:
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" ...>
<QueryStats
sql_handle="AgAAABFDQzHOhP02ljxkCQcP9kCIEz5aAAAAAAAAAAAAAAAAAAAAAAAAAAA="
statement_start_offset="0" statement_end_offset="-1" plan_generation_num="1"
plan_handle="BgABABFDQzGQSbH4AQAAAAEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=" ... />
<PlanAttributes>
<Attr attribute="set_options" value="251" is_cache_key="1" />
<Attr attribute="objectid" value="826491665" is_cache_key="1" />
<Attr attribute="dbid" value="1" is_cache_key="1" />
<Attr attribute="dbid_execute" value="0" is_cache_key="1" />
...
</PlanAttributes>
<BatchSequence>
<Batch>
...
Grafting new elements into an existing structure is easier with XML DML and the .modify() function, but that only works in an UPDATE statement so it isn't an option in this case.
