Wednesday, November 2, 2016
Tuesday, November 1, 2016
Monitor Read/Write Latency on your SQL Database
SQL query to monitor Read/Write latency on your SQL Server database. Can be of great help while performance tuning your SQL database.
Copy and run this query against your database:
SELECT
[ReadLatencyAvg ms] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
[WriteLatencyAvg ms] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
[LatencyAvg ms] =
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,
LEFT ([mf].[physical_name], 2) AS [Drive],
DB_NAME ([vfs].[database_id]) AS [DB],
[mf].[physical_name],
[vfs].[file_id]
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 [vfs].[file_id] = 1 -- mdf data files
-- WHERE [vfs].[file_id] = 2 -- log files
-- ORDER BY [LatencyAvg ms] DESC
-- ORDER BY [ReadLatencyAvg ms] DESC
ORDER BY [WriteLatencyAvg ms] DESC;
GO
Industry-wide bechmarks for performance evaluation:
-- Excellent: < 1ms
-- Very good: < 5ms
-- Good: 5 – 10ms
-- Poor: 10 – 20ms
-- Bad: 20 – 100ms
-- Really bad: 100 – 500ms
-- Really Really Bad: > 500ms
Copy and run this query against your database:
SELECT
[ReadLatencyAvg ms] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
[WriteLatencyAvg ms] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
[LatencyAvg ms] =
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,
LEFT ([mf].[physical_name], 2) AS [Drive],
DB_NAME ([vfs].[database_id]) AS [DB],
[mf].[physical_name],
[vfs].[file_id]
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 [vfs].[file_id] = 1 -- mdf data files
-- WHERE [vfs].[file_id] = 2 -- log files
-- ORDER BY [LatencyAvg ms] DESC
-- ORDER BY [ReadLatencyAvg ms] DESC
ORDER BY [WriteLatencyAvg ms] DESC;
GO
Industry-wide bechmarks for performance evaluation:
-- Excellent: < 1ms
-- Very good: < 5ms
-- Good: 5 – 10ms
-- Poor: 10 – 20ms
-- Bad: 20 – 100ms
-- Really bad: 100 – 500ms
-- Really Really Bad: > 500ms
Subscribe to:
Posts (Atom)