SELECT CONVERT(DECIMAL(18, 2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage]    ,migs.last_user_seek    ,mid.[statement] AS [Database.Schema.Table]    ,mid.equality_columns    ,mid.inequality_columns    ,mid.included_columns    ,migs.unique_compiles    ,migs.user_seeks    ,migs.avg_total_user_cost    ,migs.avg_user_impactFROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handleINNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) ON mig.index_handle = mid.index_handleORDER BY index_advantage DESCOPTION (RECOMPILE);

SELECT DISTINCT CONVERT(DECIMAL(18, 2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage]    ,migs.last_user_seek    ,mid.[statement] AS [Database.Schema.Table]    ,mid.equality_columns    ,mid.inequality_columns    ,mid.included_columns    ,migs.unique_compiles    ,migs.user_seeks    ,migs.avg_total_user_cost    ,migs.avg_user_impact    ,OBJECT_NAME(mid.[object_id]) AS [Table Name]    ,p.rows AS [Table Rows]FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handleINNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) ON mig.index_handle = mid.index_handleINNER JOIN sys.partitions AS p WITH (NOLOCK) ON p.[object_id] = mid.[object_id]WHERE mid.database_id = DB_ID()ORDER BY index_advantage DESCOPTION (RECOMPILE);

SELECT TOP (25) OBJECT_NAME(objectid) AS [ObjectName]    ,query_plan    ,cp.objtype    ,cp.usecountsFROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qpWHERE CAST(query_plan AS NVARCHAR(MAX)) LIKE N‘%MissingIndex%‘    AND dbid = DB_ID()ORDER BY cp.usecounts DESCOPTION (RECOMPILE);

SELECT OBJECT_NAME(s.[object_id]) AS [Table Name]    ,i.[name] AS [Index Name]    ,i.index_id    ,i.is_disabled    ,i.is_hypothetical    ,i.has_filter    ,i.fill_factor    ,user_updates AS [Total Writes]    ,user_seeks + user_scans + user_lookups AS [Total Reads]    ,user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]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]    AND i.index_id = s.index_idWHERE OBJECTPROPERTY(s.[object_id], ‘IsUserTable‘) = 1    AND s.database_id = DB_ID()    AND user_updates > (user_seeks + user_scans + user_lookups)    AND i.index_id > 1ORDER BY [Difference] DESC    ,[Total Writes] DESC    ,[Total Reads] ASCOPTION (RECOMPILE);

SELECT SCHEMA_NAME(o.[schema_id]) + N‘.‘ + o.[name] AS [Object Name]    ,o.type_desc AS [Object Type]    ,i.[name] AS [Index Name]    ,STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date]    ,s.auto_created    ,s.no_recompute    ,s.user_created    ,st.row_count    ,st.used_page_countFROM sys.objects AS o WITH (NOLOCK)INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.[object_id] = i.[object_id]INNER JOIN sys.stats AS s WITH (NOLOCK) ON i.[object_id] = s.[object_id]    AND i.index_id = s.stats_idINNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK) ON o.[object_id] = st.[object_id]    AND i.[index_id] = st.[index_id]WHERE o.[type] IN (        ‘U‘        ,‘V‘        )    AND st.row_count > 0ORDER BY STATS_DATE(i.[object_id], i.index_id) DESCOPTION (RECOMPILE);

SELECT TableName = OBJECT_NAME(s.[object_id])    ,SchemaName = SCHEMA_NAME(o.[schema_id])    ,IndexName = i.[name]    ,user_updates    ,i.is_primary_keyFROM sys.dm_db_index_usage_stats sJOIN sys.objects O ON s.[object_id] = O.[object_id]JOIN sys.indexes i ON s.[object_id] = i.[object_id]    AND s.index_id = i.index_idWHERE OBJECTPROPERTY(s.[object_id], ‘IsMsShipped‘) = 0    AND user_seeks = 0    AND user_scans = 0    AND user_lookups = 0    AND i.NAME IS NOT NULL -- Ignore HEAP indexes.ORDER BY user_updates DESC

SELECT o.[name] AS [Object Name]    ,o.[object_id]    ,o.type_desc    ,s.[name] AS [Statistics Name]    ,s.stats_id    ,s.no_recompute    ,s.auto_created    ,sp.modification_counter    ,sp.rows    ,sp.rows_sampled    ,sp.last_updatedFROM sys.objects AS o WITH (NOLOCK)INNER JOIN sys.stats AS s WITH (NOLOCK) ON s.object_id = o.object_idCROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS spWHERE o.type_desc NOT IN (        N‘SYSTEM_TABLE‘        ,N‘INTERNAL_TABLE‘        )    AND sp.modification_counter > 0ORDER BY sp.modification_counter DESC    ,o.[name]OPTION (RECOMPILE);

SELECT DB_NAME(ps.database_id) AS [Database Name]    ,OBJECT_NAME(ps.[object_id]) AS [Object Name]    ,i.[name] AS [Index Name]    ,ps.index_id    ,ps.index_type_desc    ,ps.avg_fragmentation_in_percent    ,ps.fragment_count    ,ps.page_count    ,i.fill_factor    ,i.has_filter    ,i.filter_definitionFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N‘LIMITED‘) AS psINNER JOIN sys.indexes AS i WITH (NOLOCK) ON ps.[object_id] = i.[object_id]    AND ps.index_id = i.index_idWHERE ps.database_id = DB_ID()    AND ps.page_count > 2500ORDER BY ps.avg_fragmentation_in_percent DESCOPTION (RECOMPILE);

SELECT OBJECT_NAME(s.[object_id]) AS [ObjectName]    ,i.[name] AS [IndexName]    ,i.index_id    ,user_seeks + user_scans + user_lookups AS [Reads]    ,s.user_updates AS [Writes]    ,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_seekFROM 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]WHERE OBJECTPROPERTY(s.[object_id], ‘IsUserTable‘) = 1    AND i.index_id = s.index_id    AND s.database_id = DB_ID()ORDER BY user_seeks + user_scans + user_lookups DESCOPTION (RECOMPILE);

SELECT OBJECT_NAME(s.[object_id]) AS [ObjectName]    ,i.[name] AS [IndexName]    ,i.index_id    ,s.user_updates AS [Writes]    ,user_seeks + user_scans + user_lookups AS [Reads]    ,i.type_desc AS [IndexType]    ,i.fill_factor AS [FillFactor]    ,i.has_filter    ,i.filter_definition    ,s.last_system_update    ,s.last_user_updateFROM 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]WHERE OBJECTPROPERTY(s.[object_id], ‘IsUserTable‘) = 1    AND i.index_id = s.index_id    AND s.database_id = DB_ID()ORDER BY s.user_updates DESCOPTION (RECOMPILE);

SELECT TOP 25 obj.[name] AS TableName    ,i.[name] AS IndexName    ,i.[type_desc] AS IndexType    ,count(*) AS Buffered_Page_Count    ,count(*) * 8192 / (1024 * 1024) AS Buffer_MB    ,obj.index_idFROM sys.dm_os_buffer_descriptors AS bdINNER JOIN (    SELECT object_name(object_id) AS NAME        ,index_id        ,allocation_unit_id        ,object_id    FROM sys.allocation_units AS au    INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id        AND (            au.type = 1            OR au.type = 3            )        UNION ALL        SELECT object_name(object_id) AS NAME        ,index_id        ,allocation_unit_id        ,object_id    FROM sys.allocation_units AS au    INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id        AND au.type = 2    ) AS obj ON bd.allocation_unit_id = obj.allocation_unit_idLEFT JOIN sys.indexes i ON i.object_id = obj.object_id    AND i.index_id = obj.index_idWHERE database_id = db_id()GROUP BY obj.NAME    ,obj.index_id    ,i.[name]    ,i.[type_desc]ORDER BY Buffered_Page_Count DESC

SELECT OBJECT_SCHEMA_NAME(ios.object_id) + ‘.‘ + OBJECT_NAME(ios.object_id) AS table_name    ,i.[name] AS index_name    ,page_io_latch_wait_count    ,page_io_latch_wait_in_ms    ,CAST(1. * page_io_latch_wait_in_ms / NULLIF(page_io_latch_wait_count, 0) AS DECIMAL(12, 2)) AS page_io_avg_lock_wait_ms    ,page_latch_wait_count    ,page_latch_wait_in_ms    ,CAST(1. * page_latch_wait_in_ms / NULLIF(page_latch_wait_count, 0) AS DECIMAL(12, 2)) AS page_avg_lock_wait_msFROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) iosINNER JOIN sys.indexes i ON i.object_id = ios.object_id    AND i.index_id = ios.index_idWHERE OBJECTPROPERTY(ios.object_id, ‘IsUserTable‘) = 1ORDER BY 3 DESC