SQL Aloha

Free SQL Server DMV Starter Pack

The SQL Server DMV Starter Pack is a free eBook (84 pages) that describes 28 different ways that DMVs can be used with SQL Server. Not only is each way described in detail, but sample code is included so you can begin using the DMVs immediately. The book covers these uses of DMVs.

• DMV#1: Are you Connected?
• DMV#2: Session Ownership
• DMV#3: Current expensive, or blocked, requests
• DMV#4: Query Stats – Find the "top X" most expensive cached queries
• DMV#5: How many single-use ad-hoc Plans?
• DMV#6: Ad-hoc queries and the plan cache
• DMV#7: Investigate expensive cached stored procedures
• DMV#8: Find Queries that are waiting, or have waited, for a Memory Grant
• DMV#9: Monitor long-running transactions
• DMV#10: Identify locking and blocking issues
• DMV#11: Find Missing Indexes
• DMV#12: Interrogate Index Usage
• DMV#13: Table Storage Stats (Pages and Row Counts)
• DMV#14: Monitor TempDB
• DMV#15: Investigate Disk Bottlenecks via I/O Stalls
• DMV#16: Investigate Disk Bottlenecks via Pending I/O
• DMV#17: Why are we Waiting?
• DMV#18: Expose Performance Counters
• DMV#19: Basic CPU Configuration
• DMV#20: CPU Utilization History
• DMV#21: Monitor Schedule activity
• DMV#22: System-wide Memory Usage
• DMV#23: Detect Memory Pressure
• DMV#24: Investigate Memory Usage Across all Caches
• DMV#25: Investigate memory use in the Buffer Pool
• DMV#26: Rooting out Unruly CLR Tasks
• DMV#27: Full Text Search
• DMV#28: Page Repair attempts in Database Mirroring

The book’s authors include Glenn Berry (Blog | Twitter), Louis Davidson (Blog | Twitter), and Tim Ford (Blog | Twitter), and you can download the eBook and scripts here.

1. Checkout SQLServerIO.com for Useful Information on SQL Server Storage Systems
2. Two Blog Series You Don’t Want to Miss
3. SQL Server Monitoring Checklist
4. Interview with SQL Server MVP Glenn Berry
5. SQL Server Hardware Checklist