執行計畫快取的正確與否

回一位朋友的問題。

參照此篇:http://blogs.msdn.com/b/sqlprogrammability/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx,簡單測試如下:

use northwind
go
create table t(c1 int identity primary key,c2 nvarchar(10) default(N’Hello’),c3 uniqueidentifier default(newid()),c4 datetime2(7) default(sysdatetime()))
go
insert t(c2) values(‘Hello’)
go
insert t(c2) select c2 from t
go 20
insert t(c2) values(‘Hi’)
go
create index idx on t(c2)
go
create proc sp @c2 nvarchar(10)
as
    select * from t where c2=@c2
go

–以下是搭配執行後執行計畫的驗證

exec sp ‘Hi’
exec sp ‘Hello’  –依執行計畫快取執行

image

 

–依資料分佈平均值建立執行計畫

alter proc sp @c2 nvarchar(10)
as
    declare @var nvarchar(10)
    set @var=@c2
    select * from t where c2=@var
go
exec sp ‘Hi’
exec sp ‘Hello’

image

 

–依參數建議值建立執行計畫

alter proc sp @c2 nvarchar(10)
as
    select * from t where c2=@c2
    option(optimize for(@c2=’Hello’))
go
exec sp ‘Hi’
exec sp ‘Hello’

image

 

–SQL Server 2008 後依資料分佈建立執行計畫,與上述使用變數中介的結果相同

alter proc sp @c2 nvarchar(10)
as
    select * from t where c2=@c2
    option(optimize for(@c2 unknown))
go
exec sp ‘Hi’
exec sp ‘Hello’

image

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: