Redheadatabase

About me: My name is Maria Zakourdaev. I have more than 10 years experience with SQL Server (starting from SQL Server 6.5). The last five years have been spent mostly on benchmarking different SQL Server features and flows, like data import, indexes impact on DML flows, star transformations in RDBMS, Hierarchic queries and custom OLAP-like aggregations. I was a speaker in the Microsoft Teched (Israel) on the SQL Server track. I am also an active member of the Israel SQL Server Group.

Thursday, December 13, 2007

Adding check constraint to the partitioned table in SQL 2005 always scans ALL partitions

Yes. Even if constraint checks partitioning column and "bad" data can exist in one partition only.
See for yourself: I create table natively partitioned on integer field with another char field to fill more pages (full script at the end of message), I fill the table
with 1000000 rows. I also have a nonclustered index in partitioned column.
For a design of our application i need to add check constraint (i >2) to
the table.
create partition function f (int) as range for values (5,50,100,500);
create partition scheme s as partition f all to ([primary])
create table t ( i int not null,c char(2000)) on s(i)
declare @i int
set @i = 1
while @i <= 1000000
begin
insert into t select @i,replicate('a',2000)
set @i = @i + 1
end
create index ind1 on t(i)

Checking the view dm_db_index_operational_stats I see that sql
server scanned index on ALL partitions to check if data ok for the constraint
: field range_scan_count changed from 0 to 1 in all partitions,
page_lock_count also changed for all columns to be the number of actual pages
in each partition according to the dm_db_partition_stats view. i think it
should have only checked first partition to be sure that there is no data
less than 2.

select index_id,partition_number,range_scan_count,singleton_lookup_count,
row_lock_count,page_lock_count,index_lock_promotion_count
from sys.dm_db_index_operational_stats (db_id(),object_id('t'),null,null)

alter table t add constraint checkCons check(i>2)

select index_id,partition_number,range_scan_count,singleton_lookup_count,
row_lock_count,page_lock_count,index_lock_promotion_count
from sys.dm_db_index_operational_stats (db_id(),object_id('t'),null,null)

In the example below, optimizer should have only checked first partition to be sure that there is no data less than 2.

In order to explain how bad such behaivior is, adding such constraint on the
table with 900 partitions each one ~ million rows takes about 15 min. What will happen with table with 20 million rows in each partition?

Labels: , , , , , , , ,

0 Comments:

Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home