통계 업데이트 및 인덱스 조각화 상태 조회

/*      --------------------------------------------------

        통계 정보 업데이트

--------------------------------------------------   */

SELECT  'USE [' + NAME + ']; EXEC SP_UPDATESTATS;'

FROM    SYS.DATABASES

WHERE   DATABASE_ID > 4

GO

 

/*      --------------------------------------------------

        인덱스 조각화 상태 조회 및 리빌드

--------------------------------------------------   */

SELECT  [ALTER]                = 'ALTER INDEX'

,       INDEX_NAME             = B.NAME

,       [ON]                   = 'ON'

,       TABLE_SCHEMA   = C.TABLE_SCHEMA + '.' + OBJECT_NAME(A.OBJECT_ID)

,       [REBUILD]              = 'REBUILD'

,       [OPTION]               = 'WITH ( SORT_IN_TEMPDB = ON );'

,       FRAG                   = A.AVG_FRAGMENTATION_IN_PERCENT

,       PAGES                  = A.PAGE_COUNT

FROM    SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(), NULL, NULL, NULL, NULL) AS A

JOIN    SYS.INDEXES AS B ON (A.OBJECT_ID = B.OBJECT_ID) AND B.INDEX_ID = A.INDEX_ID

JOIN    INFORMATION_SCHEMA.TABLES AS C ON (C.TABLE_NAME = OBJECT_NAME(A.OBJECT_ID)) AND A.DATABASE_ID = DB_ID() AND OBJECTPROPERTY(B.OBJECT_ID, 'ISSYSTEMTABLE')=0

WHERE   A.INDEX_ID > 0

AND     A.PAGE_COUNT > 100

AND     A.AVG_FRAGMENTATION_IN_PERCENT > 10.00