Summary

• Page properties (1 modified, 0 added, 0 removed)

Details

Page properties

Content

...	...	@@ -1,4 +1,4 @@
1		-
	1	+{{toc/}}
2	2
3	3
4	4	Install these scripts from the maintenance package at [[https:~~/~~/ola.hallengren.com/>>https://ola.hallengren.com/]]
...	...	@@ -7,10 +7,11 @@
7	7	1. [[CommandLog.sql>>https://github.com/olahallengren/sql-server-maintenance-solution/blob/master/CommandLog.sql]]
8	8	1. [[IndexOptimize.sql>>https://github.com/olahallengren/sql-server-maintenance-solution/blob/master/IndexOptimize.sql]]
9	9
10		-
11	11	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]]
12	12
13	13
	13	+== Fragmented Index & Statistics ==
	14	+
14	14	{{code language="sql"}}
15	15	EXECUTE dbo.IndexOptimize
16	16	@Databases = 'Everest_PreProd',
...	...	@@ -24,3 +24,37 @@
24	24	@Execute = 'Y',
25	25	@LogToTable = 'Y'
26	26	{{/code}}
	28	+
	29	+== Unused indexes ==
	30	+
	31	+{{code language="sql"}}
	32	+
	33	+--// https://www.sqlshack.com/how-to-identify-and-monitor-unused-indexes-in-sql-server/
	34	+
	35	+SELECT
	36	+ schema_name(objects.schema_id) schema_name,
	37	+ objects.name AS Table_name,
	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
	49	+ indexes.is_unique = 0 --This line excludes unique key constarint
	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}}