/*
Server:		ssunb008vm01.sunstore.ch
Format:		GCM 
Business:	TPPHAR
type:		DEVE
Version:	23.1.10016.00066

01.11.2022, TSC
*/
USE Arizona

;WITH ColInfo AS 
(	SELECT   TblName = o.name
			,SchemaTbl = s.name +  '.' + o.name
			,IndexName = i.name
			,IsPrimaryKey = i.is_primary_key
			,IsUniqueConstraint = i.is_unique_constraint
			,IsUnique = i.is_unique
			,ColName = c.name
			,IsComputedCol = c.is_computed
			,IsIncludedCol = ic.is_included_column
			,ic.key_ordinal
			,FilterDefinition = i.filter_definition
	FROM sys.objects o 
		INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
		INNER JOIN sys.columns c ON o.object_id = c.object_id
		INNER JOIN sys.indexes i ON c.object_id = i.object_id
		INNER JOIN sys.index_columns ic ON i.index_id = ic.index_id AND o.object_id = ic.object_id AND c.column_id = ic.column_id
)


--- Index Read/Write stats (all tables in current DB) ordered by Reads  (Query 62) (Overall Index Usage - Reads)
SELECT	tblName = o.name
		,o.type
	   ,i.name AS [IndexName]
	   ,i.index_id
	   ,user_seeks + user_scans + user_lookups AS [Reads]
	   ,s.user_updates AS [Writes]
	   , CASE
			WHEN s.user_updates > user_seeks + user_scans + user_lookups THEN s.user_updates - (user_seeks + user_scans + user_lookups)
			ELSE (user_seeks + user_scans + user_lookups) - s.user_updates
		END AS delta
	   ,i.type_desc AS [IndexType]
	   ,i.fill_factor AS [FillFactor]
	   ,i.has_filter
	   ,i.filter_definition
	   ,s.last_user_scan
	   ,s.last_user_lookup
	   ,s.last_user_seek
	   ,idx_cols.IndexColumns
	   ,idx_cols.IncludedColumns
FROM	sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
		INNER JOIN sys.indexes AS i WITH (NOLOCK) ON  s.object_id = i.object_id
		INNER JOIN sys.objects o WITH (NOLOCK) ON s.object_id = o.object_id
		OUTER APPLY (
			SELECT DISTINCT 
					x.TblName
				   ,x.IndexName
				   ,x.IsPrimaryKey
				   ,x.IsUniqueConstraint
				   ,x.IsUnique
				   --,size.IndexSizeKB
				   ,c.IndexColumns
				   ,ci.IncludedColumns
				   ,cc.ComputedColumns
				   ,x.FilterDefinition
			FROM ColInfo x
			CROSS APPLY(SELECT	IndexColumns = STUFF(sq.strXML, 1, 2, '')
						FROM (	SELECT	', ' + ISNULL(x2.ColName, '')
								FROM	ColInfo x2
								WHERE	x.TblName = x2.TblName AND x.IndexName = x2.IndexName AND x2.IsIncludedCol = 0
								ORDER	BY x2.key_ordinal
								FOR XML PATH('')
							) sq (strXML)
					) c
			Outer APPLY(SELECT	IncludedColumns = STUFF(sq.strXML, 1, 2, '')
						FROM (	SELECT	', ' + ISNULL(x2.ColName, '')
								FROM	ColInfo x2
								WHERE	x.TblName = x2.TblName AND x.IndexName = x2.IndexName AND x2.IsIncludedCol = 1
								ORDER	BY x2.key_ordinal
								FOR XML PATH('')
							) sq (strXML)
					) ci
			Outer APPLY(SELECT	ComputedColumns = STUFF(sq.strXML, 1, 2, '')
						FROM (	SELECT	', ' + ISNULL(x2.ColName, '')
								FROM	ColInfo x2
								WHERE	x.TblName = x2.TblName AND x.IndexName = x2.IndexName AND x2.IsComputedCol = 1
								ORDER	BY x2.key_ordinal
								FOR XML PATH('')
							) sq (strXML)
					) cc
			WHERE x.TblName = o.name
			AND x.IndexName = i.name

		)idx_cols
WHERE	o.type = 'U' -- user table
	AND i.index_id = s.index_id
	AND s.database_id = DB_ID()
	AND o.name = 'entry'
	--AND i.name IN
	--	(
	--	N'NCIX_Entry_COL_ET_accounting_period',
	--	N'NCIX_Entry_COL_ET_entry_type',
	--	N'NCIX_Entry_COL_ET_predefined_entry',
	--	N'NCIX_Entry_COL_ET_bmc_user_profile',
	--	N'NCIX_Entry_COL_ET_currency',
	--	N'NCIX_Entry_COL_ET_entry_address'
	--	)

ORDER BY delta DESC 
--ORDER BY user_seeks + user_scans + user_lookups DESC  -- Order by reads
--ORDER BY s.user_updates DESC OPTION   				 -- Order by writes
OPTION	(RECOMPILE);