1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145
| #############################################################
USE DBName
GO
DECLARE @tsql NVARCHAR(MAX)
DECLARE @fillfactor INT
SET @fillfactor = 70
DECLARE @FragmentedIndexs TABLE (IndexID INT,
IndexName VARCHAR(100),
ObjectName VARCHAR(100),
AvgFragmentationInPercent DECIMAL(6,2),
FragmentCount INT,
AvgFragmentSizeInPage DECIMAL(6,2),
IndexDepth INT)
--Insert the Details for Fragmented Indexes.
INSERT INTO @FragmentedIndexs
SELECT
PS.index_id,
QUOTENAME(I.name) Name,
QUOTENAME(DB_NAME()) +'.'+ QUOTENAME(OBJECT_SCHEMA_NAME(I.[object_id])) + '.' + QUOTENAME(OBJECT_NAME(I.[object_id])) ObjectName,
PS.avg_fragmentation_in_percent,
PS.fragment_count,
PS.avg_fragment_size_in_pages,
PS.index_depth
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL ,NULL, NULL, NULL) AS PS
INNER JOIN sys.indexes AS I
ON PS.[object_id]= I.[object_id]
AND PS.index_id = I.index_id
WHERE
PS.avg_fragmentation_in_percent > 5
ORDER BY
PS.avg_fragmentation_in_percent DESC
--Select the details.
SELECT * FROM @FragmentedIndexs
--Prepare the Query to REORGANIZE the Indexes
SET @tsql = ''
SELECT @tsql =
STUFF(( SELECT DISTINCT
';' + 'ALTER INDEX ' + FI.IndexName + ' ON ' + FI.ObjectName + ' REORGANIZE '
FROM
@FragmentedIndexs FI
WHERE
FI.AvgFragmentationInPercent <= 30
FOR XML PATH('')), 1,1,'')
SELECT @tsql
PRINT 'REORGANIZING START'
EXEC sp_executesql @tsql
PRINT 'REORGANIZING END'
--Prepare the Query to REBUILD the Indexes
SET @tsql = ''
SELECT @tsql =
STUFF(( SELECT DISTINCT
';' + 'ALTER INDEX ' + FI.IndexName + ' ON ' + FI.ObjectName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ') '
FROM
@FragmentedIndexs FI
WHERE
FI.AvgFragmentationInPercent > 30
FOR XML PATH('')), 1,1,'')
SELECT @tsql
PRINT 'REBUILD START'
EXEC sp_executesql @tsql
PRINT 'REBUILD END'
############################################################# |
Partager