|
Execute the following
Microsoft SQL Server T-SQL database administration script in SSMS Query Editor to REBUILD all indexes in the AdventureWorks2008 sample database.
-- SQL Server 2008 script to REBUILD all indexes for all tables
USE AdventureWorks2008;
DECLARE @DatabaseName SYSNAME = DB_NAME(), @TableName VARCHAR(256)
DECLARE @FILLFACTOR INT = 85
DECLARE @DynamicSQL NVARCHAR(max) =
'DECLARE curAllTablesInDB CURSOR FOR SELECT TABLE_SCHEMA +
''.'' + TABLE_NAME AS TABLENAME
FROM ' + @DatabaseName + '.INFORMATION_SCHEMA.TABLES WHERE
TABLE_TYPE = ''BASE TABLE'''
BEGIN
EXEC sp_executeSQL @DynamicSQL -- create tables cursor
OPEN curAllTablesInDB
FETCH NEXT FROM curAllTablesInDB INTO @TableName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @DynamicSQL = 'ALTER INDEX ALL ON ' + @TableName +
' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR,@FILLFACTOR) + ')'
PRINT @DynamicSQL
-- ALTER INDEX ALL ON Purchasing.ShipMethod REBUILD WITH (FILLFACTOR = 85)
EXEC sp_executeSQL @DynamicSQL
FETCH NEXT FROM curAllTablesInDB INTO @TableName
END -- cursor WHILE
CLOSE curAllTablesInDB
DEALLOCATE curAllTablesInDB
END
------------
-- INDEX REBUILD ONLINE
USE AdventureWorks2008;
GO
CREATE PROC sprocDBIndexREBUILD @FILLFACTOR INT = 90
AS
BEGIN
DECLARE @DatabaseName SYSNAME = DB_NAME(), @TableName VARCHAR(256)
DECLARE @DynamicSQL NVARCHAR(max) =
'DECLARE curAllTablesInDB CURSOR FOR SELECT TABLE_SCHEMA +
''.'' + TABLE_NAME AS TABLENAME
FROM ' + @DatabaseName + '.INFORMATION_SCHEMA.TABLES WHERE
TABLE_TYPE = ''BASE TABLE'''
BEGIN
EXEC sp_executeSQL @DynamicSQL -- create tables cursor
OPEN curAllTablesInDB
FETCH NEXT FROM curAllTablesInDB INTO @TableName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @DynamicSQL = 'ALTER INDEX ALL ON ' + @TableName +
' REBUILD WITH (ONLINE=ON, FILLFACTOR = ' + CONVERT(VARCHAR,@FILLFACTOR) + ')'
-- PRINT @DynamicSQL
EXEC sp_executeSQL @DynamicSQL
FETCH NEXT FROM curAllTablesInDB INTO @TableName
END -- cursor WHILE
CLOSE curAllTablesInDB
DEALLOCATE curAllTablesInDB
END
END -- sproc
GO
------------
-- Index fragmentation report
SELECT SCHEMA_NAME(o.schema_id) AS SchemaName,
OBJECT_NAME(a.object_id) AS TableName,
i.name AS IndexName,
a.index_id AS IndexID,
convert(tinyint,a.avg_fragmentation_in_percent) AS [Fragment%]
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL,
NULL, 'LIMITED') AS a
INNER JOIN sys.indexes i
ON i.index_id = a.index_id
AND i.object_id = a.object_id
INNER JOIN sys.objects o
ON a.object_id = o.object_id
ORDER BY SchemaName, TableName, IndexID
------------
Related articles:
Ola Hallengren: SQL Server Backup, Integrity Check, and Index and Statistics Maintenance
SQL SERVER – ReIndexing Database Tables and Update Statistics on Tables
SQL Server script to rebuild all indexes for all tables and all databases
http://www.sqlusa.com/bestpractices2008/performancetuning/missingindexes/
|
