This script remedy index fragmentation by reorganizing or rebuilding an index.
Small tables, small indexes, low fragmentation, we do not care, and keep simple & easy
We are talking about the guidance which is: •if a table has less than 10000 rows, to do nothing •if an index has less than 1000 pages, to do nothing •if the index has: less than 5% logical fragmentation, to do nothing between 5% and 30% logical fragmentation, reorganize it more than 30% logical fragmentation, rebuild it • New FILLFACTOR = 98%
Why do we fix something we do not need it? We should start to delete unused indexes if it is possible: [see Unused indexes] •Unused index, delete it, when : - Nonclustered indexes - Non-primary keys - Non unique - Non-unused (#Total read < 100 and #total write > 1000 ) - Hypothetical Index.
Alternative solutions:
- We can buy an application for index monitoring and analysis.
- We can change the configuration. ex: MIXED_PAGE_ALLOCATION
- We can create better indexes.
- We can use this nice solution of Michelle Ufford [https://github.com/MichelleUfford/sql-scripts/tree/master/indexes]
Select info about tables
1
2-- Select info about tables
3SELECT
4 db_ID() as database_id
5 ,tbSchena1.NAME AS SchemaName
6 ,tbTable1.NAME AS TableName
7 ,tbPartition1.rows AS RowCounts
8 ,SUM(tbAllocation1.used_pages) AS used_pages
9 ,SUM(tbAllocation1.total_pages) * 8 AS TotalSpaceKB
10 ,CAST(ROUND(SUM(tbAllocation1.total_pages) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS TotalSpaceMB
11 ,SUM(tbAllocation1.used_pages) * 8 AS UsedSpaceKB
12 ,CAST(ROUND(SUM(tbAllocation1.used_pages) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UsedSpaceMB
13 ,(SUM(tbAllocation1.total_pages) - SUM(tbAllocation1.used_pages)) * 8 AS UnusedSpaceKB
14 ,CAST(ROUND((SUM(tbAllocation1.total_pages) - SUM(tbAllocation1.used_pages)) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
15FROM sys.tables AS tbTable1
16INNER JOIN sys.indexes AS tbIndex1 ON tbTable1.object_id = tbIndex1.object_id
17INNER JOIN sys.partitions AS tbPartition1 ON tbIndex1.object_id = tbPartition1.object_id
18 AND tbIndex1.index_id = tbPartition1.index_id
19INNER JOIN sys.allocation_units AS tbAllocation1 ON tbPartition1.partition_id = tbAllocation1.container_id
20LEFT OUTER JOIN sys.schemas AS tbSchena1 ON tbTable1.schema_id = tbSchena1.schema_id
21WHERE (tbTable1.NAME NOT LIKE 'dt%')
22 AND (tbTable1.is_ms_shipped = 0)
23 AND (tbIndex1.object_id > 255)
24GROUP BY tbTable1.NAME
25 ,tbSchena1.NAME
26 ,tbPartition1.rows
27HAVING (tbPartition1.rows > 10000)
Select info about indexes
1
2-- Select info about indexes
3
4SELECT index_physical_stats.database_id
5 ,tbSchema2.NAME AS ShemaName
6 ,tbTable2.NAME AS TableName
7 ,tbTable2.object_id AS table_id
8 ,tbIndex2.NAME AS IndexName
9 ,tbIndex2.index_id
10 ,tbIndex2.type
11 ,tbIndex2.type_desc
12 ,SUM(tbAllocation2.used_pages) AS used_pages
13 ,8 * SUM(tbAllocation2.used_pages) AS IndexSizeKb
14 ,index_usage_stats.user_updates AS 'TotalWrites'
15 ,index_usage_stats.user_seeks + index_usage_stats.user_scans + index_usage_stats.user_lookups AS 'TotalReads'
16 ,index_usage_stats.user_updates - (index_usage_stats.user_seeks + index_usage_stats.user_scans + index_usage_stats.user_lookups) AS 'Difference'
17 ,index_physical_stats.index_depth
18 ,index_physical_stats.index_level
19 ,index_physical_stats.avg_fragmentation_in_percent
20 ,index_physical_stats.fragment_count
21 ,index_physical_stats.avg_fragment_size_in_pages
22 ,index_physical_stats.page_count
23 ,CASE
24 WHEN tbIndex2.type IN ( 2, 6, 7)
25 THEN 'Nonclustered'
26 ELSE ''
27 END AS IndexType
28 ,tbIndex2.is_unique
29 ,tbIndex2.is_primary_key
30
31 ,CASE
32 WHEN tbIndex2.type IN ( 2, 6, 7)
33 AND tbIndex2.is_unique = 0
34 AND tbIndex2.is_primary_key = 0
35 THEN CASE
36 WHEN (index_usage_stats.user_seeks + index_usage_stats.user_scans + index_usage_stats.user_lookups) < 100
37 AND index_usage_stats.user_updates < 1000
38 THEN ''
39 WHEN (index_usage_stats.user_seeks + index_usage_stats.user_scans + index_usage_stats.user_lookups) < 100
40 AND index_usage_stats.user_updates >= 1000
41 THEN 'unused'
42 ELSE 'used'
43 END
44 ELSE 'keep'
45 END AS IndexStatus
46
47 ,CASE WHEN SUM(tbAllocation2.used_pages) < 1000
48 THEN ''
49 ELSE
50 CASE
51 WHEN index_physical_stats.avg_fragmentation_in_percent < 5 THEN ''
52 WHEN index_physical_stats.avg_fragmentation_in_percent BETWEEN 5 and 30 AND tbIndex2.type > 0 THEN 'reorganize'
53 WHEN index_physical_stats.avg_fragmentation_in_percent > 30 THEN 'rebuild'
54 ELSE '' END
55 END
56 AS IndexToDo
57 ,CASE WHEN SUM(tbAllocation2.used_pages) < 1000
58 THEN ''
59 ELSE
60 CASE WHEN tbIndex2.NAME IS NULL THEN
61 CASE
62 WHEN index_physical_stats.avg_fragmentation_in_percent > 30
63 THEN 'ALTER TABLE ['+tbSchema2.NAME+'].['+tbTable2.NAME+'] REBUILD;'
64 ELSE ''
65 END
66 ELSE
67 CASE
68 WHEN index_physical_stats.avg_fragmentation_in_percent < 5 THEN ''
69 WHEN index_physical_stats.avg_fragmentation_in_percent BETWEEN 5 and 30 THEN
70 'ALTER INDEX ['+tbIndex2.name+'] ON ['+tbSchema2.NAME+'].['+tbTable2.name+'] REORGANIZE PARTITION = ALL '
71 WHEN index_physical_stats.avg_fragmentation_in_percent > 30 THEN
72 'ALTER INDEX ['+tbIndex2.name+'] ON ['+tbSchema2.NAME+'].['+tbTable2.name+'] REBUILD PARTITION = ALL WITH ('
73 + 'FILLFACTOR =' + CAST(98 AS VARCHAR(3) )
74 +')'
75 ELSE '?'
76 END
77 END
78 END
79 AS IndexSqlToFix
80
81FROM sys.partitions AS tbPartition2
82INNER JOIN sys.indexes AS tbIndex2
83 ON tbPartition2.index_id = tbIndex2.index_id AND tbPartition2.object_id = tbIndex2.object_id
84INNER JOIN sys.schemas AS tbSchema2
85INNER JOIN sys.tables AS tbTable2
86 ON tbSchema2.schema_id = tbTable2.schema_id
87 ON tbPartition2.object_id = tbTable2.object_id
88 AND tbIndex2.object_id = tbTable2.object_id
89INNER JOIN sys.allocation_units AS tbAllocation2
90 ON tbPartition2.partition_id = tbAllocation2.container_id
91INNER JOIN sys.dm_db_index_usage_stats AS index_usage_stats
92 ON tbIndex2.object_id = index_usage_stats.object_id
93 AND tbIndex2.is_disabled = 0
94 AND tbIndex2.index_id = index_usage_stats.index_id
95INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS index_physical_stats
96 ON index_physical_stats.database_id = index_usage_stats.database_id
97 AND index_physical_stats.object_id = tbIndex2.object_id
98 AND index_physical_stats.index_id = tbIndex2.index_id
99GROUP BY
100 tbSchema2.NAME
101 ,tbTable2.NAME
102 ,tbTable2.object_id
103 ,tbIndex2.NAME
104 ,tbIndex2.index_id
105 ,index_usage_stats.user_seeks
106 ,index_usage_stats.user_scans
107 ,index_usage_stats.user_lookups
108 ,index_usage_stats.user_updates
109 ,tbIndex2.type
110 ,tbIndex2.type_desc
111 ,tbIndex2.is_unique
112 ,tbIndex2.is_primary_key
113 ,tbIndex2.is_padded
114 ,tbIndex2.ignore_dup_key
115 ,tbIndex2.fill_factor
116 ,tbIndex2.allow_page_locks
117 ,tbIndex2.allow_row_locks
118 ,index_physical_stats.database_id
119 ,index_physical_stats.index_depth
120 ,index_physical_stats.index_level
121 ,index_physical_stats.avg_fragmentation_in_percent
122 ,index_physical_stats.fragment_count
123 ,index_physical_stats.avg_fragment_size_in_pages
124 ,index_physical_stats.page_count
Now, we put together both queries.
1
2SELECT
3 tbTableInfo.database_id
4, tbTableInfo.SchemaName, tbTableInfo.TableName, tbTableInfo.RowCounts
5, tbTableInfo.used_pages
6, tbIndexInfo.IndexName
7, tbIndexInfo.index_id
8, tbIndexInfo.[type_desc]
9, tbIndexInfo.used_pages AS Index_pages
10, tbIndexInfo.IndexSizeKb, tbIndexInfo.[TotalWrites]
11, tbIndexInfo.[TotalReads]
12, tbIndexInfo.[Difference]
13, tbIndexInfo.index_depth
14, tbIndexInfo.index_level
15, tbIndexInfo.fragment_count
16, tbIndexInfo.avg_fragmentation_in_percent
17, tbIndexInfo.avg_fragment_size_in_pages
18, tbIndexInfo.page_count
19, tbIndexInfo.IndexType
20, tbIndexInfo.IndexStatus
21, tbIndexInfo.IndexToDo
22, tbIndexInfo.IndexSqlToFix
23 FROM
24(
25SELECT
26 db_ID() as database_id
27 ,tbSchena1.NAME AS SchemaName
28 ,tbTable1.NAME AS TableName
29 ,tbPartition1.rows AS RowCounts
30 ,SUM(tbAllocation1.used_pages) AS used_pages
31 ,SUM(tbAllocation1.total_pages) * 8 AS TotalSpaceKB
32 ,CAST(ROUND(SUM(tbAllocation1.total_pages) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS TotalSpaceMB
33 ,SUM(tbAllocation1.used_pages) * 8 AS UsedSpaceKB
34 ,CAST(ROUND(SUM(tbAllocation1.used_pages) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UsedSpaceMB
35 ,(SUM(tbAllocation1.total_pages) - SUM(tbAllocation1.used_pages)) * 8 AS UnusedSpaceKB
36 ,CAST(ROUND((SUM(tbAllocation1.total_pages) - SUM(tbAllocation1.used_pages)) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
37FROM sys.tables AS tbTable1
38INNER JOIN sys.indexes AS tbIndex1 ON tbTable1.object_id = tbIndex1.object_id
39INNER JOIN sys.partitions AS tbPartition1 ON tbIndex1.object_id = tbPartition1.object_id
40 AND tbIndex1.index_id = tbPartition1.index_id
41INNER JOIN sys.allocation_units AS tbAllocation1 ON tbPartition1.partition_id = tbAllocation1.container_id
42LEFT OUTER JOIN sys.schemas AS tbSchena1 ON tbTable1.schema_id = tbSchena1.schema_id
43WHERE (tbTable1.NAME NOT LIKE 'dt%')
44 AND (tbTable1.is_ms_shipped = 0)
45 AND (tbIndex1.object_id > 255)
46GROUP BY tbTable1.NAME
47 ,tbSchena1.NAME
48 ,tbPartition1.rows
49HAVING (tbPartition1.rows > 10000)
50) tbTableInfo
51INNER JOIN
52(
53SELECT index_physical_stats.database_id
54 ,tbSchema2.NAME AS ShemaName
55 ,tbTable2.NAME AS TableName
56 ,tbTable2.object_id AS table_id
57 ,tbIndex2.NAME AS IndexName
58 ,tbIndex2.index_id
59 ,tbIndex2.type
60 ,tbIndex2.type_desc
61 ,SUM(tbAllocation2.used_pages) AS used_pages
62 ,8 * SUM(tbAllocation2.used_pages) AS IndexSizeKb
63 ,index_usage_stats.user_updates AS 'TotalWrites'
64 ,index_usage_stats.user_seeks + index_usage_stats.user_scans + index_usage_stats.user_lookups AS 'TotalReads'
65 ,index_usage_stats.user_updates - (index_usage_stats.user_seeks + index_usage_stats.user_scans + index_usage_stats.user_lookups) AS 'Difference'
66 ,index_physical_stats.index_depth
67 ,index_physical_stats.index_level
68 ,index_physical_stats.avg_fragmentation_in_percent
69 ,index_physical_stats.fragment_count
70 ,index_physical_stats.avg_fragment_size_in_pages
71 ,index_physical_stats.page_count
72 ,CASE
73 WHEN tbIndex2.type IN ( 2, 6, 7)
74 THEN 'Nonclustered'
75 ELSE ''
76 END AS IndexType
77 ,tbIndex2.is_unique
78 ,tbIndex2.is_primary_key
79
80 ,CASE
81 WHEN tbIndex2.type IN ( 2, 6, 7)
82 AND tbIndex2.is_unique = 0
83 AND tbIndex2.is_primary_key = 0
84 THEN CASE
85 WHEN (index_usage_stats.user_seeks + index_usage_stats.user_scans + index_usage_stats.user_lookups) < 100
86 AND index_usage_stats.user_updates < 1000
87 THEN ''
88 WHEN (index_usage_stats.user_seeks + index_usage_stats.user_scans + index_usage_stats.user_lookups) < 100
89 AND index_usage_stats.user_updates >= 1000
90 THEN 'unused'
91 ELSE 'used'
92 END
93 ELSE 'keep'
94 END AS IndexStatus
95
96 ,CASE WHEN SUM(tbAllocation2.used_pages) < 1000
97 THEN ''
98 ELSE
99 CASE
100 WHEN index_physical_stats.avg_fragmentation_in_percent < 5 THEN ''
101 WHEN index_physical_stats.avg_fragmentation_in_percent BETWEEN 5 and 30 AND tbIndex2.type > 0 THEN 'reorganize'
102 WHEN index_physical_stats.avg_fragmentation_in_percent > 30 THEN 'rebuild'
103 ELSE '' END
104 END
105 AS IndexToDo
106 ,CASE WHEN SUM(tbAllocation2.used_pages) < 1000
107 THEN ''
108 ELSE
109 CASE WHEN tbIndex2.NAME IS NULL THEN
110 CASE
111 WHEN index_physical_stats.avg_fragmentation_in_percent > 30
112 THEN 'ALTER TABLE ['+tbSchema2.NAME+'].['+tbTable2.NAME+'] REBUILD;'
113 ELSE ''
114 END
115 ELSE
116 CASE
117 WHEN index_physical_stats.avg_fragmentation_in_percent < 5 THEN ''
118 WHEN index_physical_stats.avg_fragmentation_in_percent BETWEEN 5 and 30 THEN
119 'ALTER INDEX ['+tbIndex2.name+'] ON ['+tbSchema2.NAME+'].['+tbTable2.name+'] REORGANIZE PARTITION = ALL '
120 WHEN index_physical_stats.avg_fragmentation_in_percent > 30 THEN
121 'ALTER INDEX ['+tbIndex2.name+'] ON ['+tbSchema2.NAME+'].['+tbTable2.name+'] REBUILD PARTITION = ALL WITH ('
122 + 'FILLFACTOR =' + CAST(98 AS VARCHAR(3) )
123 +')'
124 ELSE '?'
125 END
126 END
127 END
128 AS IndexSqlToFix
129
130FROM sys.partitions AS tbPartition2
131INNER JOIN sys.indexes AS tbIndex2
132 ON tbPartition2.index_id = tbIndex2.index_id AND tbPartition2.object_id = tbIndex2.object_id
133INNER JOIN sys.schemas AS tbSchema2
134INNER JOIN sys.tables AS tbTable2
135 ON tbSchema2.schema_id = tbTable2.schema_id
136 ON tbPartition2.object_id = tbTable2.object_id
137 AND tbIndex2.object_id = tbTable2.object_id
138INNER JOIN sys.allocation_units AS tbAllocation2
139 ON tbPartition2.partition_id = tbAllocation2.container_id
140INNER JOIN sys.dm_db_index_usage_stats AS index_usage_stats
141 ON tbIndex2.object_id = index_usage_stats.object_id
142 AND tbIndex2.is_disabled = 0
143 AND tbIndex2.index_id = index_usage_stats.index_id
144INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS index_physical_stats
145 ON index_physical_stats.database_id = index_usage_stats.database_id
146 AND index_physical_stats.object_id = tbIndex2.object_id
147 AND index_physical_stats.index_id = tbIndex2.index_id
148GROUP BY
149 tbSchema2.NAME
150 ,tbTable2.NAME
151 ,tbTable2.object_id
152 ,tbIndex2.NAME
153 ,tbIndex2.index_id
154 ,index_usage_stats.user_seeks
155 ,index_usage_stats.user_scans
156 ,index_usage_stats.user_lookups
157 ,index_usage_stats.user_updates
158 ,tbIndex2.type
159 ,tbIndex2.type_desc
160 ,tbIndex2.is_unique
161 ,tbIndex2.is_primary_key
162 ,tbIndex2.is_padded
163 ,tbIndex2.ignore_dup_key
164 ,tbIndex2.fill_factor
165 ,tbIndex2.allow_page_locks
166 ,tbIndex2.allow_row_locks
167 ,index_physical_stats.database_id
168 ,index_physical_stats.index_depth
169 ,index_physical_stats.index_level
170 ,index_physical_stats.avg_fragmentation_in_percent
171 ,index_physical_stats.fragment_count
172 ,index_physical_stats.avg_fragment_size_in_pages
173 ,index_physical_stats.page_count
174) as tbIndexInfo
175
176ON tbTableInfo.TableName = tbIndexInfo.TableName
177AND tbTableInfo.SchemaName = tbIndexInfo.ShemaName
178WHERE IndexSqlToFix <> ''
It can be dangerous but if you want to automatize. To be a safe script we add 5 seconds between each execute of script.
1
2SET NOCOUNT ON
3DECLARE @tb TABLE (IndexSqlToFix NVARCHAR(MAX), avg_fragmentation_in_percent FLOAT)
4
5INSERT INTO @tb (IndexSqlToFix, avg_fragmentation_in_percent)
6SELECT IndexSqlToFix, avg_fragmentation_in_percent FROM
7(
8SELECT
9 db_ID() as database_id
10 ,tbSchena1.NAME AS SchemaName
11 ,tbTable1.NAME AS TableName
12 ,tbPartition1.rows AS RowCounts
13 ,SUM(tbAllocation1.used_pages) AS used_pages
14 ,SUM(tbAllocation1.total_pages) * 8 AS TotalSpaceKB
15 ,CAST(ROUND(SUM(tbAllocation1.total_pages) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS TotalSpaceMB
16 ,SUM(tbAllocation1.used_pages) * 8 AS UsedSpaceKB
17 ,CAST(ROUND(SUM(tbAllocation1.used_pages) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UsedSpaceMB
18 ,(SUM(tbAllocation1.total_pages) - SUM(tbAllocation1.used_pages)) * 8 AS UnusedSpaceKB
19 ,CAST(ROUND((SUM(tbAllocation1.total_pages) - SUM(tbAllocation1.used_pages)) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
20FROM sys.tables AS tbTable1
21INNER JOIN sys.indexes AS tbIndex1 ON tbTable1.object_id = tbIndex1.object_id
22INNER JOIN sys.partitions AS tbPartition1 ON tbIndex1.object_id = tbPartition1.object_id
23 AND tbIndex1.index_id = tbPartition1.index_id
24INNER JOIN sys.allocation_units AS tbAllocation1 ON tbPartition1.partition_id = tbAllocation1.container_id
25LEFT OUTER JOIN sys.schemas AS tbSchena1 ON tbTable1.schema_id = tbSchena1.schema_id
26WHERE (tbTable1.NAME NOT LIKE 'dt%')
27 AND (tbTable1.is_ms_shipped = 0)
28 AND (tbIndex1.object_id > 255)
29GROUP BY tbTable1.NAME
30 ,tbSchena1.NAME
31 ,tbPartition1.rows
32HAVING (tbPartition1.rows > 10000)
33) tbTableInfo
34INNER JOIN
35(
36SELECT index_physical_stats.database_id
37 ,tbSchema2.NAME AS ShemaName
38 ,tbTable2.NAME AS TableName
39 ,tbTable2.object_id AS table_id
40 ,tbIndex2.NAME AS IndexName
41 ,tbIndex2.index_id
42 ,tbIndex2.type
43 ,tbIndex2.type_desc
44 ,SUM(tbAllocation2.used_pages) AS used_pages
45 ,8 * SUM(tbAllocation2.used_pages) AS IndexSizeKb
46 ,index_usage_stats.user_updates AS 'TotalWrites'
47 ,index_usage_stats.user_seeks + index_usage_stats.user_scans + index_usage_stats.user_lookups AS 'TotalReads'
48 ,index_usage_stats.user_updates - (index_usage_stats.user_seeks + index_usage_stats.user_scans + index_usage_stats.user_lookups) AS 'Difference'
49 ,index_physical_stats.index_depth
50 ,index_physical_stats.index_level
51 ,index_physical_stats.avg_fragmentation_in_percent
52 ,index_physical_stats.fragment_count
53 ,index_physical_stats.avg_fragment_size_in_pages
54 ,index_physical_stats.page_count
55 ,CASE
56 WHEN tbIndex2.type IN ( 2, 6, 7)
57 THEN 'Nonclustered'
58 ELSE ''
59 END AS IndexType
60 ,tbIndex2.is_unique
61 ,tbIndex2.is_primary_key
62
63 ,CASE
64 WHEN tbIndex2.type IN ( 2, 6, 7)
65 AND tbIndex2.is_unique = 0
66 AND tbIndex2.is_primary_key = 0
67 THEN CASE
68 WHEN (index_usage_stats.user_seeks + index_usage_stats.user_scans + index_usage_stats.user_lookups) < 100
69 AND index_usage_stats.user_updates < 1000
70 THEN ''
71 WHEN (index_usage_stats.user_seeks + index_usage_stats.user_scans + index_usage_stats.user_lookups) < 100
72 AND index_usage_stats.user_updates >= 1000
73 THEN 'unused'
74 ELSE 'used'
75 END
76 ELSE 'keep'
77 END AS IndexStatus
78
79 ,CASE WHEN SUM(tbAllocation2.used_pages) < 1000
80 THEN ''
81 ELSE
82 CASE
83 WHEN index_physical_stats.avg_fragmentation_in_percent < 5 THEN ''
84 WHEN index_physical_stats.avg_fragmentation_in_percent BETWEEN 5 and 30 AND tbIndex2.type > 0 THEN 'reorganize'
85 WHEN index_physical_stats.avg_fragmentation_in_percent > 30 THEN 'rebuild'
86 ELSE '' END
87 END
88 AS IndexToDo
89 ,CASE WHEN SUM(tbAllocation2.used_pages) < 1000
90 THEN ''
91 ELSE
92 CASE WHEN tbIndex2.NAME IS NULL THEN
93 CASE
94 WHEN index_physical_stats.avg_fragmentation_in_percent > 30
95 THEN 'ALTER TABLE ['+tbSchema2.NAME+'].['+tbTable2.NAME+'] REBUILD;'
96 ELSE ''
97 END
98 ELSE
99 CASE
100 WHEN index_physical_stats.avg_fragmentation_in_percent < 5 THEN ''
101 WHEN index_physical_stats.avg_fragmentation_in_percent BETWEEN 5 and 30 THEN
102 'ALTER INDEX ['+tbIndex2.name+'] ON ['+tbSchema2.NAME+'].['+tbTable2.name+'] REORGANIZE PARTITION = ALL '
103 WHEN index_physical_stats.avg_fragmentation_in_percent > 30 THEN
104 'ALTER INDEX ['+tbIndex2.name+'] ON ['+tbSchema2.NAME+'].['+tbTable2.name+'] REBUILD PARTITION = ALL WITH ('
105 + 'FILLFACTOR =' + CAST(98 AS VARCHAR(3) )
106 +')'
107 ELSE '?'
108 END
109 END
110 END
111 AS IndexSqlToFix
112
113FROM sys.partitions AS tbPartition2
114INNER JOIN sys.indexes AS tbIndex2
115 ON tbPartition2.index_id = tbIndex2.index_id AND tbPartition2.object_id = tbIndex2.object_id
116INNER JOIN sys.schemas AS tbSchema2
117INNER JOIN sys.tables AS tbTable2
118 ON tbSchema2.schema_id = tbTable2.schema_id
119 ON tbPartition2.object_id = tbTable2.object_id
120 AND tbIndex2.object_id = tbTable2.object_id
121INNER JOIN sys.allocation_units AS tbAllocation2
122 ON tbPartition2.partition_id = tbAllocation2.container_id
123INNER JOIN sys.dm_db_index_usage_stats AS index_usage_stats
124 ON tbIndex2.object_id = index_usage_stats.object_id
125 AND tbIndex2.is_disabled = 0
126 AND tbIndex2.index_id = index_usage_stats.index_id
127INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS index_physical_stats
128 ON index_physical_stats.database_id = index_usage_stats.database_id
129 AND index_physical_stats.object_id = tbIndex2.object_id
130 AND index_physical_stats.index_id = tbIndex2.index_id
131GROUP BY
132 tbSchema2.NAME
133 ,tbTable2.NAME
134 ,tbTable2.object_id
135 ,tbIndex2.NAME
136 ,tbIndex2.index_id
137 ,index_usage_stats.user_seeks
138 ,index_usage_stats.user_scans
139 ,index_usage_stats.user_lookups
140 ,index_usage_stats.user_updates
141 ,tbIndex2.type
142 ,tbIndex2.type_desc
143 ,tbIndex2.is_unique
144 ,tbIndex2.is_primary_key
145 ,tbIndex2.is_padded
146 ,tbIndex2.ignore_dup_key
147 ,tbIndex2.fill_factor
148 ,tbIndex2.allow_page_locks
149 ,tbIndex2.allow_row_locks
150 ,index_physical_stats.database_id
151 ,index_physical_stats.index_depth
152 ,index_physical_stats.index_level
153 ,index_physical_stats.avg_fragmentation_in_percent
154 ,index_physical_stats.fragment_count
155 ,index_physical_stats.avg_fragment_size_in_pages
156 ,index_physical_stats.page_count
157) as tbIndexInfo
158
159ON tbTableInfo.TableName = tbIndexInfo.TableName
160AND tbTableInfo.SchemaName = tbIndexInfo.ShemaName
161WHERE IndexToDo <> ''
162
163DECLARE @sql NVARCHAR(MAX)
164
165WHILE ((SELECT COUNT(*) FROM @tb as t) > 0)
166BEGIN
167 SELECT TOP 1 @sql = IndexSqlToFix FROM @tb ORDER BY avg_fragmentation_in_percent DESC;
168 DELETE FROM @tb WHERE IndexSqlToFix = @sql;
169
170 RAISERROR (@sql, 0, 1) WITH NOWAIT;
171 EXEC(@sql);
172 WAITFOR DELAY '0:0:5'
173
174END
source :
http://sqlblog.com/ https://www.sqlservercentral.com/Forums/1873323/DMV-sysdmdbindexphysicalstats-Detailed-vs-Limited https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-2017 https://github.com/Microsoft/tigertoolbox/tree/master/AdaptiveIndexDefrag https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/tree/master https://www.idera.com/productssolutions/freetools/sqlfragmentationanalyzer https://www.apexsql.com/sql-tools-defrag.aspx
Comments