SQL Server 上的索引重建

Question

我正在对数据库进行索引重建。 我需要验证它是否完成。 请有人指导我。 我正在使用SQL Server 2008 R2。

- Pradeep

Answer 1

如果您想查看数据库中所有索引和表的详细信息，可以使用以下方法。

SELECT OBJECT_NAME(object_id),* 
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'SAMPLED')

我想你可能也在问如何了解重新索引的进度。为此，您可以使用

SELECT percent_complete 
from sys.dm_exec_requests 
where session_id= <spid of interest>

- Martin Smith

---

我运行了一个索引重建作业长达12个小时。运行百分比完成查询，结果返回为0。哈哈。 - MaDDoG

@MaDDoG - 实际上查看文档，似乎只有ALTER INDEX REORGANIZE才会填充此列。文档曾经也说过CREATE INDEX会更新此列，但看起来该说法现在已被删除。 - Martin Smith

我知道这是一个古老的帖子，但我和@MaDDoG情况相同 - 绝望地需要知道重建状态，但在2023年仍然找不到任何方法......唉。 - Christopher

@Christopher - 如果你执行 SELECT * FROM sys.dm_exec_query_statistics_xml(session_id)，那么这可能会给你“在飞行中”的实际执行计划，并允许你查看进度，我还没有测试过。 - Martin Smith

Answer 2

一个关键的事情是在重建索引之前和之后运行“索引物理统计”报告和“磁盘使用率最高的表”报告。

在“索引物理统计”报告中，您可以看到每个索引有多么碎片化。

要查看这些报告... * 在 Sql Server Management Studio 中右键单击数据库 * 悬停在“报告”上，然后选择想要的报告。

要设置一个脚本来识别碎片化的索引并重建它们（以及获取更多信息），请参考此链接：

http://www.foliotek.com/devblog/sql-server-optimization-with-index-rebuilding/

- Narnian

Answer 3

如果您已成功重新索引表格，则索引碎片化将为零（或接近于热表）。您可以使用此脚本检查碎片化水平。

DECLARE 
@IndexID int,
@TableID int,  
@IndexName varchar(256) 

--Enter index name here
SELECT @IndexName = '<index name>'
--Enter table name here
SET @TableID = OBJECT_ID('<table name>') 

SELECT @IndexID = IndID 
FROM sysindexes 
WHERE 
    id = @TableID 
    AND name = @IndexName 

DBCC SHOWCONTIG (@id, @IndexID)

您在输出中寻找的属性是名为扫描密度的属性。这应该接近100%。如果不是，则重新索引未完成/成功。

---

如果你有很多表格/索引，手动操作可能会变得很繁琐，因此可以通过像这样自动生成脚本来简化操作：

SELECT 'DBCC SHOWCONTIG ' +
    '(' 
       + CONVERT(varchar(32), si.id) + ',' 
       + CONVERT(varchar(32), si.indid) + 
    ')--'  + so.name
FROM sysobjects so 
INNER JOIN sysindexes si 
ON (so.id = si.id) 
WHERE (
    so.type = 'U' AND
    si.indid < 2 AND
    si.id = object_id(so.name) 
    )

- Seth

---

谢谢 Seth。我在数据库中有100张表，但不知道每张表的索引名称。还有其他的方法可以做到这一点吗？ - Pradeep

我已经添加了一个自动生成脚本，它应该为每个表和索引提供与上面相同的脚本。 - Seth

更正了表别名，并在末尾添加了 so.name 作为注释，这帮助我快速定位到它所指的对象。您可以使用 ORDER BY so.name 对列表进行排序。确实是一个有用的脚本。 - Yaroslav

@Yaroslav - 谢谢你发现了这个问题！ - Seth

Answer 4

您可以尝试以下步骤。它将重建数据库中所有表的索引，并在进度过程中将结果打印到管理工作室的消息窗格中：

CREATE PROCEDURE [dbo].[ReIndexDatabase]
AS
DECLARE @MyTable VARCHAR(255)
DECLARE myCursor

CURSOR FOR
SELECT table_name
FROM   information_schema.tables
WHERE  table_type = 'base table'

OPEN myCursor
FETCH NEXT
FROM myCursor INTO @MyTable

WHILE @@FETCH_STATUS = 0    BEGIN
  PRINT 'Reindexing Table:  ' + @MyTable
  EXEC('ALTER INDEX ALL ON '+@MyTable+'
  REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = OFF,
                STATISTICS_NORECOMPUTE = ON)');

  FETCH NEXT FROM myCursor INTO @MyTable
END

CLOSE myCursor
DEALLOCATE myCursor
EXEC sp_updatestats

您可以查看此链接以获取有关重新索引的更多信息或者此链接。

请注意页面顶部的信息。

- Isola Olufemi