Identifying Missing indexes using DMV and other useful DMVs
http://www.sql-server-performance.com/articles/per/Identify_Missing_Indexes_Using_SQL_Server_DMVs_p1.aspx
http://blogs.msdn.com/b/queryoptteam/archive/2006/04/06/570176.aspx
http://blogs.msdn.com/b/heynen/archive/2008/06/28/best-practices-for-dynamic-management-views.aspx
Top 10 worst CPU consuming queries on the SQL Server (since the last time the server was restarted)
SELECT TOP 10
sql.text as sql
, qp.query_plan
, creation_time
, last_execution_time
, execution_count
, (total_worker_time / execution_count) as avg_cpu
, total_worker_time as total_cpu
, last_worker_time as last_cpu
, min_worker_time as min_cpu
, max_worker_time as max_cpu
, (total_physical_reads + total_logical_reads) as total_reads
, (max_physical_reads + max_logical_reads) as max_reads
, (total_physical_reads + total_logical_reads) / execution_count as avg_reads
, max_elapsed_time as max_duration
, total_elapsed_time as total_duration
, ((total_elapsed_time / execution_count)) / 1000000 as avg_duration_sec
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql
CROSS APPLY sys.dm_exec_query_plan (plan_handle) qp
ORDER BY qs. total_worker_time DESC
Best practices and bottleneck identification
http://www.sqlserver-easy.com/content/view/33/44/
Performance dashboard query:
SELECT migs.avg_user_impact,
*
FROM sys.dm_db_missing_index_details AS mid
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON mig.index_handle = mid.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats AS migs
ON mig.index_group_handle=migs.group_handle
ORDER BY migs.avg_user_impact desc
http://searchsqlserver.techtarget.com/resources/Microsoft-SQL-Server-Performance-Monitoring-and-Tuning?adg=301324&bucket=ETA
Finding the indexes not used:
select i.name as indexname,
object_name(s.[object_id]) as tablename, s.database_id, s.[object_id], s.User_seeks, last_user_seek, user_scans, last_user_scan,
user_lookups, Last_user_lookup, user_updates, last_user_update
from sys.dm_db_index_usage_stats s
join sys.indexes i on s.object_id = i.object_id
and s.index_id = i.index_id
where OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
and user_scans + user_lookups + user_seeks = 0
