ROWCOUNT Top

If you've looked at any insert, update, or delete plans, including those used in some of my posts, you've probably noticed that nearly all such plans include a top operator.  For example, the following update statement yields the following plan:

CREATE TABLE T (A INT)
INSERT T VALUES (0)
INSERT T VALUES (1)
INSERT T VALUES (2)

UPDATE T SET A = A + 1

Rows  Executes
3      1      UPDATE [T] set [A] = [A]+@1
3      1        |--Table Update(OBJECT:([T]), SET:([T].[A] = [Expr1004]))
0      0             |--Compute Scalar(DEFINE:([Expr1004]=[T].[A]+[@1]))
3      1                  |--Top(ROWCOUNT est 0)
3      1                       |--Table Scan(OBJECT:([T]))

What is that top operator doing right above the table scan?

It is a ROWCOUNT top.  It is used to implement SET ROWCOUNT functionality.  The "est 0" indicates that SET ROWCOUNT was 0 when the query was compiled.  (I suppose "est" is short for "estimate" though the value at compilation time has no impact on query optimization or execution.)  Recall that a value of 0 means return or update all rows.  Since SET ROWCOUNT was 0 at execution time as well, we can see from the STATISTICS PROFILE output that all 3 rows were updated.

Now try the following:

SET ROWCOUNT 1
UPDATE T SET A = A + 1

Rows  Executes
1      1      UPDATE [T] set [A] = [A]+@1
1      1        |--Table Update(OBJECT:([T]), SET:([T].[A] = [Expr1004]))
0      0             |--Compute Scalar(DEFINE:([Expr1004]=[T].[A]+[@1]))
1      1                  |--Top(ROWCOUNT est 0)
1      1                       |--Table Scan(OBJECT:([T]))

Although we get the same plan including the ROWCOUNT top with the same "estimate," this time SET ROWCOUNT was 1 at execution time, the top returned only one row from the table scan, and we can see that only 1 row was updated.

If we force a recompile, we see that the value of the "estimate" changes:

SET ROWCOUNT 1
UPDATE T SET A = A + 1 OPTION (RECOMPILE)

Rows  Executes
1      1      UPDATE T SET A = A + 1 OPTION (RECOMPILE)
1      1        |--Table Update(OBJECT:([T]), SET:([T].[A] = [Expr1004]))
0      0             |--Compute Scalar(DEFINE:([Expr1004]=[T].[A]+(1)))
1      1                  |--Top(ROWCOUNT est 1)
1      1                       |--Table Scan(OBJECT:([T]))

Why doesn't SQL Server add a ROWCOUNT top to select statements?

For example, the following query plan does not include a top yet only returns 1 row:

SET ROWCOUNT 1
SELECT * FROM T

Rows  Executes
1      1      SELECT * FROM T
1      1        |--Table Scan(OBJECT:([T]))

SQL Server implements SET ROWCOUNT for select statements by simply counting and returning the correct number of rows from the root of the plan.  Although this strategy might work for a really trivial update plan such as the one above, it would not work for more complex update plans.  For instance, if we add a unique index to our table, the update plan becomes substantially more complex:

CREATE UNIQUE INDEX TA ON T(A)
UPDATE T SET A = A + 1

Rows  Executes
2      1      UPDATE [T] set [A] = [A]+@1
2      1        |--Index Update(OBJECT:([T].[TA]), SET:([Bmk10061024] = [Bmk1006],[A1025] = [T].[A]))
2      1             |--Collapse(GROUP BY:([T].[A]))
2      1                  |--Filter(WHERE:(NOT [Expr1021]))
2      1                       |--Sort(ORDER BY:([T].[A] ASC, [Act1023] ASC))
2      1                            |--Split
1      1                                 |--Table Update(OBJECT:([T]), SET:([T].[A] = [Expr1004]))
1      1                                      |--Compute Scalar(DEFINE:([Expr1021]=[Expr1021]))
0      0                                           |--Compute Scalar(DEFINE:([Expr1021]=CASE WHEN [Expr1005] THEN (1) ELSE (0) END))
0      0                                                |--Compute Scalar(DEFINE:([Expr1004]=[T].[A]+(1), [Expr1005]=CASE WHEN [T].[A] = ([T].[A]+(1)) THEN (1) ELSE (0) END))
1      1                                                     |--Top(ROWCOUNT est 1)
1      1                                                          |--Table Scan(OBJECT:([T]))

I'm not going to try in this post to explain all of the details of the above plan.  I'll save that for a future post.  However, observe that in updating 1 row, the root of this plan returns 2 rows.  Counting 1 row from the root of this plan would not achieve an accurate result.  By placing the ROWCOUNT top above the table scan, the optimizer can ensure that the server updates exactly the correct number of rows regardless of the complexity of the remainder of the plan.

Comments

• Anonymous
  July 31, 2007
  Hi Craig,thanks again for continuing to blog.The last query plan even though is a very simple statement contains a large number of lines. Could you please talk a little bit more about the logic behind those lines that are part of the query plan but seem not to affect the performance of the query? For instance:1      1                                      |--Compute Scalar(DEFINE:([Expr1021]=[Expr1021]))0      0                                           |--Compute Scalar(DEFINE:([Expr1021]=CASE WHEN [Expr1005] THEN (1) ELSE (0) END))0      0                                                |--Compute Scalar(DEFINE:([Expr1004]=[T].[A]+(1), [Expr1005]=CASE WHEN [T].[A] = ([T].[A]+(1)) THEN (1) ELSE (0) END))Is it important to look at those lines whose executes column is zero? what are they useful for?Thanks again,

• Anonymous
  August 01, 2007
  Last week I wrote about a special case of the TOP operator known as ROWCOUNT TOP . This week I'll take

• Anonymous
  August 01, 2007
  I'll answer your question (about the extra Compute Scalars) in a future post.

• Anonymous
  August 15, 2007
  Insert, update, and delete plans consist of two parts. The first part or read cursor identifies the list

• Anonymous
  December 14, 2008
  Hi Craig -I notice that my query plan (for a simple INSERT INTO SELECT) includes a “break” in parallelism to handle ROWCOUNT (which is the only non-parallel operator in the SELECT part of my query). For example:|--Parallelism(Distribute Streams, RoundRobin Partitioning) |--Top(ROWCOUNT est 0)   |--Parallelism(Gather Streams, ORDER BY:([PtnId1003] ASC, [t].[RequestDate] ASC, [t].[RequestTime] ASC))Does this “break” hurt performance?  If so, given that I never use ROWCOUNT, can ROWCOUNT be disabled-- or is ther any other way to work around this?

• Anonymous
  January 05, 2009
  Depending on how many rows pass through the ROWCOUNT top, it is certainly possible that the serial zone in the query plan will harm performance.Unfortunately, there is no way to disable the ROWCOUNT top.  The problem is that changing the SET ROWCOUNT setting does not trigger a recompile.  Thus, were SQL Server to allow disabling of the ROWCOUNT top, it would break the SET ROWCOUNT functionality if you later chose to use it.  Even though you may never use this functionality, SQL Server does not know that.I'm guessing that you have a subquery causing the problem.  It may be possible to rewrite your query (perhaps using a join in the place of the subquery) to enable the optimizer to move the ROWCOUNT top after all parallelism operators and immediately prior to the insert operator (which is already serial).

• Anonymous
  December 29, 2012
  In 2012 it looks like this operator is only added to plans  run under SET ROWCOUNT of other than zero. As far as I can discern it is added in to the set_options used as a plan cache key.

• Anonymous
  January 02, 2013
  You are correct.  Thanks for the tip!