|
Execute the following
Microsoft SQL Server T-SQL example scripts in SSMS Query Editor to create a reindexing stored procedure and execute the sproc to reindex all tables with at least 35%
logical fragmentation:
USE AdventureWorks;
GO
-- Reindex database - reindex all tables script
-- Reindex fragmented indexes - rebuild all fragmented indexes
-- DBREINDEX database - REBUILD all database indexes
CREATE PROCEDURE uspDBREINDEXFragmentedTables @FragmentationLimit DECIMAL
AS
BEGIN
CREATE TABLE #IndexFragmentation (
ObjectName CHAR(255),
ObjectId INT,
IndexName CHAR(255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
INSERT #IndexFragmentation
EXEC( 'DBCC SHOWCONTIG WITH TABLERESULTS , ALL_INDEXES')
DELETE #IndexFragmentation
WHERE left(ObjectName,3) = 'sys'
UPDATE #IndexFragmentation
SET ObjectName = s.name + '.' + o.name
FROM #IndexFragmentation i
JOIN sys.objects o
ON i.ObjectID = o.object_id
JOIN sys.schemas s
ON o.schema_id = s.schema_id
DECLARE @Table SYSNAME,
@DynamicSQL NVARCHAR(1024)
DECLARE @objectid INT,
@indexid INT
DECLARE @Fragmentation DECIMAL
DECLARE curIndexFrag CURSOR FOR
SELECT ObjectName,
LogicalFrag = max(LogicalFrag)
FROM #IndexFragmentation
WHERE LogicalFrag >= @FragmentationLimit
AND indexid != 0
AND indexid != 255
GROUP BY ObjectName
OPEN curIndexFrag
FETCH NEXT FROM curIndexFrag
INTO @Table,
@Fragmentation
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @DynamicSQL = 'DBCC DBREINDEX (''' + RTRIM(@Table) + ''', '''', 70)'
PRINT @DynamicSQL
-- Partial result:
-- DBCC DBREINDEX ('Sales.SpecialOfferProduct', '', 70)
EXEC sp_executesql @DynamicSQL
FETCH NEXT FROM curIndexFrag
INTO @Table,
@Fragmentation
END
CLOSE curIndexFrag
DEALLOCATE curIndexFrag
DROP TABLE #IndexFragmentation
END
GO
-- Execute database reindexing (INDEX REBUILD) stored procedure
-- Reindex indexes with 35% logical fragmentation or higher
EXEC uspDBREINDEXFragmentedTables 35.0
GO
------------
Related article:
SQL Server Index Maintenance Checklist
http://www.sqlusa.com/bestpractices2008/rebuild-all-indexes/
|
