重建数据库中的所有索引 [英] Rebuild all indexes in a Database

问题描述

我有一个非常大的SQL Server 2008 R2数据库(1.5TB)，并将在同一表中的某些数据从一列复制到另一列.有人告诉我该架构具有大量索引，并且想知道是否存在将重建所有索引的默认查询或脚本.还建议同时更新统计信息吗?

I have a very large SQL Server 2008 R2 database (1.5TB) and will be copying some data from column to column within the same table. I've been told that the schema has a large number of indexes and was wondering if there is a default query or script that will rebuild all the indexes. Have also been advised to update the statistics at the same time?

30个表中的每个表都有一个聚集索引和13个非聚集索引

Each of the 30 tables has one clustered index and 13x non-clustered indexes

谢谢.

推荐答案

尝试以下脚本:

Exec sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD'
GO

也

我希望(经过长时间的搜索)使用以下脚本，该脚本包含 @fillfactor 确定每个叶子级页面上占多少百分比的空间带有数据.

I prefer(After a long search) to use the following script, it contains @fillfactor determines how much percentage of the space on each leaf-level page is filled with data.

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80 
DECLARE TableCursor CURSOR FOR
SELECT QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))+'.' + QUOTENAME(name) AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

有关更多信息，请检查以下链接:

for more info, check the following link:

，如果要检查数据库索引的索引碎片，请尝试以下脚本:

and if you want to Check Index Fragmentation on Indexes in a Database, try the following script:

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() AND dbtables.[name] like '%%'
ORDER BY indexstats.avg_fragmentation_in_percent desc

有关更多信息，请检查以下链接:

For more information, Check the following link:

http://www.schneider-electric.com/en/faqs/FA234246/

这篇关于重建数据库中的所有索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！