1

In SQL Server, we can use set statistics profile on to enable profiling for a session. Then you can execute any statement and the corresponding execution plan will be shown in a tabular form. Is it possible to save this popular form plan into some table so that I can filter against it?

The example of this tablet form execution plan looks like this: enter image description here

2 Answers 2

5

The output of set statistics profile on is returned to the client application as a result set after the normal query results (if any). This can be captured by a client application (like SSMS in your question) but not by T-SQL code.

Here's a PowerShell example that executes the query with STATISTICS PROFILE ON and inserts the statistics profile results into a table for ad-hoc needs.

$query = @"
SET STATISTICS PROFILE ON;
SELECT * FROM dbo.YourTable;
"@

$connectionString = "Data Source=.;Initial Catalog=YourDatabase;Integrated Security=SSPI"
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
try
{
    $connection.Open()
    $command = New-Object System.Data.SqlClient.SqlCommand($query, $connection)
    $queryResults = New-Object System.Data.DataTable
    $statisticsProfileResults = New-Object System.Data.DataTable
    $reader = $command.ExecuteReader()

    # get query results (first result set)
    $queryResults.Load($reader)

    # get stats profile results (second result set)
    $statisticsProfileResults.Load($reader)

    $reader.Close()

    # insert stats profile results into table
    $bcp = New-Object System.Data.SqlClient.SqlBulkCopy($connection)
    $bcp.DestinationTableName = "dbo.statistics_profile"
    $bcp.WriteToServer($statisticsProfileResults)
}
finally
{
    if ($reader -ne $null) { $reader.Dispose() }
    if ($command -ne $null) { $command.Dispose() }
    if ($bcp -ne $null) { $bcp.Dispose() }
    if ($connection -ne $null) { $connection.Dispose() }
}

DDL for stats profile table:

CREATE TABLE dbo.statistics_profile(
      Rows bigint NOT NULL  
    , Executes bigint NOT NULL
    , StmtText nvarchar(MAX)  NOT NULL
    , StmtId int  NOT NULL
    , NodeId int  NOT NULL
    , Parent int  NOT NULL
    , PhysicalOp sysname NULL
    , LogicalOp sysname NULL
    , Argument  nvarchar(MAX) NULL
    , DefinedValues nvarchar(MAX) NULL  
    , EstimateRows real NULL
    , EstimateIO real NULL
    , EstimateCPU real NULL
    , AvgRowSize int NULL
    , TotalSubtreeCost real NULL
    , OutputList nvarchar(MAX)  NULL
    , Warnings nvarchar(MAX)  NULL  
    , Type  sysname NULL
    , Parallel bit  NULL
    , EstimateExecutions real NULL
);
1

The table you see is a second resultset from a normal one-resultset query.

To see it, you need a client capable of multiple resultsets. The standard tools (SQL Server Management Studio, for example) know how to recognize such resultsets and show them as an "execution plan".

If you write your own client, then the plan will come to you as a normal resultset. Fetch all records from the query, switch to second resulset and fetch all records of a plan. So dumping it to a text file, for example, would be easy. And if needed - you can load it to some table.

Currently, there is no way in T-SQL to switch to next resultset. Server can produce them, all client interfaces can read them. But server by itself cannot.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.