/*

-- Order by Index Usage in Minion Reindex -- 

!!! Read all comments before running this code !!!

Disclaimer: 
	This code is provided as-is with no warranties on performance or accuracy in your environment.

Summary:
	This code allows you to order the processing of your tables in the order of the most used indexes 
	to the least used indexes.

	This is grouped at the table level, as we're currently not able to make changes at the index level.  
	However, we add the seeks and scans for all the indexes per table. So, while you can't get the exact 
	ordering of indexes by usage, you can definitely make sure that the most used tables are at the top.  
	It will end up being extremely close to your goal.

Instructions:

	1. Replace the name of the test database [Demo] with the name of your target database.

	2. Replace the name of the default Minion Reindex database [master] with the name of the
	   database where you have Minion Reindex installed.

	3. Implement the code in one of two ways. Either: 
		* Create a new step in the reindexing job, to run before the reindex, or
		* turn it into an SP (for example, MyMinion.OrderMyDB) and run it as a DBPreCode step. 


Discussion:
	
	This process lets you make a dynamic index maintenance ordering, based off of which tables are 
	highest in usage that day. The concept is simple: get the order of index usage, and insert/update 
	a row with the proper order for each one.
	
	This process will insert new rows to the "Minion.IndexSettingsTable" table using the settings 
	from default (or, if they exist, the settings specified for that particular database). 

	For tables that already have a row in the "Minion.IndexSettingsTable" table, this process will 
	update the ReindexGroupOrder, but it will NOT adjust other settings; we wouldn't to overwrite 
	existing custom table configurations. That means that your existing custom table level configuration 
	will stay the same; but, if you change settings (say, FF) at the database level, it won't apply to 
	these tables; they have their own configuration now.

	This can be easily adapted to alter any number of settings dynamically, and it can be easily adapted 
	to dynamically alter the "Minion.IndexSettingsDB" table as well.

*/

USE [Demo];
GO

/*** Get tables, index ordering (based on table seeks+scans+lookups), and default-level maintenance settings. ***/
SELECT	ReindexGroupOrder = IDENTITY( INT,1,1 ),	/* Used to order index ops; higher numbers are indexed first. */
		DB_NAME() AS DBName ,
		SCHEMA_NAME(SO.schema_id) AS SchemaName ,
		OBJECT_NAME(SO.object_id) AS TableName ,
		SUM(US.user_seeks + US.user_scans + US.user_lookups) AS userSeeks ,
		I.Exclude ,
		I.ReindexOrder ,
		I.ReorgThreshold ,
		I.RebuildThreshold ,
		I.FILLFACTORopt ,
		I.PadIndex ,
		I.ONLINEopt ,
		I.SortInTempDB ,
		I.DataCompression ,
		I.GetRowCT ,
		I.GetPostFragLevel ,
		I.UpdateStatsOnDefrag ,
		I.LogIndexPhysicalStats ,
		I.IndexScanMode ,
		I.LogProgress ,
		I.LogRetDays ,
		I.IncludeUsageDetails
INTO	#T
FROM	[Demo].sys.dm_db_index_usage_stats US
		INNER JOIN [Demo].sys.objects SO ON US.object_id = SO.object_id
		INNER JOIN [master].Minion.IndexSettingsDB AS I ON I.DBName = 'MinionDefault'
WHERE	DB_ID(DB_NAME()) = US.database_id
		AND OBJECT_NAME(US.object_id) IS NOT NULL
GROUP BY SCHEMA_NAME(SO.schema_id) ,
		OBJECT_NAME(SO.object_id) ,
		I.Exclude ,
		I.ReindexOrder ,
		I.ReorgThreshold ,
		I.RebuildThreshold ,
		I.FILLFACTORopt ,
		I.PadIndex ,
		I.ONLINEopt ,
		I.SortInTempDB ,
		I.DataCompression ,
		I.GetRowCT ,
		I.GetPostFragLevel ,
		I.UpdateStatsOnDefrag ,
		I.LogIndexPhysicalStats ,
		I.IndexScanMode ,
		I.LogProgress ,
		I.LogRetDays ,
		I.IncludeUsageDetails
ORDER BY userseeks ASC

/*** If there are database-level settings, update the table settings with those. ***/
UPDATE	#T
SET		Exclude = I.Exclude ,
		ReindexOrder = I.ReindexOrder ,
		ReorgThreshold = I.ReorgThreshold ,
		RebuildThreshold = I.RebuildThreshold ,
		FILLFACTORopt = I.FILLFACTORopt ,
		PadIndex = I.PadIndex ,
		ONLINEopt = I.ONLINEopt ,
		SortInTempDB = I.SortInTempDB ,
		DataCompression = I.DataCompression ,
		GetRowCT = I.GetRowCT ,
		GetPostFragLevel = I.GetPostFragLevel ,
		UpdateStatsOnDefrag = I.UpdateStatsOnDefrag ,
		LogIndexPhysicalStats = I.LogIndexPhysicalStats ,
		IndexScanMode = I.IndexScanMode ,
		LogProgress = I.LogProgress ,
		LogRetDays = I.LogRetDays ,
		IncludeUsageDetails = I.IncludeUsageDetails
FROM	[master].Minion.IndexSettingsDB AS I
WHERE	I.DBName = DB_NAME();

--	SELECT * FROM #T
--	SELECT db_name(database_id), * FROM sys.dm_db_index_usage_stats 


/*** Update existing rows with the new ReindexGroupOrder ***/
IF ( SELECT	COUNT(*)
	 FROM	[master].Minion.IndexSettingsTable AS I
			JOIN #T AS T ON T.DBName = I.DBName
							AND T.SchemaName = I.SchemaName
							AND T.TableName = I.TableName
   ) > 0 
	BEGIN 
		UPDATE	[master].Minion.IndexSettingsTable
		SET		ReindexGroupOrder = T.ReindexGroupOrder
		FROM	#T AS T
		WHERE	T.DBName = DB_NAME()
				AND [master].Minion.IndexSettingsTable.SchemaName = T.SchemaName
				AND [master].Minion.IndexSettingsTable.TableName = T.TableName
	END

/*** Insert new rows with ReindexGroupOrder and default or DB level settings ***/
INSERT	[master].Minion.IndexSettingsTable
		( [DBName] ,
		  [SchemaName] ,
		  [TableName] ,
		  [Exclude] ,
		  [ReindexGroupOrder] ,
		  [ReindexOrder] ,
		  [ReorgThreshold] ,
		  [RebuildThreshold] ,
		  [FILLFACTORopt] ,
		  [PadIndex] ,
		  [ONLINEopt] ,
		  [SortInTempDB] ,
		  [DataCompression] ,
		  [GetRowCT] ,
		  [GetPostFragLevel] ,
		  [UpdateStatsOnDefrag] ,
		  [LogIndexPhysicalStats] ,
		  [IndexScanMode] ,
		  [LogProgress] ,
		  [LogRetDays] ,
		  [IncludeUsageDetails]
						
		)
		SELECT	T.DBName ,
				T.SchemaName ,
				T.TableName ,
				T.Exclude ,
				T.ReindexGroupOrder ,
				T.ReindexOrder ,
				T.ReorgThreshold ,
				T.RebuildThreshold ,
				T.FillFactorOpt ,
				T.PadIndex ,
				T.ONLINEopt ,
				T.SortInTempDB ,
				T.DataCompression ,
				T.GetRowCT ,
				T.GetPostFragLevel ,
				T.UpdateStatsOnDefrag ,
				T.LogIndexPhysicalStats ,
				T.IndexScanMode ,
				T.LogProgress ,
				T.LogRetDays ,
				T.IncludeUsageDetails
		FROM	#T AS T
				LEFT OUTER JOIN [master].Minion.IndexSettingsTable I ON T.DBName = I.DBName
															  AND T.SchemaName = I.SchemaName
															  AND T.TableName = I.TableName
		WHERE	I.DBName IS NULL;
		
		GO
DROP TABLE #T;


--SELECT	*
--FROM	[master].Minion.IndexSettingsTable