前一篇的文章介绍了通过建立索引来提高数据库的查询性能，这其实只是个开始。后续如果缺少适当的维护，你先前建立的索引甚至会成为拖累，成为数据库性能的下降的帮凶。
消除碎片可能是索引维护最常规的任务，微软官方给出的建议是当碎片等级为 5% - 30% 之间时采用 REORGANIZE 来“重整”索引，如果达到 30% 以上则使用 REBUILD 来“重建”索引。决定采用何种手段和操作时机可能需要考虑许多的因素，以下4条是你必须要考虑的：
PS：虽然碎片与性能紧密相关，但某些特定情况下他可以被忽略。比如你有一张带有聚集索引的表，几乎所有针对该表的处理仅仅是根据主键取出一条数据。该场合下碎片的影响可以忽略不计。
那么怎样确定某个索引的碎片状况呢？使用系统函数sys.dm_db_index_physical_stats 及系统目录 sys.Indexes，示例脚本如下：
-- 获取指定表（示例：ordDemo）上所有索引的信息 SELECT sysin.name as IndexName ,sysIn.index_id ,func.avg_fragmentation_in_percent ,func.index_type_desc as IndexType ,func.page_count FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'ordDemo'), NULL, NULL, NULL) AS func JOIN sys.indexes AS sysIn ON func.object_id = sysIn.object_id AND func.index_id = sysIn.index_id -- 聚集索引的 Index_id 为 1 -- 非聚集索引为 Index_id>1 -- 以下脚本用 WHERE 子句进行了筛选（剔除了没有索引的表） -- 该脚本返回数据库所有的索引，可能花费较长时间！ SELECT sysin.name as IndexName ,sysIn.index_id ,func.avg_fragmentation_in_percent ,func.index_type_desc as IndexType ,func.page_count FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS func JOIN sys.indexes AS sysIn ON func.object_id = sysIn.object_id AND func.index_id = sysIn.index_id WHERE sysIn.index_id>0;输出截图如下
示例数据库的碎片为0，这是因为碎片是在执行增删改时产生的，我们的数据库还没有做过类似操作。
前面提到过数据以8KB 数据页的方式存放在数据库中，假设你有一张建立了聚集索引的表，每当有数据插入时，数据库会根据主键找到插入位置（数据页）并写入信息。如果该数据页已经满了或者不够空间存放新的数据，数据库会建立一个新的8KB 数据页，而这个新建的过程会造成I/O消耗。
填充因子用来减少这种情况的发生，如果你设定填充因子为10，那么你的数据初始仅使用8KB 数据页中的10%，当插入新纪录时基本不用担心会发生多余的I/O消耗，因为数据页中预留了90%的空间。
填充因子也是把双刃剑，他在增加写操作性能的同时，降低了读操作的性能。
【填充因子仅当建立索引或重建（rebuildi）索引时起作用，对于一般的DML操作无效（数据页总是填充到100%）】
以下脚本帮助你了解索引的填充因子值：
SELECT OBJECT_NAME(OBJECT_ID) AS TableName ,Name as IndexName ,Type_Desc ,Fill_Factor FROM sys.indexes WHERE -- 这里通过WHERE筛选来仅仅表示聚集索引和非聚集索引 type_desc<>'HEAP'你还可以查看数据服务器上默认的填充因子值：
SELECT Description ,Value_in_use FROM sys.configurations WHERE Name ='fill factor (%)'PS：0表示不保留任何预留空间。
ALTER INDEX [idx_refno] ON [ordDemo] REBUILD WITH (FILLFACTOR= 80) GO -- 如果要设定服务器上的默认值，使用以下脚本 Sp_configure 'show advanced options', 1 GO RECONFIGURE GO sp_configure 'fill factor', 90 GO RECONFIGURE GO在一张静态表（偶然更新）的表上建议采用较大的填充因子（90%以上），在读写频繁的表上建议采用较低的填充因子（70% - 80%）。特别的，当你的聚集索引建立在一个自增字段上时，设定填充因子为100%也没有问题，因为新插入的数据总是在所有数据的最后，不会发生插入记录与记录之间的情况。
重建索引的作用顾名思义，他带来的好处包括消除碎片，统计值(statistics)更新，数据页中物理排序顺序的对齐。另外他还会根据填充因子来压缩数据页，（如果必要的话）新增数据页。好处一箩筐，只是这个操作非常耗资源，会花费相当长的时间。如果你决定开始重建索引，你还需要知道他有两种工作模式：
离线模式：这是默认的重建索引模式，它将锁定表直到重建完成。如果表很大，会导致用户（好几个小时都）无法使用该表。相比在线模式来说离线模式工作更快，消耗的TempDb的空间更小。
在线模式：如果客观条件不允许你锁定表，你就只能选择在线模式，这将耗费更多的时间和服务器资源。值得一提的是如果你的表包含了varchar (max), nvarchar (max), text 类型字段的话，将无法在该模式下进行重建索引。
【提示：该模式选择仅在开发版/企业版中支持，其他版本默认使用离线模式！】
以下是重建索引的示例脚本：
-- 在线模式下重建索引 idx_refno
ALTER INDEX [idx_refno] ON [ordDemo]
REBUILD WITH (FILLFACTOR=80, ONLINE=ON)
GO
-- 离线模式下重建索引 idx_refno
ALTER INDEX [idx_refno] ON [ordDemo]
REBUILD WITH (FILLFACTOR=80, ONLINE=OFF)
GO
-- 重建 ordDemo 表上的所有索引
ALTER INDEX ALL ON [ordDemo]
REBUILD WITH (FILLFACTOR=80, ONLINE=OFF)
GO
-- 重建索引 idx_reno （DROP_EXISTING=ON）
CREATE CLUSTERED INDEX [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。
重整不会锁定任何对象，他是一个优化当前 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 服务的运行，直到所有的业务逻辑跑完一遍。
可参照以下链接来获取更多相关信息：
提供一个现成的脚本：
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与前一个小节相同的原因，保持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(indUsage.user_seeks,0) = 0
AND
isnull(indUsage.user_scans,0) = 0
AND
isnull(indUsage.user_lookups,0) = 0)
ORDER BY
obj.name,ind.Name
GO视图是个存储的查询，表现得像表一样。它有两个主要好处：
索引视图在创建时就解析/优化好查询语句，并把相关信息以物理形式存放在数据库中。再决定使用索引视图前请考虑以下建议：
另外如果针对该对象的处理查询少更新多，又或者原始表是个经常更新的表，那么使用索引视图并不是很合适。
如果你有个查询包含较多的合计（aggregation）/联合（join）而且表的数据量很大，那么可以考虑使用索引视图。使用索引视图必须设定以下参数（NUMERIC_ROUNDABORT为OFF，其余为ON）
示例脚本：
CREATE VIEW POView WITH SCHEMABINDING AS SELECT POH.PurchaseOrderID ,POH.OrderDate ,EMP.LoginID ,V.Name AS VendorName ,SUM(POD.OrderQty) AS OrderQty ,SUM(POD.OrderQty*POD.UnitPrice) AS Amount ,COUNT_BIG(*) AS Count FROM [Purchasing].[PurchaseOrderHeader] AS POH JOIN [Purchasing].[PurchaseOrderDetail] AS POD ON POH.PurchaseOrderID = POD.PurchaseOrderID JOIN [HumanResources].[Employee] AS EMP ON POH.EmployeeID=EMP.BusinessEntityID JOIN [Purchasing].[Vendor] AS V ON POH.VendorID=V.BusinessEntityID GROUP BY POH.PurchaseOrderID ,POH.OrderDate ,EMP.LoginID ,V.Name GO -- 在视图上建立一个聚集索引使得它成为使得它成为索引视图 CREATE UNIQUE CLUSTERED INDEX IndexPOView ON POView (PurchaseOrderID) GO
SQL Server 的查询优化器总是尝试找到最佳的执行计划，有时候虽然你建立了索引视图，但优化器依然使用了原始表上的索引，此时你可以使用 WITH NOEXPAND 来强制使用索引视图上的索引（而不是原始表上的索引）。
索引视图在 SQL Server 2012 的各个版本上都有支持，在开发版或企业版中查询处理器甚至能以此来把匹配索引视图的查询都优化了。
索引视图建立时必须带上 WITH SCHEMABINDING，以此保证用到的字段不会被修改掉。
如果索引视图包含了 GROUP BY 子句，则必须在 SELECT 子句中包含 COUNT_BIG (*)，并且不能指定 HAVING, CUBE, 以及 ROLLUP。
首先来介绍一下计算字段（Computed Columns），它通过一个表达式来引用同一张表的其他字段，然后运算出一个结果。这个字段的值会在每次被调用时都重新计算，除非你在建立时带上 PERSISTED 标记。
在决定是否在计算字段上建立索引前，需要考虑一下几点：
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
下面我们来看一个完整的例子：
1. 设定系统变量，并建立我们的测试数据表
SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET ARITHABORT ON SET CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON SET NUMERIC_ROUNDABORT OFF SELECT [SalesOrderID] ,[SalesOrderDetailID] ,[CarrierTrackingNumber] ,[OrderQty] ,[ProductID] ,[SpecialOfferID] ,[UnitPrice] INTO SalesOrderDetailDemo FROM [AdventureWorks2012].[Sales].[SalesOrderDetail] GO
CREATE FUNCTION [dbo].[UDFTotalAmount] (@TotalPrice numeric(10,3), @Freight TINYINT) RETURNS Numeric(10,3) WITH SCHEMABINDING AS BEGIN DECLARE @NetPrice Numeric(10,3) SET @NetPrice = @TotalPrice + (@TotalPrice*@Freight/100) RETURN @NetPrice END GO --adding computed column SalesOrderDetailDemo table ALTER TABLE SalesOrderDetailDemo ADD [NetPrice] AS [dbo].[UDFTotalAmount] ( OrderQty*UnitPrice,5) GO
CREATE Clustered Index idx_SalesOrderID_SalesOrderDetailID_ SalesOrderDetailDemo ON SalesOrderDetailDemo(SalesOrderID,SalesOrderDetailID) GO --checking SalesOrderDetailDemo with statistics option ON to --measure performance SET STATISTICS IO ON SET STATISTICS TIME ON GO --checking SELECT statement without having Index on Computed Column SELECT * FROM SalesOrderDetailDemo WHERE NetPrice>5000 GO
| SQL Server parse and compile time: CPU time = 650 ms, elapsed time = 650 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. (3864 row(s) affected) Table ‘SalesOrderDetailDemo‘. Scan count 1, logical reads 757, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 562 ms, elapsed time = 678 ms. |
4. 在计算字段上建立索引之前，可以用以下的脚本确认是否满足之前提到的创建要求：（返回值：0不满足，1满足）
SELECT
COLUMNPROPERTY( OBJECT_ID('SalesOrderDetailDemo'),'NetPrice','IsIndexable') AS 'Indexable?'
,COLUMNPROPERTY( OBJECT_ID('SalesOrderDetailDemo'),'NetPrice','IsDeterministic') AS 'Deterministic?'
,OBJECTPROPERTY(OBJECT_ID('UDFTotalAmount'),'IsDeterministic')'UDFDeterministic?'
,COLUMNPROPERTY(OBJECT_ID('SalesOrderDetailDemo'),'NetPrice','IsPrecise') AS 'Precise?'CREATE INDEX idx_SalesOrderDetailDemo_NetPrice ON SalesOrderDetailDemo ( NetPrice ) GO SELECT * FROM SalesOrderDetailDemo WHERE NetPrice>5000 GO这次的性能结果如下：
| SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. (3864 row(s) affected) Table ‘SalesOrderDetailDemo‘. Scan count 1, logical reads 757, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 546 ms, elapsed time = 622 ms. |
SELECT
CASE index_id
WHEN 0 THEN 'HEAP'
WHEN 1 THEN 'Clustered Index'
ELSE 'Non-Clustered Index'
END AS Index_Type,
SUM(CASE
WHEN FilledPage > PageToDeduct THEN (FilledPage-PageToDeduct)
ELSE
0
END )* 8 Index_Size
FROM
(
SELECT
partition_id,
index_id,
SUM (used_page_count) AS FilledPage,
SUM (
CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE
lob_used_page_count + row_overflow_used_page_count
END
) AS PageToDeduct
FROM
sys.dm_db_partition_stats
GROUP BY
partition_id,index_id
) AS InnerTable
GROUP BY
index_id
GO
SQL Server 性能调优3 之索引（Index）的维护,布布扣,bubuko.com
SQL Server 性能调优3 之索引（Index）的维护
原文：http://blog.csdn.net/sqlchen/article/details/36380777