Th?ng k b?o tr? ch?c nng (autostats) trong SQL Server

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

? Trang ny


Cc ch?c nng m?i ?c gi?i thi?u th?ng k b?o tr?, AutoStat, c th? t?o ra khng mong mu?n trn cao trn m?t h? th?ng s?n xu?t b?ng cch th?c hi?n m?t trong cc hnh ?ng sau y:
  • B?t ?u C?p Nh?t th?ng k trong th?i k? n?ng s?n xu?t.

    - hay -
  • B?t ?u m?t s? qu m?c cao c?a cc qu tr?nh C?p Nh?t th?ng k t?i m?t i?m nh?t ?nh trong th?i gian.
M?c ch c?a bi vi?t ny l ? chi ti?t cc i?u ki?n theo b?n c th? mong ?i ? xem autostats ?c t?o ra v th?ng k C?p Nh?t ang ?c ch?y ch?ng l?i b?ng trong c s? d? li?u.

Thng tin v? autostats trong SQL Server 2000, xem "S? d?ng s? li?u th?ng k b?i tr?nh t?i u ho truy v?n trong Microsoft SQL Server 2000" t?i MSDN Web site sau: (SQL.80) .aspx

Chu y N?u b?n ang s? d?ng Microsoft SQL Server 2005, xem gi?y tr?ng Microsoft sau y ? bi?t thng tin v? lm th? no th?ng k ?c s? d?ng b?i tr?nh t?i u ho truy v?n SQL Server 2005:


Thng tin chung

SQL Server s? d?ng c?a ti u m?t d?a trn chi ph hoa c th? v cng nh?y c?m v?i thng tin th?ng k ?c cung c?p trn b?ng v ch? s?. M khng chnh xc v up-to-date thng tin th?ng k, SQL Server c th? ?c th? thch ? xc ?nh k? ho?ch th?c hi?n t?t nh?t c?a m?t c? th? truy v?n.

Th?ng k duy tr? trn m?i bn trong SQL Server ? h? tr? t?i u ha trong chi ph d?a trn quy?t ?nh th?c hi?n bao g?m cc:
  • S? l?ng cc hng trong b?ng.
  • S? l?ng cc trang ?c s? d?ng b?i b?ng.
  • S? c?i ti?n th?c hi?n ? cc phm c?a bn k? t? cu?i C?p nh?t cc s? li?u th?ng k.
Thng tin b? sung ?c lu tr? cho ch? s?, bao g?m c? (cho m?i ch? s?):
  • M?t bi?u ? equi-chi?u cao trn c?t ?u tin.
  • M?t ? trn t?t c? cc c?t ti?n t?.
  • Trung b?nh chi?u di chnh.
Th?ng k v? ch? s? ?c t? ?ng t?o ra b?t c? khi no m?t ch? s? m?i ?c xy d?ng. Ngoi ra, n l by gi? c th? ? t?o ra v duy tr? cc s? li?u th?ng k trn c?t khc l t?t.

? duy tr? cc thng tin th?ng k trong m?t th?i trang nh up-to-date c th?, SQL Server gi?i thi?u AutoStat, trong , thng qua my ch? SQL Gim st thay ?i trong b?ng, c kh? nng t? ?ng c?p nh?t cc th?ng k cho m?t b?ng khi m?t ng?ng thay ?i nh?t ?nh ? ?t t?i. Ngoi ra, SQL Server gi?i thi?u th?ng auto-t?o-k, m gy ra my ch? ? t? ?ng t?o ra t?t c? cc s? li?u th?ng k c?n thi?t cho cc chnh xc t?i u ha c?a m?t truy v?n c? th?.

Vi?c xc ?nh khi AutoStat th? h? l s?p x?y ra

Nh ? ? c?p ? trn, AutoStat s? t? ?ng c?p nh?t cc s? li?u th?ng k cho m?t b?ng c? th? khi m?t "ng?ng thay ?i" ? ?t t?i. Cc sysindexes.rowmodctr c?t duy tr? m?t ch?y t?ng c?a t?t c? cc s?a ?i m?t b?ng m, qua th?i gian, c th? ?nh h?ng ?n b? vi x? l? truy v?n quy?t ?nh lm cho qu tr?nh. S? l?t truy c?p ny ?c c?p nh?t m?i khi b?t k? c?a cc sau s? ki?n x?y ra:
  • Chn m?t n hng ?c th?c hi?n.
  • Xa n hng ?c th?c hi?n.
  • M?t C?p Nh?t cho m?t c?t ?c nh ch? m?c ?c th?c hi?n.
LU ?: TRUNCATE b?ng khng C?p Nh?t rowmodctr.

Sau khi b?ng s? li?u th?ng k ? ?c C?p Nh?t, gi tr? rowmodctr ?t l?i v? 0 v phin b?n ? b?ng s? li?u th?ng k ?c C?p Nh?t.

Hn n?a, trong nh?ng t?nh hu?ng m trong th?c hi?n m?t th? t?c ?c lu tr? k? ho?ch l L?y t? b? nh? cache v r?ng k? ho?ch l nh?y c?m v?i s? li?u th?ng k, th?ng k Phin b?n ? s? ?c so snh v?i phin b?n hi?n t?i. N?u khng c m?i th?ng k c s?n, k? ho?ch cho cc th? t?c ?c lu tr? s? ?c bin.

Thu?t ton c b?n ? t? ?ng C?p Nh?t s? li?u th?ng k l:
  • N?u cardinality cho m?t b?ng l t hn su v b?ng trong c s? d? li?u tempdb, t? ?ng C?p Nh?t v?i m?i s?a ?i su ?n b?ng.
  • N?u cardinality cho m?t b?ng l l?n hn 6, nhng t hn ho?c b?ng v?i 500, C?p nh?t t?nh tr?ng s?a ?i 500 m?i.
  • N?u cardinality cho m?t b?ng l l?n hn 500, C?p Nh?t th?ng k khi (500 + 20 ph?n trm c?a b?ng) thay ?i ? x?y ra.
  • ?i v?i b?ng bi?n, thay ?i cardinality khng kch ho?t t? ?ng C?p Nh?t s? li?u th?ng k.
LU ?: Trong ? ngh?a ch?t ch? nh?t ny, SQL Server ?m cardinality nh l s? hng trong b?ng.

LU ?: Thm vo cardinality, ch?n l?c predicate c?ng ?nh h?ng ?n th? h? AutoStats. i?u ny c ngh?a r?ng s? li?u th?ng k c th? khng C?p Nh?t afer m?i s?a ?i 500 n?u cardinality ? < 500="" or="" for="" every="" 20%="" of="" changes="" if="" cardinality="" were=""> 500. Quy m ln y?u t? (gi tr? kho?ng 1-4, 1 v 4 bao g?m) ?c t?o ra ty thu?c vo s? ch?n l?c v m?t s?n ph?m c?a y?u t? ny v m?t s? thay ?i nh thu ?c t? cc thu?t ton s? l s? c?i ti?n c?n thi?t cho th? h? AutoStats, th?c t?.

Cc thu?t ton ? trn c th? ?c tm t?t trong h?nh th?c m?t b?ng:
 Table Type | Empty Condition | Threshold When Empty |Threshold When Not Empty 
 Permanent  | < 500 rows      | # of Changes >= 500  | # of Changes >= 500 + (20% of Cardinality)
 Temporary  | < 6 rows        | # of Changes >= 6    | # of Changes >= 500 + (20% of Cardinality)
Variables   | Change in cardinality does not affect AutoStats generation.
Sau y l hai v d? ? gip ch?ng minh khi ni?m ny:

V d? 1

Xem xt tc gi? b?ng trong c s? d? li?u qun r?u, c ch?a cc hng 23 v c hai ch? s?. Cc ch? s? nhm ?c o, UPKCL_auidind, ?c l?p ch? m?c trn m?t c?t, au_id, v m?t ch? s? t?ng h?p c?a nonclustered, aunmind, ? thnh l?p ngy au_lname v au_fname c?t. B?i v? b?ng ny ch?a t hn 500 hng, AutoStat s? b?t ?u sau khi 500 thay ?i b?ng d? li?u ? x?y ra. Nh?ng thay ?i c th? l m?t trong 500 hay nhi?u Chen, xa, thay ?i ?i v?i m?t c?t ?c nh ch? m?c nh au_lname, ho?c b?t k? k?t h?p no .

B?n c th?, do , d? on khi s? li?u th?ng k C?p Nh?t s? ?c kh?i x?ng b?i Gim st gi tr? sysindexes.rowmodctr, m s? ?c incremented khi m?i l?n C?p Nh?t. Khi n ?t ?n ho?c v?t qu 500, b?n c th? mong ?i C?p Nh?t Th?ng k ph?i ?c b?t ?u.

V d? 2

Xem xt m?t b?ng th? hai, t2, c m?t cardinality 1000. Bn v?i hn 500 hng, SQL Server s? C?p Nh?t th?ng k khi (500 + 20 ph?n trm) thay ?i ? ?c th?c hi?n. Lm nh ton h?c, 20 ph?n trm c?a 1.000 l 200, do , b?n c th? mong ?i ? xem AutoStat b?t ?u sau khi kho?ng 700 S?a ?i ? ?c th?c hi?n ? bn.

T? ?ng ho Autostats quy?t tm

? t? ?ng xc ?nh khi AutoStat s? ?c ch?y, b?n c th? cu?c thm d? cc sysindexes b?ng v xc ?nh khi s?a ?i b?ng ti?p c?n cc i?m kh?i ?u. D?i y l m?t thu?t ton c b?n ? lm nh v?y:
   if (sysindexes.rows > 500)
      if (sysindexes.rows * 0.20 >= sysindexes.rowmodctr && production
      hours) //500 change leeway
            disable autostats
            log autostats disable
            stats ok
      if (sysindexes.rowmodctr >= 425) //75 change leeway
            disable autostats
            log autostats disable

Sau , b?n c th? l?ch tr?nh m?t cng vi?c ? lm nh sau:
  • Ch?y C?p Nh?t th?ng k ch?ng l?i t?t c? cc b?ng m b?n bu?c ph?i v hi?u ha chng trong ngy.

    - v -
  • Kch ho?t l?i AutoStat, b?i v? m?i b?ng s?a ?i s? l?t truy c?p s? ? ?c ?t thnh 0 khi C?p Nh?t s? li?u th?ng k ?c ch?y.

Vi?c ki?m sot li?u th?ng k UPDATE ang ch?y ch?ng l?i m?t b?ng

Gi?i php r? rng nh?t cho cu h?i ny, khi AutoStat ? ch?ng minh ?c Problematic, l ? v hi?u ho t? ?ng th? h? th?ng k, do ? l?i cc c s? d? li?u qu?n tr? vin mi?n ph ? l?ch tr?nh C?p Nh?t s? li?u th?ng k trong th?i gian t hn l?n xm nh?p. B?n c th? lm i?u ny ho?c b?ng cch s? d?ng s? li?u th?ng k C?p Nh?t tuyn b? ho?c cc sp_autostats lu tr? th? t?c. C php cho C?p Nh?t Th?ng k tuyn b? l:

C php cho cc th? t?c sp_autostats lu tr? l:
sp_autostats <table_name>, <stats_flag>, <index_name> </index_name></stats_flag></table_name>
ni <stats_flag> l m?t trong hai "" ho?c "t?t". </stats_flag>

B?n c?ng c th? s? d?ng sp_dboption ? v hi?u ha s? xu?t hi?n t? ?ng C?p Nh?t Th?ng k ho?c t?o ra s? li?u th?ng k trn m?t m?c ? cho m?i s? d? li?u:
sp_dboption <dbname>, 't? ?ng C?p Nh?t th?ng k', <on |="" off=""> <b00></b00></on></dbname>

- hay -

sp_dboption <dbname>, 't? ?ng t?o ra th?ng k', <on |="" off=""> </on></dbname>

Ki?m sot s? l?ng ?ng th?i C?p Nh?t th?ng k quy tr?nh

Hi?n nay, ng?n c?a v hi?u ha AutoStat cho cc b?ng c? th?, n khng ph?i l c th? c?u h?nh s? t? ?ng C?p Nh?t s? li?u th?ng k bo co m ang ?c ch?y ?ng th?i (DCR 51539 ? ?c ? tr?nh ny). Cc h? ph?c v?, tuy nhin, h?n ch? s? l?ng ?ng th?i C?p Nh?t th?ng k quy tr?nh ln 4 cho m?i b? vi x? l?.

Vi?c xc ?nh khi Autostats ang ?c i?u hnh

B?n c th? s? d?ng d?u v?t c? 205 bo khi m?t ph? thu?c vo s? li?u th?ng k lu tr? th? t?c ang ?c bin nh l k?t qu? c?a AutoStat. Ny c? water s? vi?t th sau y ? l?i ng nh?p:
1998-10-15 11:10:51.98 spid9 Recompile pht hnh: ProcName: sp_helpindex
LineNo: 75 StmtNo: 29
Khi d?u v?t c? 205 ?c kch ho?t, thng bo sau c?ng s? khung cc AutoStat tin nh?n t? 8721 khi s? li?u th?ng k ?c C?p Nh?t. Vi?c m? thng i?p c?a khung c th? ?c phn bi?t b?i gi tr? RowModCnt, m s? l?n hn 0. Khung cu?i cng, sau khi cc s? li?u th?ng k UPDATE, s? c m?t gi tr? RowModCnt 0:
1998-10-15 11:38:43.68 spid8 Schema thay ?i: Tbl Dbid: 7 Objid:
133575514 RowModCnt: 60500 RowModLimit: 60499
Th ny, "rowmodcnt" l t?ng s? s?a ?i ? cc b?ng. "rowmodlimit" l ng?ng m, khi v?t qu, k?t qu? trong m?t Th?ng k UPDATE tuyn b? th?c hi?n cho cc b?ng.

N c?ng c th? ? cho php d?u v?t c? 8721, m s? b?i ch?a thng tin vo l?i ng nh?p khi AutoStat ? ?c i?u hnh. Sau y l m?t V d? v? lo?i tin nh?n m b?n c th? th?y:
1998-10-14 16:22:13.21 spid13 AUTOSTATS: C?p Nh?t Tbl: [tc gi?]
Hng: 23 Mods: 501 Bound: 500 th?i gian th?c hi?n: 47ms UpdCount: 2
Th ny, "Mods" l t?ng s? s?a ?i ? bn. "Rng bu?c" ng?ng s?a ?i, "Th?i gian" l s? ti?n c?a th?i gian m tuyn b? th?ng k C?p Nh?t c?n thi?t ? hon thnh, v "updcount" l b t?c c?a th?ng k C?p Nh?t.

B?n c?ng c th? s? d?ng SQL Server Profiler ? xc ?nh khi C?p Nh?t s? li?u th?ng k l?i tuyn b? ang ?c ch?y. ? th?c hi?n vi?c ny, th?c hi?n theo cc b?c sau:
  1. Trn cc H? s tr?nh n, nh?p vo Cng cu, v sau nh?p vo Tuy chon.
  2. Trn cc T?ng qut tab, i ?n S? ki?n, v ch?n T?t c? cc l?p h?c t? ch?c s? ki?n.
  3. Xc ?nh m?t d?u v?t m?i, v d?i S? ki?n, ch?n Misc, ch?n cc Th?ng k c?p nh?t t? ?ng Sub-event.
Lu ?: N?u s? li?u th?ng k nhi?u ang ?c C?p Nh?t b?i AutoStat, m?t s? l?ng l?n tin nh?n c th? ?c ghi vo Nh?t k? l?i. Tri?t ? th? nghi?m v?i cc water c? tr?c khi dng n trn b?t k? s?n xu?t ho?c n?u khng quan tr?ng h? ph?c v?.

Gi?n ? ? kha

SQL Server s? d?ng hai lo?i ? kha gi?n ?, c? hai ?u ?c th?c hi?n khi n C?p nh?t cc s? li?u th?ng k cho m?t b?ng:
   Sch-S: Schema Stability Lock
   This lock ensures that a schema element, such as a table or index, will
   not be dropped while any session holds a schema stability lock on the
   schema element.

   Sch-M-UPD-STATS: Schema Modification Lock
   This is a non-blocking lock that is used by the system to ensure that
   only one automatic UPDATE STATISTICS process is run against a table at
   any given point in time. The sp_lock stored procedure will report this
   lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M.
B?n c th? xem cc ? kha b?ng cch ch?y sp_lock ho?c b?ng cch ch?n t? cc syslockinfo b?ng.

Thu?c tnh

ID c?a bi: 195565 - L?n xem xt sau cng: 20 Thang Tam 2011 - Xem xt l?i: 2.0
p d?ng
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Workgroup Edition
T? kha:
kbinfo kbmt KB195565 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:195565

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