Contents

1 譴觜
2 螳 覩語讌 SQL Server, 伎襯 ?
3 Magic Density ろ
4 襷覓企Μ


1 譴觜 #

ろ碁ゼ 譴觜企.
if object_id('test') is not null
	drop table test;
go

with temp1(num)
as
(
        select 1 num
        union all
        select num + 1 from temp1
        where num + 1 <= 5000
),
temp2(num)
as
(
        select 1 num
        union all
        select num + 1 from temp2
        where num + 1 <= 600
)
select 
	identity(int,1,1) seq
,	convert(char(8), getdate() + b.num, 112) dt
,	left(newid(), 12) dumy1-- 一危磯ゼ 豈j鍵 
,	newid() dumy2-- 一危磯ゼ 豈j鍵 
into test
from temp1 a, temp2 b
option (maxrecursion 0);

create clustered index cix_seq
on test(seq);
--企 'test'. 蟆  1, 朱Μ 所鍵  19738

create index nix_dt
on test(dt);
--企 'test'. 蟆  1, 朱Μ 所鍵  20443

2 螳 覩語讌 SQL Server, 伎襯 ? #


螳 MSSQL Server 牛磯伎螳 覩語 讌 . 覦襦 れ螻 螳 . SQL 1, SQL 2 ろ螻螻 I/O襯 覲企.
SQL 1
--select min(dt), count(*) from test
--蟆郁骸: 20080112, 3000000

declare @dt char(8)
set @dt = '20080112'

select *
from test 
where dt <= @dt
--(5000 row(s) affected)
--企'test'. 蟆1, 朱Μ所鍵20444, 覓朱Μ所鍵0, 覩碁Μ所鍵0, LOB 朱Μ所鍵0, LOB 覓朱Μ所鍵0, LOB 覩碁Μ所鍵0.

magic_desity01.jpg

SQL 2
select *
from test 
where dt <= '20080112'
--(5000 row(s) affected)
--企'test'. 蟆1, 朱Μ所鍵15336, 覓朱Μ所鍵0, 覩碁Μ所鍵0, LOB 朱Μ所鍵0, LOB 覓朱Μ所鍵0, LOB 覩碁Μ所鍵0.

magic_desity02.jpg

蟆郁骸 螳讌襷 SQL 1 れ 朱, SQL 2 Index Seek襯 . 觜螻 麹 襷 るジ 蟆 . 蠏 伎 襦貉覲襯 蟆曙一 SQL 1 where 譟郁唄 @dt 蠏 伎. 螳 蟆一讌 牛磯伎螳 覦襯 讌 覈詩蠍 覓語企. 蠏碁 SQL Server 麹 企れ 蠍 覓語 SQL 1螻 螳 螳 覩誤[1] 襷り覲 伎 れ螻 螳 蠍一朱 企Π. (願姥 Magic Density手 . DBCC SHOW_STATISTICS 蟆郁骸 Index Density手 .)

譟郁唄覿
>, >=, <, <=30%
=10%
Between25%

蠍一朱 SQL 1 襯 螻壱 覲企 3000000 * 0.3 = 900000 朱 襷 伎. 企 蠍一 れ URL 誤 . ( 覓語 >, >=, <, <= 蟆曙磯 33%襦 .)

[http]INF: Search Arguments That Determine Distribution Page Usage(http://support.microsoft.com/kb/169642)

覓語 SQL Server 6.0, 6.5 覯 覓語 螳伎 . 蠏碁磯 讌蠍 2000, 2005 覯 ろ碁ゼ >, >=, <, <= 蟆曙磯 . れ 讌蠍 覯(2000, 2005) 襷蟆 襴企慨覃 れ螻 螳.

譟郁唄覿
>, >=, <, <=30%
=All Density[%]
Between9%
* All Density DBCC SHOW_STATISTICS 蟆郁骸 谿場 覲 .

3 Magic Density ろ #

襯 覲企 蟆讌襷 '=' 譟郁唄 蟆曙磯 譯殊襯 蠍一語 . 蠍郁 企(9%危 ′語 覯螳 Index Seek觜 Full Scan覲企 企) 蟆曙 れ ′語 覯螳 蟇 譬蟇 螳 Index Seek襯 覩襦 觜 貎朱Μ螳 蠍 覓語企. 蠏碁 =觜蟲 between ろ碁ゼ 企慨襦 蟆.

SQL 3
declare @dt char(8)
set @dt = '20080112'

select *
from test 
where dt = @dt
--(5000 row(s) affected)
--企'test'. 蟆1, 朱Μ所鍵15336, 覓朱Μ所鍵0, 覩碁Μ所鍵0, LOB 朱Μ所鍵0, LOB 覓朱Μ所鍵0, LOB 覩碁Μ所鍵0.

magic_desity03.jpg

SQL 4
--betweenろ碁ゼtest2 企襷り鍵
select distinct * into test2
from test

insert test2
select * from test2

insert test2
select * from test2

insert test2
select * from test2

insert test2
select * from test2
go

create index nix_dt
on test2(dt)
go

--ろ
declare @dt char(8)
declare @dt2 char(8)
set @dt = '20080113'
set @dt2 = '20080114'

select *
from test2
where dt between @dt and @dt2
--(32 row(s) affected)
--企'test2'. 蟆1, 朱Μ所鍵3, 覓朱Μ所鍵0, 覩碁Μ所鍵0, LOB 朱Μ所鍵0, LOB 覓朱Μ所鍵0, LOB 覩碁Μ所鍵0.

magic_desity04.jpg

SQL 5
--@dt2襯朱蟆覯襯朱覲伎
--觜 覦!!!!
declare @dt char(8)
declare @dt2 char(8)
set @dt = '20080113'
set @dt2 = '20300114'

select *
from test2
where dt between @dt and @dt2

--(9584 row(s) affected)
--企'test2'. 蟆1, 朱Μ所鍵31, 覓朱Μ所鍵0, 覩碁Μ所鍵0, LOB 朱Μ所鍵0, LOB 覓朱Μ所鍵0, LOB 覩碁Μ所鍵 0.
--れ朱Μ所鍵21
--select 862.56/9584*100 -- 9%
--@dt2 螳 覓企Μ 覲貅    覲讌 .

magic_desity05.jpg

SQL3 碁煙るゼ 襦 蟆 覲 . 襯 襦 谿 蟆 覲企 = 觜蟲 蟆曙磯 Index Desity襯 谿語^る 蟆 . 讀,

DBCC SHOW_STATISTICS ('dbo.test', 'nix_dt');
magic_desity06.jpg

0.001666667*3000000 = 5000.001000000 螳 螳 る 蟆企. 覈 讌 EQ_ROWS螳 5000企襦 語姶 れ螻 螳 一危磯ゼ れ 螻壱 覲願.

sQL 6
delete from test 
where seq <= 9000 and dt <= '20080113';
go

update statistics test 
with fullscan
go

DBCC SHOW_STATISTICS ('dbo.test', 'nix_dt')
go

magic_desity07.jpg

SQL 7
declare @dt char(8)
set @dt = '20080113'

select *
from test 
where dt = @dt

--select 0.001669449*2991000 
--4993.321959000


magic_desity08.jpg

SQL8 螳 碁煙るゼ 讌 襦 螳 4993.32 蟆 覲企 = 觜蟲 蟆曙磯 Index Desity襯 谿語^る 蟆 .

SQL 8
declare @dt char(8)
set @dt = '20080113'

select *
from test with (index = 0)
where dt = @dt
magic_desity09.jpg

4 襷覓企Μ #

襷讌襷朱 KB覓語([http]http://support.microsoft.com/kb/169642) 瑚 伎 . 覓語 覃 WHERE 譟郁唄 WHERE dt like @dt + % 螳 覃 Index Density襯 ろ螻 語 讌 り . 覃 襦貉 覲襯 Like 蟆 ろ 蟾讌 蠏 覿襯 蠍 覓語企. 蠏碁 企至 企 Magic Density襯 讌 蟆 ? 覘.. 螳. 襷り覲 る . SP襷り碓 EXEC() 螳 讌 k 蟆豌 襷り碓 sp_executesql 磯 . (企 伎 螻 一危 訖れ讌 蟇伎 覈襯願れ. 襷 螳瑚る 伎 . 企 譬 螻給 襷伎..)

ろ 蟾讌 覿襯 蟆 SP襯 襷谿螳讌. 覈詩覃 ろ螻 (Index Seek覲企 Full Scan 襴 一危 覿 蟆曙)朱 誤 SP螳 . 企 蟆 讀 襷り覲 る企手 . 谿 企 螳り 覿碁. 谿瑚襦 襦貉 覲襯 譟郁唄 WHERE dt like @dt + % 譟郁 ろ碁ゼ 企慨 0.031% 螳 螳 蟆 誤讌襷 譬 ろ碁ゼ 企 蟆 螳. like一一 企朱 >, <, >=, <= 襦 覦 ろ 企慨讌 . 讌 ろ 螻旧 谿場伎 覈詩.