This blog will no longer be updated.

New content is available on my new blog

relative query cost not accurate when using set rowcount - Piotr Rodak

relative query cost not accurate when using set rowcount

When tuning performance of a query, it is quite common to compare different variants of the query in the same batch and compare the execution plans to see if changes that you made actually work for better or not. Often you would compare just relative cost of execution plan of a query within the batch. I lived for a long time with innocent assumption that the relative cost is accurate and reliable. As Gail Shaw showed in her blog, this assumption doesn't hold if you use scalar functions within a query. I just found out that this is true also for certain type of queries that you would use in specific scenarios.


Let's assume that you have a huge table that is heavily used by OLTP system. Yet you have to delete significant amount of data from it with as small impact for the user as possible. If you just execute delete from table with some key value, it may happen that the amount of affected rows will trigger lock escalation to table level. This will obviously prevent users from inserting or even reading data from the table.
The remedy for such scenarion is to delete rows in batches.
Let's see how we can do it. Here is the table we are going to use.

create table t1(a int identity(1, 1) primary key clustered, b char(10) default('a'))


I will populate it with 100 000 rows:

set rowcount 100000

insert t1 (b)

select 'a'

from master.sys.objects a, master.sys.objects b, master.sys.objects c

set rowcount 0

select count(*) [before batch 1] from t1



Fist approach to delete rows in batches from this table can be as follows:

--batch 1

declare @batchsize int

set @batchsize = 100

set rowcount @batchsize

while 1=1

begin

delete t1 where a < 50000

if @@rowcount = 0

break;

end

set rowcount 0



As you see, we can modify the size of the batch here to adjust it to the size of the table and required degree of concurrency.
The other option is to use rowcount setting, similar to the approach we used to populate the table:

declare @batchsize int

set @batchsize = 100

while @batchsize <> 0

begin

delete top (@batchsize) from t1 where a < 50000

set @batchsize = @@rowcount

end



The query and the loop looks a bit simpler. Which approach is better?  

To be honest both techniques give similar results, at least for the sample data. I didn't have time to run tests for bigger amounts of data, but if you find this interesting, I attach the script so you can check it out for yourself.

More interesting are the execution plans. I removed while loops from above queries and executed them with "Include Actual Execution Plan" option on.

declare @batchsize int

set @batchsize = 100


--query 1

set rowcount @batchsize

delete t1 where a < 50000

set rowcount 0

--query 2

delete top (@batchsize) from t1 where a < 50000


The set rowcount query shows plainly delete from the clustered index. The select top() query shows plan that is more complicated, involving clustered index seek and Top operator. Yet SQL Server shows that the plan with select top() is much more efficient!


 

Why? I checked IO statistics and both queries show the same number of reads:

Table 't1'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 't1'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

If you look though at the estimated and actual numbers of rows that optimizer processed, you will see immediate difference

 

 

 


The optimizer doesn't know that set rowcount @batchsize is in effect and estimates that all rows will have to be processed. The select top() query is parsed by optimizer and it knows that we want only 100 rows, so it estimates that the cost of IO and memory will be much less than in case of set rowcount query. Yet actual numbers of rows are the same, which may explain why my tests show no particular difference between both approaches.

This leads me to conclusion that the relative difference between both plans, as you see on the first picture, is mostly caused by inaccurate estimation of amount of data the engine will have to read and modify. And if this is the case, I think this is worth to keep in mind that it may be that stale statistics may affect your plan comparisons too. You can recognize stale statistics amongst the others by significant differences between estimated and actual numbers of rows in execution plans.

I attach the scripts that I used for tests here: testbatchdelete.zip

Published 27 January 2010 22:33 by Piotr Rodak

Comments

# re: relative query cost not accurate when using set rowcount

Informative post...

Of course, one should get out of the habit of using SET ROWCOUNT anyway, since it is a feature that is set to be deprecated in future versions.

(Love the title of your blog, BTW... use pubs... LOL).

--Brad

29 January 2010 00:40 by brad_schulz