RiCo技術農場

統計資料是描述索引鍵值分布訊息，SQL Server 可以針對索引或是資料表某欄位來建立，

而查詢最佳化程式透過統計資料才知道要使用那些運算子較為合理，

同時也會估計基數並建立最佳執行計畫，例如透過所估計基數選擇索引搜尋運算子，

而非需要大量資源的索引掃描運算子。

/*
RiCo 技術農場
https://dotblogs.com.tw/ricochen

資料庫建議啟用自動建立和更新統計資料
*/
use master
go
--auto_create_statistics on and auto_update_statistics on
create database StatisticsON

alter database StatisticsON set auto_create_statistics on with no_wait
alter database StatisticsON set auto_update_statistics on with no_wait

use StatisticsON
go

create table TblON
(
c1 int
,c2 varchar(50)
,c3 varchar(50)
)

create index idx1 on TblON(c1)
create index idx2 on TblON(c2)

insert into dbo.TblON
select top 100000 ROW_NUMBER() over(order by a.name) as c1,
'rico'+ cast(ROW_NUMBER() over(order by a.name)%2000 as varchar(50)) as c2,
'sherry'+ cast(ROW_NUMBER() over(order by a.name)%100 as varchar(50)) as c3
from sys.all_objects a
cross join 
sys.all_objects b

--auto_create_statistics off and auto_update_statistics off
use master
go
create database StatisticsOFF

alter database StatisticsOFF set auto_create_statistics off with no_wait
alter database StatisticsOFF set auto_update_statistics off with no_wait

use StatisticsOFF
go
create table TblOFF
(
c1 int
,c2 varchar(50)
,c3 varchar(50)
)
create index idx1 on TblOFF(c1)
create index idx2 on TblOFF(c2)

insert into dbo.TblOFF
select top 100000 ROW_NUMBER() over(order by a.name) as c1,
'rico'+ cast(ROW_NUMBER() over(order by a.name)%2000 as varchar(50)) as c2,
'sherry'+ cast(ROW_NUMBER() over(order by a.name)%100 as varchar(50)) as c3
from sys.all_objects a
cross join 
sys.all_objects b

use master
go

set statistics io,time on
select * from StatisticsON.dbo.TblON where c2='rico1'
select * from StatisticsOFF.dbo.TblOFF where c2='rico1'

drop database StatisticsON
drop database StatisticsOFF

 

參考

如何寫出高效能 TSQL – 探索統計值