Wiki source code of index-maintenance
Version 6.3 by Gideon Lombard on 2023/05/18 05:52
Hide last authors
| author | version | line-number | content |
|---|---|---|---|
 |
2.1 | 1 | {{toc/}} |
| |
1.1 | 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 | |||
| |
2.1 | 13 | == Fragmented Index & Statistics == |
| 14 | |||
| |
1.1 | 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}} | ||
| |
2.1 | 28 | |
| 29 | == Unused indexes == | ||
| 30 | |||
| 31 | {{code language="sql"}} | ||
| 32 | |||
| |
6.1 | 33 | --// https://www.sqlshack.com/how-to-identify-and-monitor-unused-indexes-in-sql-server/ |
| 34 | |||
| |
2.1 | 35 | SELECT |
| |
3.1 | 36 | schema_name(objects.schema_id) schema_name, |
| 37 | objects.name AS Table_name, | ||
| |
2.1 | 38 | indexes.name AS Index_name, |
| 39 | dm_db_index_usage_stats.user_seeks, | ||
| 40 | dm_db_index_usage_stats.user_scans, | ||
| 41 | dm_db_index_usage_stats.user_updates | ||
| 42 | FROM | ||
| 43 | sys.dm_db_index_usage_stats | ||
| 44 | INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID | ||
| 45 | 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 | ||
| 46 | WHERE | ||
| 47 | indexes.is_primary_key = 0 --This line excludes primary key constarint | ||
| 48 | AND | ||
| |
4.1 | 49 | indexes.is_unique = 0 --This line excludes unique key constarint |
| |
2.1 | 50 | AND |
| 51 | dm_db_index_usage_stats.user_updates <> 0 -- This line excludes indexes SQL Server hasn’t done any work with | ||
| 52 | AND | ||
| 53 | dm_db_index_usage_stats.user_lookups = 0 | ||
| 54 | AND | ||
| 55 | dm_db_index_usage_stats.user_seeks = 0 | ||
| 56 | AND | ||
| 57 | dm_db_index_usage_stats.user_scans = 0 | ||
| 58 | ORDER BY | ||
| 59 | dm_db_index_usage_stats.user_updates DESC | ||
| 60 | |||
| 61 | {{/code}} |