在Microsoft SQL Server如何获得查询/存储过程的查询执行计划?
当前回答
您也可以通过powershell使用SET STATISTICS XML ON来获得实际的计划。我写它是为了将多语句计划合并为一个计划;
########## BEGIN : SCRIPT VARIABLES #####################
[string]$server = '.\MySQLServer'
[string]$database = 'MyDatabase'
[string]$sqlCommand = 'EXEC sp_ExampleSproc'
[string]$XMLOutputFileName = 'sp_ExampleSproc'
[string]$XMLOutputPath = 'C:\SQLDumps\ActualPlans\'
########## END : SCRIPT VARIABLES #####################
#Set up connection
$connectionString = "Persist Security Info=False;Integrated Security=true;Connection Timeout=0;Initial Catalog=$database;Server=$server"
$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
#Set up commands
$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
$command.CommandTimeout = 0
$commandXMLActPlanOn = new-object system.data.sqlclient.sqlcommand("SET STATISTICS XML ON",$connection)
$commandXMLActPlanOff = new-object system.data.sqlclient.sqlcommand("SET STATISTICS XML OFF",$connection)
$connection.Open()
#Enable session XML plan
$result = $commandXMLActPlanOn.ExecuteNonQuery()
#Execute SP and return resultsets into a dataset
$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet) | Out-Null
#Set up output file name and path
[string]$fileNameDateStamp = get-date -f yyyyMMdd_HHmmss
[string]$XMLOutputFilePath = "$XMLOutputPath$XMLOutputFileName`_$fileNameDateStamp.sqlplan"
#Pull XML plans out of dataset and merge into one multi-statement plan
[int]$cntr = 1
ForEach($table in $dataset.Tables)
{
if($table.Columns[0].ColumnName -eq "Microsoft SQL Server 2005 XML Showplan")
{
[string]$fullXMLPlan = $Table.rows[0]."Microsoft SQL Server 2005 XML Showplan"
if($cntr -eq 1)
{
[regex]$rx = "\<ShowPlanXML xmlns\=.{1,}\<Statements\>"
[string]$startXMLPlan = $rx.Match($fullXMLPlan).Value
[regex]$rx = "\<\/Statements\>.{1,}\<\/ShowPlanXML\>"
[string]$endXMLPlan = $rx.Match($fullXMLPlan).Value
$startXMLPlan | out-file -Append -FilePath $XMLOutputFilePath
}
[regex]$rx = "\<StmtSimple.{1,}\<\/StmtSimple\>"
[string]$bodyXMLPlan = $rx.Match($fullXMLPlan).Value
$bodyXMLPlan | out-file -Append -FilePath $XMLOutputFilePath
$cntr += 1
}
}
$endXMLPlan | out-file -Append -FilePath $XMLOutputFilePath
#Disable session XML plan
$result = $commandXMLActPlanOff.ExecuteNonQuery()
$connection.Close()
其他回答
除了已经发布的综合答案之外,有时能够以编程方式访问执行计划以提取信息也是有用的。下面是示例代码。
DECLARE @TraceID INT
EXEC StartCapture @@SPID, @TraceID OUTPUT
EXEC sp_help 'sys.objects' /*<-- Call your stored proc of interest here.*/
EXEC StopCapture @TraceID
StartCapture定义
CREATE PROCEDURE StartCapture
@Spid INT,
@TraceID INT OUTPUT
AS
DECLARE @maxfilesize BIGINT = 5
DECLARE @filepath NVARCHAR(200) = N'C:\trace_' + LEFT(NEWID(),36)
EXEC sp_trace_create @TraceID OUTPUT, 0, @filepath, @maxfilesize, NULL
exec sp_trace_setevent @TraceID, 122, 1, 1
exec sp_trace_setevent @TraceID, 122, 22, 1
exec sp_trace_setevent @TraceID, 122, 34, 1
exec sp_trace_setevent @TraceID, 122, 51, 1
exec sp_trace_setevent @TraceID, 122, 12, 1
-- filter for spid
EXEC sp_trace_setfilter @TraceID, 12, 0, 0, @Spid
-- start the trace
EXEC sp_trace_setstatus @TraceID, 1
停止捕获定义
CREATE PROCEDURE StopCapture
@TraceID INT
AS
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql),
CTE
as (SELECT CAST(TextData AS VARCHAR(MAX)) AS TextData,
ObjectID,
ObjectName,
EventSequence,
/*costs accumulate up the tree so the MAX should be the root*/
MAX(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost
FROM fn_trace_getinfo(@TraceID) fn
CROSS APPLY fn_trace_gettable(CAST(value AS NVARCHAR(200)), 1)
CROSS APPLY (SELECT CAST(TextData AS XML) AS xPlan) x
CROSS APPLY (SELECT T.relop.value('@EstimatedTotalSubtreeCost',
'float') AS EstimatedTotalSubtreeCost
FROM xPlan.nodes('//sql:RelOp') T(relop)) ca
WHERE property = 2
AND TextData IS NOT NULL
AND ObjectName not in ( 'StopCapture', 'fn_trace_getinfo' )
GROUP BY CAST(TextData AS VARCHAR(MAX)),
ObjectID,
ObjectName,
EventSequence)
SELECT ObjectName,
SUM(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost
FROM CTE
GROUP BY ObjectID,
ObjectName
-- Stop the trace
EXEC sp_trace_setstatus @TraceID, 0
-- Close and delete the trace
EXEC sp_trace_setstatus @TraceID, 2
GO
您也可以通过powershell使用SET STATISTICS XML ON来获得实际的计划。我写它是为了将多语句计划合并为一个计划;
########## BEGIN : SCRIPT VARIABLES #####################
[string]$server = '.\MySQLServer'
[string]$database = 'MyDatabase'
[string]$sqlCommand = 'EXEC sp_ExampleSproc'
[string]$XMLOutputFileName = 'sp_ExampleSproc'
[string]$XMLOutputPath = 'C:\SQLDumps\ActualPlans\'
########## END : SCRIPT VARIABLES #####################
#Set up connection
$connectionString = "Persist Security Info=False;Integrated Security=true;Connection Timeout=0;Initial Catalog=$database;Server=$server"
$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
#Set up commands
$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
$command.CommandTimeout = 0
$commandXMLActPlanOn = new-object system.data.sqlclient.sqlcommand("SET STATISTICS XML ON",$connection)
$commandXMLActPlanOff = new-object system.data.sqlclient.sqlcommand("SET STATISTICS XML OFF",$connection)
$connection.Open()
#Enable session XML plan
$result = $commandXMLActPlanOn.ExecuteNonQuery()
#Execute SP and return resultsets into a dataset
$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet) | Out-Null
#Set up output file name and path
[string]$fileNameDateStamp = get-date -f yyyyMMdd_HHmmss
[string]$XMLOutputFilePath = "$XMLOutputPath$XMLOutputFileName`_$fileNameDateStamp.sqlplan"
#Pull XML plans out of dataset and merge into one multi-statement plan
[int]$cntr = 1
ForEach($table in $dataset.Tables)
{
if($table.Columns[0].ColumnName -eq "Microsoft SQL Server 2005 XML Showplan")
{
[string]$fullXMLPlan = $Table.rows[0]."Microsoft SQL Server 2005 XML Showplan"
if($cntr -eq 1)
{
[regex]$rx = "\<ShowPlanXML xmlns\=.{1,}\<Statements\>"
[string]$startXMLPlan = $rx.Match($fullXMLPlan).Value
[regex]$rx = "\<\/Statements\>.{1,}\<\/ShowPlanXML\>"
[string]$endXMLPlan = $rx.Match($fullXMLPlan).Value
$startXMLPlan | out-file -Append -FilePath $XMLOutputFilePath
}
[regex]$rx = "\<StmtSimple.{1,}\<\/StmtSimple\>"
[string]$bodyXMLPlan = $rx.Match($fullXMLPlan).Value
$bodyXMLPlan | out-file -Append -FilePath $XMLOutputFilePath
$cntr += 1
}
}
$endXMLPlan | out-file -Append -FilePath $XMLOutputFilePath
#Disable session XML plan
$result = $commandXMLActPlanOff.ExecuteNonQuery()
$connection.Close()
在SQL Server Management Studio:
“Ctrl + M”将生成实际执行计划
“Ctrl + L”将生成估计执行计划
“Shift + Alt + S”为客户端统计
“Ctrl + Alt + P”用于SQL Server Profiler中的跟踪查询。
我最喜欢的获取和深入分析查询执行计划的工具是SQL Sentry Plan Explorer。它在执行计划的细节分析和可视化方面比SSMS更加人性化、方便和全面。
下面是一个示例屏幕截图,让你了解这个工具提供了什么功能:
它只是工具中可用的视图之一。注意应用程序窗口底部的一组选项卡,它可以让你获得不同类型的执行计划表示以及有用的附加信息。
此外,我还没有发现它的免费版有任何限制,会阻止你每天使用它,或者迫使你最终购买专业版。所以,如果你更喜欢使用免费版,没有什么能阻止你这么做。
除了前面所说的,还有一件重要的事情需要知道。
Query plans are often too complex to be represented by the built-in XML column type which has a limitation of 127 levels of nested elements. That is one of the reasons why sys.dm_exec_query_plan may return NULL or even throw an error in earlier MS SQL versions, so generally it's safer to use sys.dm_exec_text_query_plan instead. The latter also has a useful bonus feature of selecting a plan for a particular statement rather than the whole batch. Here's how you use it to view plans for currently running statements:
SELECT p.query_plan
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_text_query_plan(
r.plan_handle,
r.statement_start_offset,
r.statement_end_offset) AS p
然而,与XML列相比,结果表中的文本列不是很方便。为了能够单击要在单独的选项卡中作为图表打开的结果,而不必将其内容保存到文件中,您可以使用一个小技巧(记住您不能只使用CAST(…AS XML)),尽管这将只适用于单行:
SELECT Tag = 1, Parent = NULL, [ShowPlanXML!1!!XMLTEXT] = query_plan
FROM sys.dm_exec_text_query_plan(
-- set these variables or copy values
-- from the results of the above query
@plan_handle,
@statement_start_offset,
@statement_end_offset)
FOR XML EXPLICIT
推荐文章
- 如何在Ruby On Rails中使用NuoDB手动执行SQL命令
- 查询JSON类型内的数组元素
- 确定记录是否存在的最快方法
- 阅读GHC核心
- 获得PostgreSQL数据库中当前连接数的正确查询
- 在SQL选择语句Order By 1的目的是什么?
- Python: List vs Dict用于查找表
- 从现有模式生成表关系图(SQL Server)
- 我如何循环通过一组记录在SQL Server?
- 为什么MATLAB的矩阵乘法运算这么快?
- 数据库和模式的区别
- 如何在SQL Server中一次更改多个列
- 如何从命令行通过mysql运行一个查询?
- 外键约束可能导致循环或多条级联路径?
- for循环和for-each循环在性能上有区别吗?