/* Asagidaki iki sorguyu, farkli query pencerelirinde acip index iki sorgu sonrasinda karar ver */
1- SELECT MID.[statement] AS ObjectName , MID.equality_columns AS EqualityColumns , MID.inequality_columns AS InequalityColms , MID.included_columns AS IncludedColumns , MIGS.last_user_seek AS LastUserSeek , MIGS.avg_total_user_cost * MIGS.avg_user_impact * (MIGS.user_seeks + MIGS.user_scans) AS Impact , 'CREATE INDEX [IX_' + OBJECT_NAME(MID.object_id,MID.database_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(MID.equality_columns,''),', ','_'),'[',''),']','') + CASE WHEN MID.equality_columns IS NOT NULL AND MID.inequality_columns IS NOT NULL THEN '_' ELSE '' END + REPLACE(REPLACE(REPLACE(ISNULL(MID.inequality_columns,''),', ','_'),'[',''),']','') + '_CT]' + ' ON ' + MID.statement + ' (' + ISNULL (MID.equality_columns,'') + CASE WHEN MID.equality_columns IS NOT NULL AND MID.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (MID.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + MID.included_columns + ')', '') +' WITH(ONLINE=ON,FILLFACTOR=99,MAXDOP=8,DATA_COMPRESSION=PAGE)ON[OrganizerIdxFG]' AS Create_Statement 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 WHERE database_id = DB_ID() AND MIGS.last_user_seek >= DATEDIFF(month, GetDate(), -1) ORDER BY ObjectName,EqualityColumns,InequalityColms, Impact DESC;
2- WITH XMLNAMESPACES (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' ,N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ShowPlan) SELECT ECP.[usecounts] AS [UsageCounts] ,ECP.[refcounts] AS [RefencedCounts] ,ECP.[objtype] AS [ObjectType] ,ECP.[cacheobjtype] AS [CacheObjectType] ,EST.[dbid] AS [DatabaseID] ,DB_NAME(EST.dbid) AS DB ,EST.[objectid] AS [ObjectID] ,EST.[text] AS [Statement] ,EQP.[query_plan] AS [QueryPlan] FROM sys.dm_exec_cached_plans AS ECP CROSS APPLY sys.dm_exec_sql_text(ECP.[plan_handle]) AS EST CROSS APPLY sys.dm_exec_query_plan(ECP.[plan_handle]) AS EQP WHERE ECP.[usecounts] > 1 -- Plan should be used more then one time (= no AdHoc queries) AND EQP.[query_plan].exist(N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup') <> 0 ORDER BY ECP.[usecounts] DESC
