-- Find missing indexes (DMV) SELECT * FROM sys.dm_db_missing_index_details; -- Most expensive queries by avg CPU SELECT TOP 10 qs.total_worker_time / qs.execution_count AS avg_cpu, st.text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st ORDER BY avg_cpu DESC;
-- Fragmented indexes >30% SELECT OBJECT_NAME(ips.object_id), ips.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips WHERE ips.avg_fragmentation_in_percent > 30; sql server developer