数据库性能优化－DMV(2)

-- Index field use analysis

 use DBNAME

SELECT  TOP 20
  [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
  , avg_user_impact -- Query cost would reduce by this amount, on average.
  , TableName = statement
  , [EqualityUsage] = equality_columns
  , [InequalityUsage] = inequality_columns
  , [Include Cloumns] = included_columns
FROM  sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;

select * from sys.dm_db_missing_index_groups

 

 

-- Waiting Item Analysis

use DBNAME

SELECT TOP 10
 [Wait type] = wait_type,
 [Wait time (s)] = wait_time_ms / 1000,
 [% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0 / SUM(wait_time_ms) OVER())
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
ORDER BY wait_time_ms DESC;