----------------------------------------------
-- Latência Geral
----------------------------------------------
select io_stall/(num_of_reads + num_of_writes) IO_Stall,
case
when io_stall/(num_of_reads + num_of_writes)<5 then 'Excelente'
when io_stall/(num_of_reads + num_of_writes)<20 then 'Bom'
when io_stall/(num_of_reads + num_of_writes)<60 then 'Aceitável'
when io_stall/(num_of_reads + num_of_writes)<100 then 'Ruim'
else 'Péssimo'
end IO_Status,
db_name(mf.database_id) DatabaseName,type_desc FileType,mf.name LogigalName,mf.physical_name PhysicalName
from sys.dm_io_virtual_file_stats(default,default) vfs
inner join sys.master_files mf on vfs.database_id = mf.database_id and vfs.file_id = mf.file_id
order by 1 desc
----------------------------------------------
-- Latência Separada em Reads e Writes
----------------------------------------------
SELECT @@ServerName as instanceName, [Drive], volume_mount_point,
(io_stall_read_ms/(1+num_of_reads)) AS [Read Latency],
(io_stall_write_ms/(1+num_of_writes)) AS [Write Latency],
(io_stall/(1+num_of_reads + num_of_writes)) AS [Overall Latency],
(num_of_bytes_read/(1+num_of_reads)) AS [Avg Bytes/Read],
(num_of_bytes_written/(1+num_of_writes)) AS [Avg Bytes/Write],
((num_of_bytes_read + num_of_bytes_written)/(1+num_of_reads + num_of_writes)) AS [Avg Bytes/Transfer]
FROM (SELECT LEFT(UPPER(mf.physical_name), 2) AS Drive, UPPER (volume_mount_point) as volume_mount_point
,SUM(num_of_reads) AS num_of_reads,
SUM(io_stall_read_ms) AS io_stall_read_ms, SUM(num_of_writes) AS num_of_writes,
SUM(io_stall_write_ms) AS io_stall_write_ms, SUM(num_of_bytes_read) AS num_of_bytes_read,
SUM(num_of_bytes_written) AS num_of_bytes_written, SUM(io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID)
GROUP BY LEFT(UPPER(mf.physical_name), 2),UPPER (volume_mount_point)) AS tab
ORDER BY [Overall Latency] DESC OPTION (RECOMPILE);
----------------------------------------------
-- tamanho tempdb
----------------------------------------------
select @@servername,name,size from sys.sysaltfiles where dbid = 2
----------------------------------------------
-- Latência Separada em Reads e Writes por ficheiro
----------------------------------------------
SELECT mf.physical_name AS Filename
,SUM(num_of_reads) AS num_of_reads,
SUM(io_stall_read_ms) AS io_stall_read_ms, SUM(num_of_writes) AS num_of_writes,
SUM(io_stall_write_ms) AS io_stall_write_ms, SUM(num_of_bytes_read) AS num_of_bytes_read,
SUM(num_of_bytes_written) AS num_of_bytes_written, SUM(io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID)
GROUP BY mf.physical_name
----------------------------------------------
-- I/O and Size about all files
----------------------------------------------
select
db_name(mf.database_id) DatabaseName,type_desc FileType,mf.name LogigalName,mf.physical_name PhysicalName, mf.size/128 size_in_mb,
(num_of_bytes_read + num_of_bytes_written)/(vfs.sample_ms/1000/60) io_bytes_per_minute
from sys.dm_io_virtual_file_stats(default,default) vfs
inner join sys.master_files mf on vfs.database_id = mf.database_id and vfs.file_id = mf.file_id
order by 1 desc
----
