This blog is part of the blog series named SQL Server Monitoring.
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.
Report
The report is divided into 3 parts.
The upper part are showing 2 line charts where the left shows the number of MB data read over time, and the right shows the number of MB written over time.
The center part are showing 2 line charts where the left shows the read latency in mS over time, and the right shows the write latency in mS over time.
Be aware that the latency measured are based on the DMV information, and is not a direct measurement against the storage used.
The lower part are showing 2 tables where the left shows the number of MB read per database, and the right shows the number of MB written per database, and with the possibility to drill down to type (data and log) and logical file level.
Model
The model for the report uses the fact [Database IO] and the dimensions [Date], [Time] and [Database file].
Configuration
The service accepts 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.
