SQL Server研究之統計資訊—發現到期統計資訊並處理詳解

標籤：sqlserver

前言：

        統計資訊是關於謂詞中的資料分布的主要資訊源，如果不知道具體的資料分布，最佳化器不能獲得預估的資料集，從而不能統計需要返回的資料。

        在建立列的統計資訊後，在DML操作如insert、update、delete後，統計資訊就會過時。因為這些操作更改了資料，影響了資料分布。此時需要更新統計資料。

        在高活動的表中，統計資訊可能幾個小時就會過時。對於靜態表，可能幾個星期才會過時。這要視乎表上DML的操作。

        從2000開始，SQLServer對增刪改操作會增加在表sysindexes中的RowModCtr（Row Modification Counter）值，當統計資訊更新後，該值會重設會0，並重新累加。所以查看這個表的這個值就可以知道統計資訊是否過時。上章研究了非索引鍵上統計資訊的影響詳解不明白的可以去看下。

        在2000之後，SQLServer改變了這種跟蹤方式，把更改存放到對應的資料行上。這個值是未公開的ColModCtr。

        但是sys.sysindexes到2012依舊可用，還是可以用這個表的數值來確定是否到期。

準備工作：

本文將用到下面的系統檢視表和相容性檢視：

1、  sys.sysindexes：相容性檢視，提供RowModCtr列值，是本文的核心。

2、  sys.indexes：使用表ID來獲得統計資訊名。

3、  sys.objects：擷取架構名。

步驟：

顯示RowModCtr值很高的統計資訊：

123456789101112131415161718192021222324252627282930313233

SELECTDISTINCT         OBJECT_NAME(SI.object_id)AS Table_Name,         SI.nameAS Statistics_Name,         STATS_DATE(SI.object_id,SI.index_id)AS Last_Stat_Update_Date,         SSI.rowmodctrAS RowModCTR,         SP.rowsAS Total_Rows_In_Table,         ‘UPDATE STATISTICS [‘+ SCHEMA_NAME(SO.schema_id)+ ‘].[‘         +OBJECT_NAME(SI.object_id)+ ‘]‘+ SPACE(2)+ SI.nameAS Update_Stats_Script FROM    sys.indexesAS SI(NOLOCK )         INNERJOIN sys.objectsAS SO(NOLOCK )ON SI.object_id= SO.object_id         INNERJOIN sys.sysindexesSSI(NOLOCK )ON SI.object_id= SSI.id                                                     ANDSI.index_id= SSI.indid         INNERJOIN sys.partitionsAS SPON SI.object_id= SP.object_id WHERE  SSI.rowmodctr> 0         ANDSTATS_DATE(SI.object_id,SI.index_id)IS NOTNULL         ANDSO.type= ‘U‘ ORDERBY RowModCTR DESC

分析：

需要瞭解一些事情：

1、  從你上次更新統計資料是何時的事情？

2、  在更新統計資料之後有多少事務發生在表上？

3、  哪些T-SQL需要用於更新統計資料。

4、  更新統計資料是否可行？這個是對比RowModCTR列和Total_Rows_In_Table列。

當在資料庫開啟了Auto_Update_Statistics之後，還有資料的話，那就有必要更新統計資料。下面有一些規則：

1、  表大小從0增長。

2、  當表的資料小於等於500時沒有問題，並且ColModCtr從超過500行之後開始增長。

3、  當表的行數超過500行時，在統計資訊對象的引導列的ColModCtr值超過500+20%的行數時，就需要更新。

例子：有一個100萬行的表，最佳化器會在插入200500行新資料後認為統計資訊過時。但是這並不是絕對化的。

擴充知識：

沒有直接的方式訪問ColModCtr的值，因為它只是用於最佳化引起，並且對使用者透明，但是可以使用DAC(專用管理員連接)來訪問sys.sysrscols.rcmodified系統。但是僅在2008R2及以後版本才可用。