Summary

• Page properties (3 modified, 0 added, 0 removed)
• Objects (0 modified, 1 added, 0 removed)
  • XWiki.XWikiComments[0]

Details

Page properties

Parent

...	...	@@ -1,1 +1,1 @@
1		-Technical Documentation.analysis.sql-performance.WebHome
	1	+Technical Documentation.analysis.sql.sql-performance.WebHome

Author

...	...	@@ -1,1 +1,1 @@
1		-XWiki.rudim
	1	+XWiki.gideonl

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}}

XWiki.XWikiComments[0]

Author

...	...	@@ -1,0 +1,1 @@
	1	+XWiki.gideonl

Comment

...	...	@@ -1,0 +1,1 @@
	1	+{{mention reference="XWiki.rudim" style="FULL_NAME" anchor="XWiki-rudim-z3bw3b"/}} should these not be on the DBG schema?

Date

...	...	@@ -1,0 +1,1 @@
	1	+2022-12-03 05:28:56.355