Need statistics of whole database in one view? My script uses DMVs instead of the deprecated sp_helpstats and shows each stat, column and last update.
Finding the Slowest Stored Procedures With sys.dm_exec_procedure_stats
Find the slowest stored procedures with cache statistics, compare CPU and reads, and use two snapshots to isolate one busy hour.
SQL SERVER – Pending IO request in SQL Server – DMV
How many pending IO requests are there for each MDF and LDF file? My handy script joins sys.dm_io_pending_io_requests with virtual file stats.
SQL SERVER – CXPACKET Parallelism Advanced Solution – Wait Type – Day 7 of 28
Beyond MAXDOP and cost threshold, my CXPACKET parallelism advanced solution looks at other causes, like skewed data and threads stuck on IO, memory or CPU.
SQL SERVER – CXPACKET – Parallelism – Usual Solution – Wait Type – Day 6 of 28
CXPACKET is one of the top wait stats on most multi-CPU systems. Day 6 explains why parallel queries create it and the usual fix with MAXDOP.
SQL SERVER – Capturing Wait Types and Wait Stats Information at Interval – Wait Type – Day 5 of 28
Wait stats reset when services restart. Day 5 shows my method for capturing wait types and wait stats in a table at intervals to compare them later.
SQL SERVER – DMV – sys.dm_os_waiting_tasks and sys.dm_exec_requests – Wait Type – Day 4 of 28
sys.dm_os_wait_stats shows the instance-level picture. This query joins sys.dm_os_waiting_tasks and sys.dm_exec_requests to see what waits right now.