SQLServer性能调优3之索引（Index）的维护 - 数据库编程

NDEX [idx_refno] ON [ordDemo](refno) WITH ( DROP_EXISTING = ON, FILLFACTOR = 70, ONLINE = ON ) GO -- 使用 DBCC DBREINDEX 重建 ordDemo 表上的所有索引 DBCC DBREINDEX ('ordDemo') GO -- 使用 DBCC DBREINDEX 重建 ordDemo 表上的一个索引 DBCC DBREINDEX ('ordDemo','idx_refno',90) GO

【DBCC DBREINDEX 将在后续版本被废弃】

基于作者的个人经验，在一张大数据量的表上进行重建操作时，使用批量日志恢复（bulk-logged recovery）或简单恢复（simple recovery）比较好，这能防止日志文件过大。不过需要提醒你的是，切换恢复模式时会打断数据库的备份链，所以如果你之前是完全恢复模式（full recovery），记得重建后再切换回来。

重建时一定要有耐心，长的可能花上1天，冒昧地打断他是非常危险的（数据库可能进入恢复模式）。

执行该操作的用户必须是该表的所有者，或是该服务器的sysadmin一员，或是该数据库的db_owner / db_ddladmin。

重整（REORGANIZE）索引来提高索引效率

重整不会锁定任何对象，他是一个优化当前 B-Tree，组织数据页的处理及碎片整理。重整索引处理示例脚本如下：

-- 重整 "ordDemo" 表上的 "idx_refno" 索引
ALTER INDEX [idx_refno] ON [ordDemo]
REORGANIZE
GO

-- 重整 ordDemo 表上所有索引
ALTER INDEX ALL ON [ordDemo]
REORGANIZE
GO

-- 重整 AdventureWorks2012 数据库中 ordDemo 表上所有索引
DBCC INDEXDEFRAG ('AdventureWorks2012','ordDemo')
GO

-- 重整 AdventureWorks2012 数据库中 ordDemo 表上索引 idx_refno
DBCC INDEXDEFRAG ('AdventureWorks2012','ordDemo','idx_refno')
GO

注意：执行该操作的用户必须是该表的所有者，或是该服务器的sysadmin一员，或是该数据库的db_owner / db_ddladmin。

发现缺失的索引

现在你已经了解索引带来的性能提升，但实际情况下很难在一开始就建立好足够正确及必要的索引，我们要怎样才能判断出哪些表需要索引，哪些索引建立得不对呢？

通常情况下，SQL Server 会利用既有的索引来执行查询脚本，如果没有找到索引他会自动生成一个并存放在DMV（dynamic management view）中。每当SQL Server 服务重启的时候这些信息会被清除，所以在获取缺失索引的过程中最好保持SQL Server 服务的运行，直到所有的业务逻辑跑完一遍。

可参照以下链接来获取更多相关信息：

sys.dm_db_missing_index_detailssys.dm_db_missing_index_group_statssys.dm_db_missing_index_groupssys.dm_db_missing_index_columns(Index_Handle)

提供一个现成的脚本：

SELECT
  avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)
    AS PossibleImprovement
  ,last_user_seek
  ,last_user_scan
  ,statement AS Object
  ,'CREATE INDEX [IDX_' + CONVERT(VARCHAR,GS.Group_Handle) + '_' +
  CONVERT(VARCHAR,D.Index_Handle) + '_'
    + REPLACE(REPLACE(REPLACE([statement],']',''),'[',''),'.','') +
    ']'
    +' ON '
    + [statement]
    + ' (' + ISNULL (equality_columns,'')
    + CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS
      NOT NULL THEN ',' ELSE '' END
    + ISNULL (inequality_columns, '')
    + ')'
    + ISNULL (' INCLUDE (' + included_columns + ')', '')
  AS Create_Index_Syntax
FROM
  sys.dm_db_missing_index_groups AS G
INNER JOIN
  sys.dm_db_missing_index_group_stats AS GS
ON
  GS.group_handle = G.index_group_handle
INNER JOIN
  sys.dm_db_missing_index_details AS D
ON
  G.index_handle = D.index_handle
Order By PossibleImprovement DESC

PS：你获取到的信息是一个提议列表，最终的决定权在你，另外DMV最多只保存500个索引。

发现未使用的索引

我们建立了索引来改进性能，但如果建立的索引没有被利用到，那反而成累赘了。

与前一个小节相同的原因，保持SQL Server 服务的运行，直到所有的业务逻辑跑完一遍。运行一下脚本：

SELECT
  ind.Index_id,
  obj.Name as TableName,
  ind.Name as IndexName,
  ind.Type_Desc,
  indUsage.user_seeks,
  indUsage.user_scans,
  indUsage.user_lookups,
  indUsage.user_updates,
  indUsage.last_user_seek,
  indUsage.last_user_scan,
  'drop index [' + ind.name + '] ON [' + obj.name + ']' as
    DropIndexCommand
FROM
  Sys.Indexes as ind
JOIN
  Sys.Objects as obj
ON
  ind.object_id=obj.Object_ID
LEFT JOIN
  sys.dm_db_index_usage_stats indUsage
ON
  ind.object_id = indUsage.object_id
AND
  ind.Index_id=indUsage.Index_id
WHERE
  ind.type_desc<>'HEAP' and obj.type<>'S'
AND
  objectproperty(obj.object_id,'isusertable') = 1
AND
  (isnull(

SQLServer性能调优3之索引（Index）的维护(二)

重整（REORGANIZE）索引来提高索引效率

发现缺失的索引

发现未使用的索引