Constant Scan in Execution Plans

July 1, 2008 at 3:05 pm (SQL Server 2005, SQL Server 2008, TSQL) (, , )


I see a lot of searches from people apparently trying to find out what having a Constant Scan in their execution plan means. I can understand why. Here’s the definition from the Books Online:

The Constant Scan operator introduces one or more constant rows into a query. A Compute Scalar operator is often used to add columns to a row produced by a Constant Scan operator.

OK. Very precise and yet, unless you know what the sentence means, reading it can be pretty confusing. The key is to see what Compute Scalar means:

The Compute Scalar operator evaluates an expression to produce a computed scalar value. This may then be returned to the user, referenced elsewhere in the query, or both. An example of both is in a filter predicate or join predicate.

What this usually means is, when you have something like GETDATE() in your query, you’ll see a Compute Scalar. What does that have to do with Constant Scan? You’ll see situations where the query has to create a row to hold it’s data before it can access data from tables. The following example is taken from Dissecting SQL Server Execution Plans:

 

INSERT INTO

 

 

[AdventureWorks].[Person].[Address]
(
[AddressLine1]
,[AddressLine2]|
,[City]
,[StateProvinceID]
,[PostalCode]
,[rowguid]
,[ModifiedDate])
VALUES
(
‘1313 Mockingbird Lane’
,‘Basement’
,‘Springfield’
,’79’
,‘02134’
,NEWID()
,GETDATE()

 

In this example, the execution plan generated starts off with a Constant Scan where the engine creates a row that it begins to populate in order to generate the rest of the data for the insert. You’ll see Constant Scans created in other situations for the same purpose.  I’ve seen them alot when querying against XML or creating XML.

Another instance of Constant Scans is when you have partitioned tables. As this explanation from Microsoft points out, the Constant Scans in these situations represent the partitions.

It’s usually not a big deal or a major performance bottleneck. Spend time worrying about other operators.

3 Comments

  1. Vish said,

    its not yet clear about what it is. Can you just clear it out with one step at a time. thanks in advance.

  2. Vish said,

    one more thing, I have a function as this :

    alter FUNCTION [dbo].[FNAFormulaText] (
    @maturity_date varchar(20),
    @as_of_date varchar(20)
    )
    RETURNS varchar(8000) AS
    BEGIN

    RETURN (@maturity_date)
    END

    Now when i see the execution plan for the following i get the constant scan cost as 92% and scalar compute cost as 8%

    select dbo.FNAFormulaText(‘2009-12-01′,’2009-12-01’)

    what this means? m really very unclear about this.

  3. scarydba said,

    You might want to take the whole execution plan and post it with a question on the SQL Server Central forums. You’ll get a lot more people looking it over that way.

    From what you’ve said, not being able to see the plan, I’m assuming that the constant scan operator is filling out the place holder to provide a space for input for your function. I suspect, again, not seeing it, it’s hard to know, that if you look at the execution count for the constant scan, it’s very high. That’s because it’s being performed against each row in the query.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: