--Missing Index query
SELECT TOP 20
ROUND(s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans),0) AS [Total Cost],
d.[statement] AS [Table Name],
equality_columns,
inequality_columns,
included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC
--index fragmentation
SELECT object_name(IPS.object_id) AS [TableName],
SI.name AS [IndexName],
IPS.Index_type_desc,
IPS.avg_fragmentation_in_percent,
IPS.fragment_count,
IPS.avg_fragment_size_in_pages,
alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats(db_id(N'AdventureWorks2012'), NULL, NULL, NULL , 'LIMITED') IPS
JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0 and index_type_desc in ('NONCLUSTERED INDEX','CLUSTERED INDEX')
and (fragment_count * avg_fragment_size_in_pages) > 20
ORDER BY avg_fragmentation_in_percent desc
SELECT object_name(IPS.object_id) AS [TableName],
SI.name AS [IndexName],
IPS.Index_type_desc,
IPS.avg_fragmentation_in_percent,
IPS.avg_page_space_used_in_percent,
IPS.record_count,
IPS.ghost_record_count,
IPS.fragment_count,
IPS.avg_fragment_size_in_pages,
alloc_unit_type_desc,
index_level
FROM sys.dm_db_index_physical_stats(db_id(N'AdventureWorks2012'), NULL, NULL, NULL , 'DETAILED') IPS
JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0 and (fragment_count * avg_fragment_size_in_pages) > 20
and index_type_desc in ('NONCLUSTERED INDEX','CLUSTERED INDEX') and index_level<>0
ORDER BY avg_fragmentation_in_percent desc
--1: Page life expectancy: If its value is low (below 300 seconds), this is a clear indication of memory pressure
--2: Free list stalls/sec: If above 2, consider adding memory to the server
--3: Page reads/sec: The recommended Page reads/sec value should be under 90. Higher values indicate insufficient memory and indexing issues
SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] in ('Page life expectancy','Free list stalls/sec','Page reads/sec')