Updated August 29, 2021.
Purpose
To get a baseline of the IO level for the databases and be able to detect abnormalities as well as use it with regards of capacity planning.
Sources
This feature queries the DMV sys.dm_io_virtual_file_stats which returns I/O statistics for data and log files.
Reports
The report page Database IO has 4 line charts, 2 showing the number of bytes read and written, and the 2 others showing the level of IO latency in mS for read and write operations.
The charts also includes trendlines helping to see the growth or decline over time.
The x-axis for the charts is at the lowest granularity which by default is 15 minutes interval.
The report has 2 buttons to select the level where the tooltips are showing data. For this feature Database and Logical name can be selected.
When hovering over the charts tooltips are showing detailed insight by showing the top 5 databases or logical names (depending on the tooltip button selections) with regards of number of bytes read and written, and IO latency for read and write.
Number of bytes read tooltip
Database
Logical name
Number of bytes written tooltip
Database
Logical name
IO latency for read tooltip
Database
Logical name
IO latency for write tooltip
Database
Logical name
Model
The model for the report uses the fact [Database IO] and the dimensions [Database file] and [Date].
The fact view [Database IO] 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
The feature accept the parameter @Databases and the syntax is the same that is used in the SQL Server Index and Statistics Management solution made by Ola Hallengren.
The default parameter for the feature is “@Databases = ‘ALL_DATABASES'”
| Value | Description |
|---|---|
| SYSTEM_DATABASES | All system databases (master, msdb, model, and tempdb) |
| USER_DATABASES | All user databases |
| ALL_DATABASES | All databases |
| Db1 | The database Db1 |
| Db1, Db2 | The databases Db1 and Db2 |
| USER_DATABASES, -Db1 | All user databases, except Db1 |
| %Db% | All databases that have “Db” in the name |
| %Db%, -Db1 | All databases that have “Db” in the name, except Db1 |
| ALL_DATABASES, -%Db% | All databases that do not have “Db” in the name |
Scheduling
The default schedule for the feature is every 15 minutes (900 seconds).
Retention
The default retention for the table dbo.fhsmDatabaseIO is 30 days.
