Cacher is the code snippet organizer for pro developers

SELECT  
	[Impact] = (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans),  
	[Table] = [statement],
	[CreateIndexStatement] = 'CREATE NONCLUSTERED INDEX ix_' 
		+ sys.objects.name COLLATE DATABASE_DEFAULT 
		+ '_' 
		+ REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,'')+ISNULL(mid.inequality_columns,''), '[', ''), ']',''), ', ','_')
		+ ' ON ' 
		+ [statement] 
		+ ' ( ' + IsNull(mid.equality_columns, '') 
		+ CASE WHEN mid.inequality_columns IS NULL THEN '' ELSE 
			CASE WHEN mid.equality_columns IS NULL THEN '' ELSE ',' END 
		+ mid.inequality_columns END + ' ) ' 
		+ CASE WHEN mid.included_columns IS NULL THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END 
		+ ';', 
	mid.equality_columns,
	mid.inequality_columns,
	mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs 
	INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle 
	INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle 
	INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID 
WHERE (migs.group_handle IN 
		(SELECT TOP (500) group_handle 
		FROM sys.dm_db_missing_index_group_stats WITH (nolock) 
		ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))  
	AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable') = 1 
ORDER BY [Impact] DESC , [CreateIndexStatement] DESC

SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +
       CASE WHEN ps.avg_fragmentation_in_percent > 40 THEN 'REBUILD' ELSE 'REORGANIZE' END +
       CASE WHEN pc.partition_count > 1 THEN ' PARTITION = ' + cast(ps.partition_number as nvarchar(max)) ELSE '' END
FROM   sys.indexes AS ix INNER JOIN sys.tables t
           ON t.object_id = ix.object_id
       INNER JOIN sys.schemas s
           ON t.schema_id = s.schema_id
       INNER JOIN (SELECT object_id, index_id, avg_fragmentation_in_percent, partition_number
                   FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)) ps
           ON t.object_id = ps.object_id AND ix.index_id = ps.index_id
       INNER JOIN (SELECT object_id, index_id, COUNT(DISTINCT partition_number) AS partition_count
                   FROM sys.partitions
                   GROUP BY object_id, index_id) pc
           ON t.object_id = pc.object_id AND ix.index_id = pc.index_id
WHERE  ps.avg_fragmentation_in_percent > 10 AND
       ix.name IS NOT NULL

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent, page_count 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind  
ON ind.object_id = indexstats.object_id 
AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent > 30 
ORDER BY indexstats.avg_fragmentation_in_percent DESC

--CRM manual reindex
--AllIndexTypes, Default=0; 0=Clustered Index only, 1=Clustered and Non Clustered indexes
--MaxRunTime, NO Default;  Maximum allowed running time (in seconds)
--FragRebuildPct, Default=30;  Percentage of fragmentation at which indexes are rebuilt
--MinPages, Default=25; Avoid tables less than MinPages amount of pages  
--Verbose, Default=0; 1=Print progress messages and detailed results
DECLARE @RC int
DECLARE @AllIndexTypes int
DECLARE @MaxRunTime int
DECLARE @FragRebuildPct int
DECLARE @MinPages int
DECLARE @Verbose int
DECLARE @Online bit

SELECT 
@AllIndexTypes = 1
,@FragRebuildPct = 15
,@MinPages = 20
,@Verbose = 1

-- TODO: Set parameter values here.

EXECUTE @RC = [dbo].[p_ReindexAll] 
   @AllIndexTypes
  ,@MaxRunTime
  ,@FragRebuildPct
  ,@MinPages
  ,@Verbose
  ,@Online
GO

 /* GET ALL SUGGESTED INDEXES */
 
 SELECT
  d.[object_id],
  s = OBJECT_SCHEMA_NAME(d.[object_id]),
  o = OBJECT_NAME(d.[object_id]),
  d.equality_columns,
  d.inequality_columns,
  d.included_columns,
  s.unique_compiles,
  s.user_seeks, s.last_user_seek,
  s.user_scans, s.last_user_scan
INTO #candidates
FROM sys.dm_db_missing_index_details AS d
INNER JOIN sys.dm_db_missing_index_groups AS g
ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats AS s
ON g.index_group_handle = s.group_handle
WHERE d.database_id = DB_ID()
AND OBJECTPROPERTY(d.[object_id], 'IsMsShipped') = 0;

SELECT * FROM #candidates

/* GET INDEX USAGE STATS */
SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
         I.[NAME] AS [INDEX NAME], 
         USER_SEEKS, 
         USER_SCANS, 
         USER_LOOKUPS, 
         USER_UPDATES --these are inserts, updates, deletes to maintain index 
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S 
         INNER JOIN SYS.INDEXES AS I 
           ON I.[OBJECT_ID] = S.[OBJECT_ID] 
              AND I.INDEX_ID = S.INDEX_ID 
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1