Useful queries

 SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.cached_time  

FROM sys.procedures AS p WITH (NOLOCK)  

INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)  

ON p.[object_id] = qs.[object_id] WHERE qs.database_id = DB_ID()  

ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);  

-- This helps you find the most expensive cached  

-- stored procedures from a CPU perspective –  

SELECT TOP(25) p.name AS [SP Name],qs.total_physical_reads AS [TotalPhysicalReads],qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads], qs.execution_count, qs.total_logical_reads, qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.cached_time  

FROM sys.procedures AS p WITH (NOLOCK)  

INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)  

ON p.[object_id] = qs.[object_id]  

WHERE qs.database_id = DB_ID()  

AND qs.total_physical_reads > 0  

ORDER BY qs.total_physical_reads DESC, qs.total_logical_reads DESC OPTION (RECOMPILE); 

This helps you find the most expensive cached stored procedures from a read I/O perspective  

SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,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_id  

WHERE 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 > 1  

ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);  

-- Look for indexes with high numbers of writes and zero or very low numbers of reads  

-- Consider your complete workload 

 -- Investigate further before dropping an index!  

SELECT o.name, 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  

FROM 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_id  

INNER 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] = 'U'  

ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC OPTION (RECOMPILE);  

-- Helps discover possible problems with out-of-date statistics  

-- Also gives you an idea which indexes are most active.