Lm th? no ?: S? d?ng KEEPFIXED k? ho?ch ? v hi?u ha th? t?c ?c lu tr? Recompilations

D?ch tiu ? D?ch tiu ?
ID c?a bi: 276220 - Xem s?n ph?m m bi ny p d?ng vo.
Bung t?t c? | Thu g?n t?t c?

? Trang ny


Trong m?t s? t?nh hu?ng ni th? t?c ?c lu tr? ?c bin, chi ph ?a c th? l?n hn l?i ch m c ngu?n g?c t? khi lm i?u . Lu ? r?ng b?i v? SQL Server Service Pack 2 (SP2) v tr?c khng h? tr? tuyn b? c?p ?a, cc th? t?c ?c lu tr? ton b? ph?i ?c bin khi m?t recompile ?c kch ho?t. V? v?y, s? l?ng recompiles ang ?c kch ho?t trong th?i gian th?c hi?n m?t th? t?c ?c lu tr? v chi?u di c?a cc th? t?c ?c lu tr? i khi c th? tng trong th?i gian t?ng th? th?c hi?n th? t?c ?c lu tr?.

B?t ?u t? trong SQL Server 7.0 SP3, m?t g?i ? truy v?n m?i, K? HO?CH KEEPFIXED, ? ?c gi?i thi?u ? gip trong cc t?nh hu?ng ni m chi ph ?a l nhi?u hn chi ph c?a vi?c s? d?ng hi?n c k? ho?ch.

Cc K? HO?CH KEEPFIXED truy v?n g?i ? l?c l?ng t?i u ha truy v?n ? khng bao gi? c th? bin d?ch m?t truy v?n v? c?a nh?ng thay ?i trong s? li?u th?ng k ho?c thay ?i l?p ch? m?c c?t (v d? nh, C?p Nh?t, xa ho?c chn). ? bi?t thm chi ti?t v? lm th? no ? g? r?i cc th? t?c ?c lu tr? ?a, nh?p vo s? bi vi?t sau ? xem bi vi?t trong c s? ki?n th?c Microsoft:
243586INF: X? l? s? c? th? t?c ?c lu tr? ?a

M? m?u s? d?ng KEEPFIXED k? ho?ch G?i ?

M?u m? trong ph?n ny l t? bi vi?t c s? ki?n th?c Microsoft sau:
243586 INF: X? l? s? c? th? t?c ?c lu tr? ?a
   drop procedure RowModifications 
   create procedure RowModifications as
   -- assume SomeTable exists with the same definition as #t, 
   -- and has over 1000 rows
   create table  #t (a int )
   select * from  #t
   insert  #t select * from retest
   select count(*) from #t  where a = 37
   --option (keepfixed plan)
   exec RowModifications
   exec RowModifications
? th?c hi?n th? hai c?a cc RowModifications th? t?c, o?n m? sau nguyn nhn l ?a:
  select count(*) from #t where a = 37
N?u b?n s? d?ng m? ny:
Option (keepfixed plan)
Trong truy v?n ny, truy v?n khng gy ra m?t l?n n?a ?a:
select count(*) from #t  where a = 37 option (keepfixed plan) 
Chu y G?i ? truy v?n ny ?c p d?ng trn c?p ? tuyn b? v khng ?nh h?ng ?n ph?m vi c?a cc th? t?c ?c lu tr? ton b?. N?u b?n mu?n c ty ch?n ny ?nh h?ng ?n nhi?u pht bi?u trong m?t th? t?c ?c lu tr?, t?ng tuyn b? ph?i th?c hi?n nh?ng g?i ? SQL Server p d?ng cho m?t hnh vi. Ny g?i ? l khng c s?n cho SQL Server 7.0 Service Pack 2 (SP2) ho?c tr?c .

Thu?c tnh

ID c?a bi: 276220 - L?n xem xt sau cng: 01 Thang Chin 2011 - Xem xt l?i: 3.0
p d?ng
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
T? kha:
kbhowtomaster kbinfo kbmt KB276220 KbMtvi
My d?ch
QUAN TRONG: Bi vi?t ny ?c d?ch b?ng ph?n m?m d?ch my c?a Microsoft ch? khng ph?i do con ng?i d?ch. Microsoft cung c?p cc bi vi?t do con ng?i d?ch v c? cc bi vi?t do my d?ch ? b?n c th? truy c?p vo t?t c? cc bi vi?t trong C s? Ki?n th?c c?a chng ti b?ng ngn ng? c?a b?n. Tuy nhin, bi vi?t do my d?ch khng ph?i lc no c?ng hon h?o. Lo?i bi vi?t ny c th? ch?a cc sai st v? t? v?ng, c php ho?c ng? php, gi?ng nh m?t ng?i n?c ngoi c th? m?c sai st khi ni ngn ng? c?a b?n. Microsoft khng ch?u trch nhi?m v? b?t k? s? thi?u chnh xc, sai st ho?c thi?t h?i no do vi?c d?ch sai n?i dung ho?c do ho?t ?ng s? d?ng c?a khch hng gy ra. Microsoft c?ng th?ng xuyn c?p nh?t ph?n m?m d?ch my ny.
Nh?p chu?t vo y ? xem b?n ti?ng Anh c?a bi vi?t ny:276220

Cung cp Phan hi


Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com