Updated February 19, 2022.
Purpose
To get a quick overview of the databases on the instance and if they have configurations that should be changed.
Sources
This feature queries the DMV sys.databases which returns data for the databases on the instance.
Reports
The report page Database State has a table showing the databases on the instance, as well a few but critical and important settings.
The RCSI (Read Committed Snapshot Isolation) setting will be shown with green background if enabled.
The following settings well be shown with yellow background if not configured optimal:
| Setting | Description |
|---|---|
| Comp. level | Databases running at a lower compatibility level than the instance will be colored yellow |
| Recovery model | User databases not configured to be using Full recovery model will be colored yellow. There can be situations where it is acceptable to have a production database in Simple or Bulk Logged but these situations are rare |
| Page verify | Databases not configured to be using Checksum as Page verify method will be colored red |
| Auto close | Databases configured to be using Auto close will be colored red |
| Auto shrink | Databases configured to be using Auto shrink will be colored red |
| Auto update stats. async. | Databases configured to be using Auto update stats. async. will be colored yellow |
| Delayed durability | Databases configured to be using Delayed durability will be colored yellow |
When hovering over the table a tooltip is showing the history regarding database configuration changes.
Model
The model for the report uses the fact [Database state] and [Database state history] and the dimension [Database].
The fact view [Database state] in the database contains all values from the DMV, but as good practice dictates it, it is only the values used that are loaded into Power BI report.
Configuration
There is no configuration for this feature.
Scheduling
The default schedule for the feature is every hour (3600 seconds).
Retention
The default retention for the table dbo.fhsmDatabaseState is 1.825 days (5 years).
