SQL Server??????

???????????? ???????[ 2013/4/10 9:57:43 ] ????????

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

???????????????ν???е?????????????????????????????????????????????????????????????????????????????????????

??????????е???????????DML??????insert??update??delete???????????????????Щ??????????????????????????????????????????????

????????????У??????????????С???????????????????????????????????????????DML???????

??????2000?????SQLServer???????????????????sysindexes?е?RowModCtr??Row Modification Counter????????????????o???????????0???????????????????????????????????????????????

??????2000???SQLServer????????????????????????????????????????????δ??????ColModCtr??

????????sys.sysindexes??2012??????????????????????????????????????

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

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

????1??sys.sysindexes???????????????RowModCtr???????????????

????2??sys.indexes??????ID???????????????

????3??sys.objects????????????

???????裺

???????RowModCtr?????????????

SELECT DISTINCT
        OBJECT_NAME(SI.object_id) AS Table_Name ??
        SI.name AS Statistics_Name ??
        STATS_DATE(SI.object_id?? SI.index_id) AS Last_Stat_Update_Date ??
        SSI.rowmodctr AS RowModCTR ??
        SP.rows AS Total_Rows_In_Table ??
        'UPDATE STATISTICS [' + SCHEMA_NAME(SO.schema_id) + '].['
        + OBJECT_NAME(SI.object_id) + ']' + SPACE(2) + SI.name AS Update_Stats_Script
FROM    sys.indexes AS SI( NOLOCK )
        INNER JOIN sys.objects AS SO( NOLOCK ) ON SI.object_id = SO.object_id
        INNER JOIN sys.sysindexes SSI( NOLOCK ) ON SI.object_id = SSI.id
                                                    AND SI.index_id = SSI.indid
        INNER JOIN sys.partitions AS SP ON SI.object_id = SP.object_id
WHERE   SSI.rowmodctr > 0
        AND STATS_DATE(SI.object_id?? SI.index_id) IS NOT NULL
        AND SO.type = 'U'
ORDER BY 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?????????????????????????????????????

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