Diagnostics and health checks are tools that help you discover information about the health of your SQL Servers. Specifically, they tell you how well your database indexes are doing and whether your instances and databases are configured correctly.
What is a diagnostics project?
A diagnostics project analyses one or more SQL Server instances and databases. Two types of diagnostics projects are available: full and lightweight. The only difference is that a full diagnostics project contains index fragmentation data whereas a lightweight project does not. A lightweight project consumes less resources and takes less time to run and may be preferable in cases where database sizes are very large.
After a diagnostics project run has completed, you can view its results on the Reports page by clicking on the Diagnostics overview report. The report shows:
- Unused indexes
- Missing indexes
- Fragmented indexes (for full diagnostics projects)
- Bad wait statistics
Whenever you run a diagnostics project, a health check is also included.
What is a health check?
A health check shows the status of your instance and database configurations. For each configuration, an explanation is shown, and for performance-related configurations, a health status is included. If some of your configurations have a status other than OK, it is recommended to go through them and see the suggestions given in the report.