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.

Permalink 3 Comments

XML Showplan in 2008

May 28, 2008 at 3:37 pm (SQL Server 2005, SQL Server 2008, TSQL) (, , )

I just found my first fundamental difference between execution plans in SQL Server 2005 and SQL Server 2008. In 2005 when you run an query and have it display an XML execution plan by issuing the statement “SET STATISTICS XML ON” you get a second result set that includes a URL. The same thing happens in 2008. When you click on that URL in 2005 it opens the XML file in a new window. If you want to look at the graphical plan, you have to save the file with  a “*.SQLPLAN” exentension. In 2008 if you click on the URL, it opens a new window, but it shows the graphical plan. What happened to the XML? The developers at Microsoft are smart, that’s what happened. If you then right click on that graphical plan a menu choice “Show Execution Plan XML…” is available. Clicking on that opens the XML plan.

So I went one step further. I knew that the graphical plan in 2005 was just an XML plan, if not so readily accessible (do a “Save As” and force an XML extension on it). So I right clicked on a graphical plan that I generated the old fashioned way. Sure enough, the same menu choice to show the XML was there. Nice functionality.

Permalink Leave a Comment