Sql-server – Why does this execution plan have Compute Scalars

execution-planoptimizationperformancesql server

I have a simple SELECT statement.

USE [AdventureWorks2014]
GO

SELECT *
FROM Sales.SalesOrderDetail sod

The execution plan has two Compute Scalar.

Why is this? I was expecting to just get the Index Scan or maybe a Table Scan?

The first (rght-most) has

 [[AdventureWorks2014].[Sales].[SalesOrderDetail].LineTotal] = Scalar Operator(isnull(CONVERT_IMPLICIT(numeric(19,4),[AdventureWorks2014].[Sales].[SalesOrderDetail].[UnitPrice] as [sod].[UnitPrice],0)*((1.0)-CONVERT_IMPLICIT(numeric(19,4),[AdventureWorks2014].[Sales].[SalesOrderDetail].[UnitPriceDiscount] as [sod].[UnitPriceDiscount],0))*CONVERT_IMPLICIT(numeric(5,0),[AdventureWorks2014].[Sales].[SalesOrderDetail].[OrderQty] as [sod].[OrderQty],0),(0.000000)))

When second has:

[[sod].LineTotal] = Scalar Operator([AdventureWorks2014].[Sales].[SalesOrderDetail].[LineTotal] as [sod].[LineTotal])

Best Answer

The Compute Scalars are related to the computed field LineTotal. Script out the table, and you'll see that field defined as:

[LineTotal]  AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),

SQL Server has two operations to perform. It has to first run the calculation of:

[UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty]

Then it has to check to see if that value is null, and if so, substitute 0.0.

Related Solutions

Sql-server – SQL Server Index Scan Actual Executions

Schema and indexes are only one aspect of query plan and performance. Your statement "but with different data" is likely the source of the difference. The number of rows and the distribution of data is essential to the query optimizer. If you have significantly more rows in D2, or if the data is of entirely different characteristics (wider or narrower range of values), then you should expect to see different performance and execution plans.

For each set of statistics, SQL Server keeps a maximum of 200 samples. As the rows in the tables grow and the more irregular the distribution of values the more likely it is that SQL Server will not have enough information to generate optimal execution plans. That's where the use of filtered indexes and statistics comes into play.

If this is a parameterized query you may also be running into a parameter sniffing problem. Note that if you're using local variables the calculation changes also.

Sql-server – SHOWPLAN does not display a warning but “Include Execution Plan” does for the same query

This:

SET SHOWPLAN_XML ON;
GO
SELECT * FROM sys.objects;
GO

Is equivalent to pressing Display Estimated Execution Plan on the toolbar (or hitting Ctrl + L). You'll notice that no rows are returned from the query, like there is when you use Include Actual Execution Plan (Ctrl + M).

The spill warning is only a runtime warning. There is no way that SQL Server can know, when displaying the estimated plan, that a spill will happen at runtime. This is because a spill is caused by factors that might only be present during certain invocations of the query (for example, when there is memory pressure). The estimated plan knows roughly how much memory it's going to ask for, but it can't know until execution that it isn't going to get it.

As an aside, may I recommend* our free tool, SQL Sentry Plan Explorer? I think it provides much more obvious information than Management Studio. I recently wrote a lengthy blog post that can act as a tutorial, and Jonathan Kehayias has a great PluralSight course on it as well.

_{* Disclaimer: I work for SQL Sentry.}

Best Answer

Related Solutions

Sql-server – SQL Server Index Scan Actual Executions

Sql-server – SHOWPLAN does not display a warning but “Include Execution Plan” does for the same query

Related Question