数据库性能优化－－DMV（1）

在作系统优化时，应用了一些DMV的 相关知识，拿 出来与大家分享：

Use VitalDoc
SELECT TOP 1
  [Maintenance cost]  = (user_updates + system_updates)
  ,[Retrieval usage] = (user_seeks + user_scans + user_lookups)
  ,DatabaseName = DB_NAME()
  ,TableName = OBJECT_NAME(s.[object_id])
  ,IndexName = i.name
  -- Useful fields below:
--  ,user_updates 
--  ,system_updates
--  ,user_seeks
--  ,user_scans
--  ,user_lookups
--  ,system_seeks
--  ,system_scans
--  ,system_lookups
  -- Useful fields below:
--  ,*
INTO #TempMaintenanceCost
FROM   sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id]
    AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
 AND (user_updates + system_updates) > 0 -- Only report on active rows.
 AND s.[object_id] = -999  -- Dummy value, just to get table structure.
;

-- Loop around all the databases on the server.
EXEC sp_MSForEachDB 'USE [?];
-- Table already exists.
INSERT INTO #TempMaintenanceCost
SELECT TOP 10
  [Maintenance cost]  = (user_updates + system_updates)
  ,[Retrieval usage] = (user_seeks + user_scans + user_lookups)
  ,DatabaseName = DB_NAME()
  ,TableName = OBJECT_NAME(s.[object_id])
  ,IndexName = i.name
  -- Useful fields below:
--  ,user_updates 
--  ,system_updates
--  ,user_seeks
--  ,user_scans
--  ,user_lookups
--  ,system_seeks
--  ,system_scans
--  ,system_lookups
  -- Useful fields below:
--  ,*
FROM   sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id]
    AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
    AND i.name IS NOT NULL -- I.e. Ignore HEAP indexes.
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
 AND (user_updates + system_updates) > 0 -- Only report on active rows.
ORDER BY [Maintenance cost]  DESC
;
'

-- Select records.
SELECT TOP 10 *  FROM #TempMaintenanceCost ORDER BY [Maintenance cost]  DESC
-- Tidy up.
DROP TABLE #TempMaintenanceCost