sql server execution plan - 执行计划的诡异 （ 二 ）

1） 创建这次实验的表结构

create table dbo.fctOrderSales
( ordermonthkey int not null, orderdatekey int not null, orderkey int not null, 
customerkey int not null, currencykey int not null, partitionid int,
salesamount bigint, salesprofit numeric(9,2))
go 

alter table dbo.fctordersales
add constraint PK_FctOrderSales primary key clustered (ordermonthkey,orderdatekey,
orderkey,customerkey,currencykey)
go

create index IX_FctorderSales_Partitionid on dbo.FctOrderSales(partitionid)
go

2） 给表灌 6000 万条数据, 开 10 个 session, 来灌表

declare @partitionid int = 0 
declare @increaseNumber int = 1 + @partitionid * 6000000
declare @maxbucketnumber int =(@partitionid + 1)*6000000
declare @orderdatekey int = convert (int, convert(varchar,getdate(),112))
declare @ordermonthkey int = convert(int, left(convert(varchar,@orderdatekey),6))
declare @orderkey int = 1
declare @customerkey int = 1
declare @currencykey int  = 2

while ( @increasenumber <= @maxbucketnumber)
begin 
 insert into dbo.fctordersales(ordermonthkey,orderdatekey,orderkey,customerkey,currencykey,partitionid,
 salesamount,salesprofit) values(@ordermonthkey,@orderdatekey,@orderkey,@customerkey,@currencykey,
 @partitionid,@increasenumber,@increasenumber)
 set @orderdatekey = convert (int, convert(varchar,dateadd(dd,@increasenumber,getdate()),112))
 set @ordermonthkey = convert(int, left(convert(varchar,@orderdatekey),6))
 set @increasenumber = @increasenumber + 1
 set @orderkey = @increasenumber
end

3） 灌表的时候顺便查下 table 与 index 的同步情况

select * from dbo.fctordersales with(nolock) 
where partitionid = 1 

---------
41674 rows

select count(1) from dbo.fctordersales with(nolock)
where partitionid = 1 
group by partitionid 

--------
48963 

两者的 execution plan 分别是

不同的执行计划带来的数据集不同步的情况。 在这里，的确是 index 先于 table insert 了记录，至于为什么会有如此差异，得看看 table, index 的创建方式。 下回分解。那么删除时候， table, index 各自的更新情况呢？