Sql server 数据库索引优化

一，查询哪些数据库缺失索引

SELECT 
DatabaseName = DB_NAME(database_id)
,[Number Indexes Missing] = count(*) 
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY 2 DESC;
 

二，查询哪些库，表缺失索引

SELECT  
[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) 
, avg_user_impact
, 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;

EqualityUsage ：用 = 查询列

InequalityUsage：用<> 查询的列

Include Cloumns：用 in 查询的列

三，添加对应的非聚集索引

CREATE NONCLUSTERED INDEX 索引名 ON 
表名
(
  EqualityUsage，InequalityUsage
)include(Include Cloumns) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

drop index 索引名 on 表名