October 18, 2021 at 12:41 pm
For SQL Managed Instance, disk latency for Reads and Writes, when I query through ssms is very high for the user databases (latency is >300ms). I've been using below query to get the disk latency for Onpremise and SQL MI databases. The Query works fine for Onpremise, but on MI it is showing up high latency for user database data/log file.
Microsoft documentation says SQL MI in General Purpose tier can have a maximum latency of 5-10ms. Is there anything wrong with the query or sys.dm_io_virtual_file_stats in Managed instance?
SELECT
DB_NAME ([vfs].[database_id]) AS [DBName],
[mf].[physical_name],LEFT ([mf].[physical_name], 2) AS [Drive],
[ReadLatency] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
[WriteLatency] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
[Latency] =
CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
[AvgBPerRead] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
[AvgBPerWrite] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
[AvgBPerTransfer] =
CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
THEN 0 ELSE
(([num_of_bytes_read] + [num_of_bytes_written]) /
([num_of_reads] + [num_of_writes])) END
FROM
sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
ON [vfs].[database_id] = [mf].[database_id]
AND [vfs].[file_id] = [mf].[file_id]
WHERE DB_NAME ([vfs].[database_id])
not in ('master','msdb','model')
ORDER BY DB_NAME ([vfs].[database_id]);
October 19, 2021 at 9:23 am
On General Purpose MI, the disk speed is based on the size of the disk, and each log and data file is on a separate disk. The bigger the disk is, the faster it is. Maybe you need to increase your file size to get them into the next disk size.
https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/resource-limits
October 19, 2021 at 12:20 pm
Thankyou for the reply.
We are using 4tb disk as the db size is 3tb. Regardless of the disk size it says in the link, latency for general purpose tier should be 5-10 ms and for business critical tier it should be 1-2 ms.
When I query the databases, latency is more than 300ms, is this normal?
I'm new to Azure and would like to know if this can be considered as normal or am I selecting wrong system table.
October 20, 2021 at 9:42 am
That's an interesting point actually. The way I read it is that the MI server can talk to the disks with latency of 5-10ms but the disks themselves add a latency on top of that, depending on the throughput capability of the disk size.
Would be helpful if that was clarified in the documentation.
I ran the query on a dev GP server that I have access to, and also tried another version of the query that I have to measure disk latency. Both results show higher than 10ms for some disks/files, and sometimes much higher. When I run on a Business Critical version, the results are very low latency but sometimes still a bit more than 2ms.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply