Wiki source code of index-maintenance
Version 4.1 by Rudi Marais on 2022/02/10 15:33
Show last authors
| author | version | line-number | content |
|---|---|---|---|
| 1 | {{toc/}} | ||
| 2 | |||
| 3 | |||
| 4 | Install these scripts from the maintenance package at [[https:~~/~~/ola.hallengren.com/>>https://ola.hallengren.com/]] | ||
| 5 | |||
| 6 | 1. [[CommandExecute.sql>>https://github.com/olahallengren/sql-server-maintenance-solution/blob/master/CommandExecute.sql]] | ||
| 7 | 1. [[CommandLog.sql>>https://github.com/olahallengren/sql-server-maintenance-solution/blob/master/CommandLog.sql]] | ||
| 8 | 1. [[IndexOptimize.sql>>https://github.com/olahallengren/sql-server-maintenance-solution/blob/master/IndexOptimize.sql]] | ||
| 9 | |||
| 10 | Documentation for the index maintenance methods can be found here: [[SQL Server Index and Statistics Maintenance (hallengren.com)>>url:https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html]] | ||
| 11 | |||
| 12 | |||
| 13 | == Fragmented Index & Statistics == | ||
| 14 | |||
| 15 | {{code language="sql"}} | ||
| 16 | EXECUTE dbo.IndexOptimize | ||
| 17 | @Databases = 'Everest_PreProd', | ||
| 18 | @FragmentationLow = NULL, | ||
| 19 | @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', | ||
| 20 | @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', | ||
| 21 | @FragmentationLevel1 = 5, | ||
| 22 | @FragmentationLevel2 = 30, | ||
| 23 | @UpdateStatistics = 'ALL', | ||
| 24 | @FillFactor = 80, | ||
| 25 | @Execute = 'Y', | ||
| 26 | @LogToTable = 'Y' | ||
| 27 | {{/code}} | ||
| 28 | |||
| 29 | == Unused indexes == | ||
| 30 | |||
| 31 | {{code language="sql"}} | ||
| 32 | |||
| 33 | SELECT | ||
| 34 | schema_name(objects.schema_id) schema_name, | ||
| 35 | objects.name AS Table_name, | ||
| 36 | indexes.name AS Index_name, | ||
| 37 | dm_db_index_usage_stats.user_seeks, | ||
| 38 | dm_db_index_usage_stats.user_scans, | ||
| 39 | dm_db_index_usage_stats.user_updates | ||
| 40 | FROM | ||
| 41 | sys.dm_db_index_usage_stats | ||
| 42 | INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID | ||
| 43 | INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID | ||
| 44 | WHERE | ||
| 45 | indexes.is_primary_key = 0 --This line excludes primary key constarint | ||
| 46 | AND | ||
| 47 | indexes.is_unique = 0 --This line excludes unique key constarint | ||
| 48 | AND | ||
| 49 | dm_db_index_usage_stats.user_updates <> 0 -- This line excludes indexes SQL Server hasn’t done any work with | ||
| 50 | AND | ||
| 51 | dm_db_index_usage_stats.user_lookups = 0 | ||
| 52 | AND | ||
| 53 | dm_db_index_usage_stats.user_seeks = 0 | ||
| 54 | AND | ||
| 55 | dm_db_index_usage_stats.user_scans = 0 | ||
| 56 | ORDER BY | ||
| 57 | dm_db_index_usage_stats.user_updates DESC | ||
| 58 | |||
| 59 | {{/code}} |