??λ??SQL Server??????????

???????????? ???????[ 2012/12/25 10:00:59 ] ????????

????????

???????????????洢??????????????????????????????????????????????????????????Щ?????????????Щ?б????????????????????????????洢??????棬????????????????????α???ú????????????????????θ???????????????ü????????????????inserts??updates??deletes??selects????

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

??????SQLServer?У?????μ?DMVs?????????????Щ?????汾?в?????????????????????????o?????????????????????????????

????Sys.dm_db_index_operational_stats??sys.dm_db_index_usage_stats

????Sys.dm_db_index_operational_stats??

?????ú?????????insert/update/delete????????????????????????????????????????????????????????????????????????У?????????????漸?У?

?????? leaf_insert_count –??????????????
?????? leaf_delete_count -???????????????
?????? leaf_update_count -????????μ?????

??????????????????Щ??????????????????????????????У?

SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME]??
       I.[NAME] AS [INDEX NAME]??
       A.LEAF_INSERT_COUNT??
       A.LEAF_UPDATE_COUNT??
       A.LEAF_DELETE_COUNT
FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL??NULL??NULL??NULL ) A
       INNER JOIN SYS.INDEXES AS I
         ON I.[OBJECT_ID] = A.[OBJECT_ID]
            AND I.INDEX_ID = A.INDEX_ID
WHERE OBJECTPROPERTY(A.[OBJECT_ID]??'IsUserTable') = 1

????????????????????????????У???????????ж???insert/update/delete???????????????????????

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

?????? user_seeks – ??????????????
?????? user_scans- ?????????????
?????? user_lookups – ?????????????
?????? user_updates – ???????????????

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME]??
         I.[NAME] AS [INDEX NAME]??
         USER_SEEKS??
         USER_SCANS??
         USER_LOOKUPS??
         USER_UPDATES
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S
         INNER JOIN SYS.INDEXES AS I
           ON I.[OBJECT_ID] = S.[OBJECT_ID]
              AND I.INDEX_ID = S.INDEX_ID
WHERE    OBJECTPROPERTY(S.[OBJECT_ID]??'IsUserTable') = 1

??????????????????????????п??????ж?????????衢???????????????????