为什么在sql server 2008中使用非聚集索引时增加估计的子树成本？ [英] Why did increase Estimated subtree cost when using non clustered index in sql server 2008?

问题描述

我有六行的下表

I have following table with six rows

CREATE TABLE TimePeriod
(
        TimePeriodId int not null identity(1,1) primary key,
        TimePeriodName nvarchar(30) not null,
        OrderId int not null,
        sDelete bit not null default 'False'
)

当我运行此查询时

When I run this query

SELECT * FROM TimePeriod where timeperiodname='quarter'

然后预计子树成本为0.0032886


但是当在TimePeriodName列上定义非聚集索引时

Then Estimated subtree cost comes 0.0032886

But when defined non clustered index on column TimePeriodName

CREATE UNIQUE NONCLUSTERED INDEX IX_TimePeriod_TimePeriodName
ON TimePeriod
(
        TimePeriodName ASC
) 
 WITH (PAD_INDEX  = OFF,
  STATISTICS_NORECOMPUTE  = OFF, 
  SORT_IN_TEMPDB = OFF, 
  IGNORE_DUP_KEY = OFF,
  DROP_EXISTING = OFF, 
  ONLINE = OFF, 
  ALLOW_ROW_LOCKS  = ON, 
  ALLOW_PAGE_LOCKS  = ON) 

然后运行查询

Then run query

SELECT * FROM TimePeriod where timeperiodname='quarter'

我得到估计子树成本是0.0065704



那么为什么非成本增加聚集索引是在表上定义的吗？

我在做错误的地方e？

I get Estimated subtree cost is 0.0065704

So why is cost incresing when non clustered index is defined on the table?
Where I am doing mistake?

推荐答案

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c1d30080-db97-4b5b-a712-505eaf6d4836 [ ^ ]



希望它给出你的答案



数据库性能优化第1部分（索引策略） [ ^ ]



这将给出你对它有一个很好的解释..希望它会有所帮助..

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c1d30080-db97-4b5b-a712-505eaf6d4836[^]

Hope its gives your answer

Database performance optimization part 1 (Indexing strategies)[^]

and this will give you a nice explanation on it..hope it will help..

这篇关于为什么在sql server 2008中使用非聚集索引时增加估计的子树成本？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！