????? ??????? ????????? (autostats) ?? SQL Server

?????? ????????? ?????? ?????????
???? ???????: 195565 - ??? ???????? ???? ????? ????? ??? ???????.
????? ???? | ?? ????

?? ??? ??????

??????

?? ????? ??????? ????????? ????? ?????? AutoStat ? ????? ??? ??? ??????? ???? ??? ???? ????? ?????? ??? ????????? ???????:
  • ??? ????????? ????????? ????? ????? ??????? ????.

    - ?? -
  • ??? ??? ????? excessively ???????? UPDATE ???????? ??? ???? ????? ?? ?????.
????? ?? ??? ??????? ?? ???????? ?????? ???? ????? ???? ??????? autostats ????? ???? ???????? UPDATE ????? ????? ??????? ?? ????? ??????.

?????? ??? ??????? ??? autostats ?? SQL Server 2000 "???????? ?????? ?????? ?????? ????????? ?? Microsoft SQL Server 2000" ?? ???? MSDN ??????:
http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx


?????? ?? ???? ??????? Microsoft SQL Server 2005 ???? ????? ?????? Microsoft ??????? ????? ?? ????????? ??? ????? ??????? ???????? ?????? ?????? ????????? ?? SQL Server 2005:
http://technet.microsoft.com/en-us/library/cc966419.aspx

??????? ????

??????? ????

SQL Server ??????? ???? ?? ?????? ???????? ??? ??????? ???? ???? ?? ???? ?????? ?????? ????????? ????????? ???? ??? ??????? ??? ??????? ????????. ???? ????????? ????????? ????? ?????? ? ???? ?? ????? SQL Server ?????? ??? ??????? ???? ??????? ????.

???????? ??? ?? ???? ?? SQL Server ???????? ?? ?????? ?? ???????? ???????? ??? ????? ????? ?????:
  • ??? ?????? ?? ??????.
  • ??? ??????? ????????? ?? ??? ??????.
  • ??? ????????? ???? ????? ??? ?????? ?????? ??? ??? ????? ??? ??????????.
??? ????? ??????? ?????? ?? ??????? ??? ?? ??? (?? ??? ?? ????):
  • ?????? ??????? ?????? ??? ?????? ?????.
  • densities ??? ???? ???????? ??????.
  • ????? ??? ???????.
??? ????? ???????? ??? ????? ???????? ???? ?? ????? ???? ????. ???????? ??? ???? ?? ?????? ???? ?????? ????????? ???????? ??? ??????? ?????? ?????.

???????? ??? ????????? ????????? ?????? ??? ????? ??? ??????? SQL Server ???? AutoStat ? ? ???? ?????? SQL Server ????????? ?????? ??? ???? ??? ????? ???????? ???? ???????? ??? ?????? ??? ?? ????? ????. ???????? ??? ???? ???? SQL Server ?????? - ????? - ???????? ? ???? ???? ?????? ?????? ???????? ???? ???????? ?? ??? ????? ??? ??????? ???? ????????.

????? ????? ??? ????? AutoStat imminent

??? ???????? ?????, ????? AutoStat ???????? ?????? ???????? ???? ???? ??? ?????? ??? "?? ?????". ????? ?????? sysindexes.rowmodctr ?????? ?????? ???? ????????? ??? ???? ????? ????? ????? ??? ????? ????? ???? ????? ?????????. ??? ????? ??? ?????? ?? ??? ???? ?? ?? ??????? ???????:
  • ?? ????? ?? ????.
  • ?? ??? ?? ????.
  • ??? ????? ????? ???? ?????.
??????: ?????? ?? ??? ????? rowmodctr TABLE.

??? ????? ?????? ?????????? ????? ???? rowmodctr ??? 0 ?? ????? ????? ?????? ???????? ??????.

???? ?? ? ?? ??????? ???? ??? ??? ??? ????? ??????? ?????? ?? ????? ??????? ?????? ? ???? ??? ????? ???????? ????? ?????? ????? ?????? ?????????? ??? ??????? ??????. ??? ??? ???? ???????? ????? ??????, ???? recompiled ??? ??????? ??????.

???????? ???????? ???????? ??????? ????????:
  • ??? ?? ??????? ???????? ????? ??? ?? ??? ???? ?????? ?? ????? ?????? tempdp ?????? ??????? ?? ?? ??? ??????? ??? ??????.
  • ??? ???? ??????? ???????? ????? ???? ?? 6 ???? ??? ?? ?? ????? 500 ? ????? ?????? ?? ????????? 500.
  • ??? ??? ??????? ???????? ?? ???? ???? ?? 500 ? ????? ?????????? ??? (500 + 20 ??????? ?? ??????) ???? ?????????.
  • ????????? ?????? ????????? ??????? ???????? ?? ???? ?????? ???????? ??????? ????????.
??????: ?? ??? ??????? strictest SQL Server ???? ??????? ???????? ?? ??? ?????? ?? ??????.

??????: ???????? ??? ??????? ???????? ? ???? selectivity ???? ??????? ????? ??? ????? AutoStats. ???? ???? ??? ?????????? ?? ?? ???? afer ??????? ?? ????????? 500 ??? ???? ??????? ???????? < 500 ?? ?? 20 % ?? ????????? ??? ???? ??????? ???????? > 500. ??? ????? ????? ????? ???? (?????? ????? ?? 1 ??? 4 "?" 1 "?" 4 ?????) ???????? selectivity ?????? ???? ??? ?????? ???? ????????? ??? ?? ?????? ????? ?? ?????????? ????? ?????? ????????? ???????? ?? ??? ????? AutoStats.

???? ?? ???? summarised ???????? ????? ?? ????? ????:
_________________________________________________________________________________
 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)
___________________________________________________________________________
Table
Variables   | Change in cardinality does not affect AutoStats generation.
___________________________________________________________________________
?????? ????? ????? ???????? ??? ??? ??? ???????:

???? 1

?? ???? ???????? ???? ??????? ?? ????? ?????? pubs (????????) ???? ????? ??? ???? 23 ????? ??? ????? ?????. ???? ?????? ???????? ???? ? UPKCL_auidind, ??? ????? ??? ???? ???? au_id ? ?? ????? ???? nonclustered ???? ? aunmind ? ??? au_lname ?????? au_fname. ??????? ?????? ??? ???? ??? ?? 500 ???? AutoStat ??? ???? ??????? 500 ??? ?????? ????. ???? ?? ???? ????????? ??? 500 ?? ???? ????? ?? ??? ?? ????? ??? ???? ????? ??? au_lname ?? ?? ?????? ???.

???? ? ?????? ?????? ??? ??? ??? ??? UPDATE ???????? ??????? ???? sysindexes.rowmodctr ???? ?????? ?? ??? ?? ?????. ??? ???? ?? ?????? 500, ????? ???? ???????? UPDATE ????.

???? 2

?? ???? ???????? ???? ???? t2 ?? ????? ?????? 1.000. ??????? ?? ???? ?? 500 ?????? ????? SQL Server ???????? UPDATE ??? (500 + 20 ???????) ????? ?????????. ????? ?? ????????? ?? 20 ??????? 1.000 ?? 200 ? ???? ????? ???? ??????? AutoStat ??? ??? ????? ??????? ????? 700 ??? ??????.

????? Autostats ???????

?????? ????? ??? ?????? AutoStat ???? ????????? ?????? sysindexes ?????? ??? ????????? ???? ??? ?????? ??? ???? ???????. ?????? ???????? ?????? ?????? ????:
   if (sysindexes.rows > 500)
      if (sysindexes.rows * 0.20 >= sysindexes.rowmodctr && production
      hours) //500 change leeway
         begin
            disable autostats
            log autostats disable
         end
      else
         begin
            stats ok
         end
   else
      if (sysindexes.rowmodctr >= 425) //75 change leeway
         begin
            disable autostats
            log autostats disable
         end
				

?????? ????? ????? ???? ?????? ?? ???:
  • ?????? UPDATE ???????? ???? ??????? ???? ?? ??? ?????? ???? ?????.

    - ? -
  • ????? ????? AutoStat ? ??? ?????? ??????? ?? ???? ???? ??? ????? ????? ??? 0 ??? ????? ???????? UPDATE.

?????? ?? ?? ??? ??? ??? ????? ???????? UPDATE ????? ????

???? ?????? ??? ??? ?????? ????? ????? proven AutoStat ?? Problematic ? ????? ????? ????? ?????? ????? ???? ??????? ????? ???????? ????? ?????? ???????? UPDATE ????? ????? intrusive ???. ????? ?????? ???? ??? ???????? ????? UPDATE ???????? ?? ????? sp_autostats ?????. ?? ???? ???? ??????? UPDATE ????????:
   UPDATE STATISTICS <table>...with NORECOMPUTE
				

?? ???? ???? ??????? sp_autostats ?????:
sp_autostats <table_name> ? <stats_flag> ? <index_name>
??? <stats_flag> "???" ?? "off".

????? ????? ??????? sp_dboption ?????? ???????? ????? ???????? UPDATE ?? CREATE ???????? ??? ????? ?? ????? ??????:
sp_dboption <dbname>? '???????? ??????? ????????' ? < ??? | ????? >

- ?? -

sp_dboption <dbname>? '?????? ????? ????????' ? < ??? | ????? >

?????? ?? ??? ???????? ????????? ???????? UPDATE

??????? ??? ???? ????? AutoStat ????? ????? ??? ?? ?????? ?????? ??? ?????? UPDATE ?????????? ???????? ???? ??? ???? ??????? ???? ?????? (??? ?? ????? DCR 51539 ???). ??? ???? ??? ?????? ????? ??? ?????? UPDATE ???????? ????????? ??? 4 ??? ?????.

????? ??? ?????? Autostats

????? ??????? ????? ???? 205 ????? ????? ??? ???? recompiled ????? ???? ???????? ??????? ?????? AutoStat. ??? ??? ??????? ?????? ????? ??????? ??????? ??? ??? ???????:
????? ????? spid9 11:10:51.98 1998-10-15: ProcName: sp_helpindex
StmtNo LineNo: 75: 29
??? ????? ???? ??????? 205 ???? ????? bracket ??????? ??????? ????? AutoStat ?? 8721 ??? ????? ??????????. ???? ?? ??? ????? ??? ??????? ?? ????? ???? ??? ?????? RowModCnt ????? ???? ?? 0. ??? ??????? ??? ???????? UPDATE ????? RowModCnt ???? 0:
????? ???? spid8 11:38:43.68 1998-10-15: Tbl Dbid: Objid 7:
RowModCnt 133575514: RowModLimit 60500: 60499
??? ??????? "RowModCnt" ?? ????? ???????? ??????? ??? ??????. "RowModLimit" ?? ?????? ???? ??? ????? ???? ????? ????? UPDATE ?????????? ???? ??????.

?? ?????? ????? ????? ????? ???? 8721 ? ????? ????? ??????? ??? ??? ??? ??? ????? AutoStat. ?????? ?? ???? ?? ??? ??????? ???? ????? ????:
16:22:13.21 1998-10-14 AUTOSTATS spid13: ????? Tbl: [???????]
Mods ??????: 23: ???? ???: ????? 500: UpdCount 47ms: 2
??? ??????? "Mods" ?? ????? ???????? ??????? ??? ??????. ???? ??????? ?? "????" ?? "?????" ??? ????? ????? ??????? ????? UPDATE ???????? ?????? ?? ?? "UpdCount" ?? ????? ??????????.

????? ????? ??????? ???? ????? ??????? ?? SQL Server ?????? ??? ????? ?????? UPDATE ??????????. ?????? ????? ?? ?????? ??????? ???????:
  1. ?? ??????? ???? ????? ??????? ? ???? ??? ????? ?? ???? ??? ??????.
  2. ?? ??????? ??? ? ????? ??? ????? ?? ?? ?????? ???? ???? ???????.
  3. ????? ????? ????? ???? ??? ????? ? ??? ?????? ??????, ??? sub-event ???????? ??????? ????????.
??????: ??? ??? ??????? ???????? ?????? ?? ??? AutoStat ? ??? ???? ?? ??????? ???? ??????? ??? ??? ???????. ?????? ?????? ??? ???????? ?????? ??? ??????? ?? ??????? ?? ???? ???? ??????.

????? ??????

?????? SQL Server ????? ?? ????? ???? ?? ????? ??? ??? ?? ???? ???? ?????? ???????? ????:
   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.
				
????? ??? ??? ??????? ?? ???? ????? sp_lock ?? ?? ???? ??????? ?? ?????? syslockinfo.

???????

???? ???????: 195565 - ????? ??? ??????: 21/????/1428 - ??????: 7.6
????? ???
  • 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
  • Microsoft SQL Server 2000 Desktop Engine (Windows)
  • Microsoft SQL Server 2000 Enterprise Edition 64-bit
????? ??????: 
kbmt kbinfo KB195565 KbMtar
????? ????
???: ??? ????? ??? ?????? ???????? ?????? ????? ???? ????? ?????????? ????? ?? ????????? ?????? ????. ???? ???? ?????????? ???? ?? ???????? ???????? ?????? ????????? ????? ????????? ???????? ????? ???????? ?????? ?? ?????? ??? ?? ???????? ???????? ?? ????? ??????? ?????? ??? ??????? ?????? ??. ?????? ?? ???? ??? ??????? ???????? ????? ?? ???? ????? ?????? ??? ????? ??? ????? ??????? ?? ????? ?? ?????? ??? ??? ??????? ??????? ?? ????? ????? ????? ????? ?????. ?? ????? ???? ?????????? ??????? ??? ????? ?? ??????? ?? ????? ?????? ?? ??? ????? ?? ????? ??????? ?? ???????? ?? ??? ???????. ???? ???? ?????????? ???????? ??? ????? ?????? ??????? ??????
???? ??? ????? ??????? ?????? ??????????195565

????? ???????

 

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