在Microsoft SQL Server如何获得查询/存储过程的查询执行计划?


当前回答

预估执行计划

估计的执行计划是由Optimizer在不运行SQL查询的情况下生成的。

为了获得估计的执行计划,您需要在执行查询之前启用SHOWPLAN_ALL设置。

SET SHOWPLAN_ALL ON

现在,当执行以下SQL查询时:

SELECT p.id
FROM post p
WHERE EXISTS (
  SELECT 1
  FROM post_comment pc
  WHERE
    pc.post_id = p.id AND
    pc.review = 'Bingo'
)
ORDER BY p.title
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY

SQL Server将生成以下估计的执行计划:

| NodeId | Parent | LogicalOp            | EstimateRows | EstimateIO  | EstimateCPU | AvgRowSize | TotalSubtreeCost | EstimateExecutions |
|--------|--------|----------------------|--------------|-------------|-------------|------------|------------------|--------------------|
| 1      | 0      | NULL                 | 10           | NULL        | NULL        | NULL       | 0.03374284       | NULL               |
| 2      | 1      | Top                  | 10           | 0           | 3.00E-06    | 15         | 0.03374284       | 1                  |
| 4      | 2      | Distinct Sort        | 30           | 0.01126126  | 0.000504114 | 146        | 0.03373984       | 1                  |
| 5      | 4      | Inner Join           | 46.698       | 0           | 0.00017974  | 146        | 0.02197446       | 1                  |
| 6      | 5      | Clustered Index Scan | 43           | 0.004606482 | 0.0007543   | 31         | 0.005360782      | 1                  |
| 7      | 5      | Clustered Index Seek | 1            | 0.003125    | 0.0001581   | 146        | 0.0161733        | 43                 |

在运行我们想要获得估计执行计划的查询之后,您需要禁用SHOWPLAN_ALL,否则,当前数据库会话将只生成估计的执行计划,而不是执行提供的SQL查询。

SET SHOWPLAN_ALL OFF

SQL Server Management Studio估计计划

在SQL Server Management Studio应用程序中,您可以通过按CTRL+L快捷键轻松获得任何SQL查询的估计执行计划。

实际执行计划

实际的SQL执行计划由Optimizer在运行SQL查询时生成。如果数据库表统计信息是准确的,那么实际计划应该与估计的计划没有太大差异。

要获得SQL Server上的实际执行计划,需要启用STATISTICS IO, TIME, PROFILE设置,如下SQL命令所示:

SET STATISTICS IO, TIME, PROFILE ON

现在,当运行前面的查询时,SQL Server将生成以下执行计划:

| Rows | Executes | NodeId | Parent | LogicalOp            | EstimateRows | EstimateIO  | EstimateCPU | AvgRowSize | TotalSubtreeCost |
|------|----------|--------|--------|----------------------|--------------|-------------|-------------|------------|------------------|
| 10   | 1        | 1      | 0      | NULL                 | 10           | NULL        | NULL        | NULL       | 0.03338978       |
| 10   | 1        | 2      | 1      | Top                  | 1.00E+01     | 0           | 3.00E-06    | 15         | 0.03338978       |
| 30   | 1        | 4      | 2      | Distinct Sort        | 30           | 0.01126126  | 0.000478783 | 146        | 0.03338679       |
| 41   | 1        | 5      | 4      | Inner Join           | 44.362       | 0           | 0.00017138  | 146        | 0.02164674       |
| 41   | 1        | 6      | 5      | Clustered Index Scan | 41           | 0.004606482 | 0.0007521   | 31         | 0.005358581      |
| 41   | 41       | 7      | 5      | Clustered Index Seek | 1            | 0.003125    | 0.0001581   | 146        | 0.0158571        |
 
SQL Server parse and compile time:
   CPU time = 8 ms, elapsed time = 8 ms.
 
(10 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'post'. Scan count 0, logical reads 116, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'post_comment'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 
(6 row(s) affected)
 
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

运行查询后,我们感兴趣的是获得实际的执行计划,你需要禁用STATISTICS IO, TIME, PROFILE ON设置,如下所示:

SET STATISTICS IO, TIME, PROFILE OFF

SQL Server Management Studio实际计划

在SQL Server Management Studio应用程序中,您可以通过按CTRL+M快捷键轻松获得任何SQL查询的估计执行计划。

其他回答

与SQL Server Management Studio(已经解释过了)一样,Datagrip也可以在这里解释。

右键单击SQL语句,并选择Explain plan。 在Output窗格中,单击Plan。 默认情况下,您将看到查询的树表示形式。去看 查询计划时,单击“显示可视化”图标,或按 Ctrl + Shift + Alt + U

预估执行计划

估计的执行计划是由Optimizer在不运行SQL查询的情况下生成的。

为了获得估计的执行计划,您需要在执行查询之前启用SHOWPLAN_ALL设置。

SET SHOWPLAN_ALL ON

现在,当执行以下SQL查询时:

SELECT p.id
FROM post p
WHERE EXISTS (
  SELECT 1
  FROM post_comment pc
  WHERE
    pc.post_id = p.id AND
    pc.review = 'Bingo'
)
ORDER BY p.title
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY

SQL Server将生成以下估计的执行计划:

| NodeId | Parent | LogicalOp            | EstimateRows | EstimateIO  | EstimateCPU | AvgRowSize | TotalSubtreeCost | EstimateExecutions |
|--------|--------|----------------------|--------------|-------------|-------------|------------|------------------|--------------------|
| 1      | 0      | NULL                 | 10           | NULL        | NULL        | NULL       | 0.03374284       | NULL               |
| 2      | 1      | Top                  | 10           | 0           | 3.00E-06    | 15         | 0.03374284       | 1                  |
| 4      | 2      | Distinct Sort        | 30           | 0.01126126  | 0.000504114 | 146        | 0.03373984       | 1                  |
| 5      | 4      | Inner Join           | 46.698       | 0           | 0.00017974  | 146        | 0.02197446       | 1                  |
| 6      | 5      | Clustered Index Scan | 43           | 0.004606482 | 0.0007543   | 31         | 0.005360782      | 1                  |
| 7      | 5      | Clustered Index Seek | 1            | 0.003125    | 0.0001581   | 146        | 0.0161733        | 43                 |

在运行我们想要获得估计执行计划的查询之后,您需要禁用SHOWPLAN_ALL,否则,当前数据库会话将只生成估计的执行计划,而不是执行提供的SQL查询。

SET SHOWPLAN_ALL OFF

SQL Server Management Studio估计计划

在SQL Server Management Studio应用程序中,您可以通过按CTRL+L快捷键轻松获得任何SQL查询的估计执行计划。

实际执行计划

实际的SQL执行计划由Optimizer在运行SQL查询时生成。如果数据库表统计信息是准确的,那么实际计划应该与估计的计划没有太大差异。

要获得SQL Server上的实际执行计划,需要启用STATISTICS IO, TIME, PROFILE设置,如下SQL命令所示:

SET STATISTICS IO, TIME, PROFILE ON

现在,当运行前面的查询时,SQL Server将生成以下执行计划:

| Rows | Executes | NodeId | Parent | LogicalOp            | EstimateRows | EstimateIO  | EstimateCPU | AvgRowSize | TotalSubtreeCost |
|------|----------|--------|--------|----------------------|--------------|-------------|-------------|------------|------------------|
| 10   | 1        | 1      | 0      | NULL                 | 10           | NULL        | NULL        | NULL       | 0.03338978       |
| 10   | 1        | 2      | 1      | Top                  | 1.00E+01     | 0           | 3.00E-06    | 15         | 0.03338978       |
| 30   | 1        | 4      | 2      | Distinct Sort        | 30           | 0.01126126  | 0.000478783 | 146        | 0.03338679       |
| 41   | 1        | 5      | 4      | Inner Join           | 44.362       | 0           | 0.00017138  | 146        | 0.02164674       |
| 41   | 1        | 6      | 5      | Clustered Index Scan | 41           | 0.004606482 | 0.0007521   | 31         | 0.005358581      |
| 41   | 41       | 7      | 5      | Clustered Index Seek | 1            | 0.003125    | 0.0001581   | 146        | 0.0158571        |
 
SQL Server parse and compile time:
   CPU time = 8 ms, elapsed time = 8 ms.
 
(10 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'post'. Scan count 0, logical reads 116, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'post_comment'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 
(6 row(s) affected)
 
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

运行查询后,我们感兴趣的是获得实际的执行计划,你需要禁用STATISTICS IO, TIME, PROFILE ON设置,如下所示:

SET STATISTICS IO, TIME, PROFILE OFF

SQL Server Management Studio实际计划

在SQL Server Management Studio应用程序中,您可以通过按CTRL+M快捷键轻松获得任何SQL查询的估计执行计划。

除了前面所说的,还有一件重要的事情需要知道。

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

在SQL Server Management Studio:

“Ctrl + M”将生成实际执行计划

“Ctrl + L”将生成估计执行计划

“Shift + Alt + S”为客户端统计

“Ctrl + Alt + P”用于SQL Server Profiler中的跟踪查询。

您也可以通过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()