Extended per-operator level performance stats for Query Processing

  • Article

Back in March we announced the availability of per-operator level performance stats for Query Processing (exposed in Showplan XML) with SQL Server 2014 SP2 and SQL Server 2016, such as CPU and I/O.

Observe the example, where by executing the below query in AdventureWorks2014, on SQL Server 2016 SP1, we get a Spill Warning in the Sort operator. I’m using the legacy CE to force this behavior, and limiting to one thread for the sake of simplicity:

 SELECT *
FROM Sales.SalesOrderDetail SOD
INNER JOIN Production.Product P ON SOD.ProductID = P.ProductID
ORDER BY Style
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'), MAXDOP 1)
GO

image

Notice the new properties in the runtime operator performance stats we get for the Sort:

 <RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="121317" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="782" ActualCPUms="637" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="4424" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" InputMemoryGrant="44792" OutputMemoryGrant="45296" UsedMemoryGrant="44792" />
</RunTimeInformation>

And the Hash Match:

 <RunTimeInformation>
  <RunTimeCountersPerThread Thread="0" ActualRows="121317" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="42" ActualCPUms="42" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" InputMemoryGrant="46192" OutputMemoryGrant="1904" UsedMemoryGrant="640" />
</RunTimeInformation>

As requested by the community to complete the per-operator information, starting with SQL Server 2016 SP1 we are now exposing memory grant per grant iterator (such as Sorts and Hash Matches). These give you added insight into memory grants, and how overall memory usage is driven throughout execution.

In the October release of SSMS we exposed per-operator stats directly in the Properties window for each operator, but the memory grant properties are not there yet. These will be added to the properties window in an upcoming release of SSMS.

Pedro Lopes (@sqlpto) – Senior Program Manager