SQL Server Health & Performance Report

This repository contains DatabaseHealthPerformanceReport.sql, a comprehensive T-SQL diagnostic script that generates a full health and performance profile of a SQL Server database. It is designed as a one-stop, read‑only reporting tool for DBAs, SQL Developers, data engineers, and anyone who needs fast visibility into how a database is behaving.

---

Features

This report surfaces detailed information across multiple dimensions of SQL Server performance:

1. Database & File Size Summary

• Logical and physical file names
• File sizes, space used, and free space
• Growth settings and file types

2. Largest Tables (Top 50)

• Row counts
• Data, used, and total size (MB)
• Highlights storage-heavy objects

3. Index Usage Statistics

• Seeks, scans, lookups, and updates
• Read vs write patterns
• Last usage timestamps
• Ideal for identifying unused or underused indexes

4. Index Fragmentation Report

• Fragmentation percent
• Page counts
• Index types
• Helps determine rebuild vs reorganize strategies

5. Missing Index Recommendations

• Estimated impact
• Equality, inequality, and INCLUDE columns
• Top 50 recommendations by estimated improvement

6. Unused / Low-Usage Index Identification

• Indexes with reads = 0
• High write / low read patterns
• Helps optimize storage and reduce overhead

7. Top Resource-Consuming Queries

• Average CPU, elapsed time, logical reads
• Query text snippet
• First/last execution timestamps
• Excellent for identifying performance bottlenecks

8. Active Sessions & Blocking

• Open sessions
• Blocking and wait types
• Current executing statement
• CPU and I/O details

---

How to Use

1. Open the script in SQL Server Management Studio (SSMS) or your preferred SQL client.

2. Optionally specify a database context at the top of the file:

   -- USE YourDatabaseNameHere;
   -- GO

3. Execute the script.
   All sections run sequentially and return multiple result sets.

4. Export results if needed:

   • CSV, Excel, PDF, etc. via SSMS
   • Useful for audits, documentation, and tuning sessions

---

Permissions Required

Some sections rely on SQL Server DMVs and require:

• VIEW SERVER STATE
• VIEW DATABASE STATE

If you lack these permissions, those sections will return limited or no data.

---

Notes & Limitations

• Script is read-only and does not modify any data or objects.
• Uses both catalog views and DMVs.
• Fragmentation report uses SAMPLED mode for performance.
• Missing index DMV recommendations do not always equal optimal design — they are a starting point.

---

Use Cases

• Baseline database health checks
• Performance tuning sessions
• Pre‑migration analysis
• Identifying indexing improvements
• Developer onboarding to large/legacy databases
• Regular operational reporting

---

Repository Structure

/
└── DatabaseHealthPerformanceReport.sql   # Main T-SQL script
└── README.md                             # Documentation

---

Author

Alex McAnnally
SQL Developer & Database Analyst
Last Updated: November 2025

Feel free to submit issues or pull requests with enhancements or suggestions.