SqlServer input - Change to "Database IO" measurement

[Trovalo]

Feature Request

Let's start by telling that this is might be a breaking change for this measurement, I'm considering only the v2 of the measurement "sqlserver_database_io".

The main purpose of this measurement is to get IO info like the "Disk Latency", which is the most important to me.

Those data come from the dynamic management view in SQL Server sys.dm_io_virtual_file_stats. The view outputs incremental counters which restart from zero when the instance is restarted.

The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started.

Proposal:

Change the query to get the data to the attachment Disk latency for telegraf.txt

• The query is compatible with the on-premise version of SQL Server starting from SQL Server 2008.
• The same/similar logic can be applied also to "Azure SQL DB", without "by disk" metrics since that info is not available. (I don't have an Azure SQL DB so I might be wrong)

The updated measurement will have the following structure:
Tags

• sql_instance
• database_name
• physical_filename
• logical_filename
• file_type (which is kept as-is: possible values are "ROWS"|"LOG"|"FILESTREAM"|"FULLTEXT")
• volume_mount_point (which usually is the disk volume itself but can also be a subpath)

Fields

• read_latency_ms (this is the actual istantaneous latency)
• write_latency_ms (this is the actual istantaneous latency)
• overall_latency_ms (this is the actual istantaneous latency)
• avg_bytes_per_read
• avg_bytes_per_write
• avg_bytes_per_transfer

The query also adds the overall disk stats, regardless of the single database, for these rows tags like "database_name" and "physical_filename" have "All Databases" and "All Files" as value

Note that the current query fields are not included, but can be included to maintain retro compatibility. (although some renaming is necessary for the "latency" fields)

My query comes from those references:

• SQLskills - SQL Server IO Latency
• SQLskills - SQL Server Diagnostic Information Queries (somewhere around query 28)

Current behavior:

As of now, the measurement contains the following data, which are the fields outputted by the view as-is:

Tags

• sql_instance
• database_name
• physical_filename
• logical_filename
• file_type (which is calculated and can be only "LOG"|"DATA")

Fields

• read_bytes
• read_latency_ms (this is the time passed waiting since the startup of the instance)
• reads
• write_bytes
• write_latency_ms (this is the time passed waiting since the startup of the instance)
• writes

Here is the link to the query that extracts those data

telegraf/plugins/inputs/sqlserver/sqlserver.go

Line 353 in e9e4f2c

const sqlDatabaseIOV2 = `

Desired behavior:

The measurement contains:

• the "instantaneous latency" instead of the current "incremental time since startup"
• The measurements contain data by disk (volume_mount_point)
• the measurement contains overall disk data

Use case:

• It will simplify the formulas when querying the measurement since you won't need anymore to do calculation between different fields
• It will add the data by disk, which now is not provided. This is extremely useful when checking performance issues that can be disk related