Files with larger disk consumption

Junior Galvão - MVP, 2019-01-15 (first published: 2019-01-08)

How to use this script is quite simple:

1 – Download the script, open in your access to a tool instance or Server Microsoft SQL Server;

2 – Preferably use for the first runs a development or test environment;

3 – The logged on user must have View Server State permission so you can get the data returned by sys.dm_io_virtual_file_stats Dynamic Management Function that replaces the old fn_virtualfilestats function;


4 – After running note the values given in column [AVG IT Stall in MS], she represents the parameter which we must examine how possible to handle disk latency related reading activities;

5 – The values presented for this column should be identified according to the table below:

< 1ms: unbelievable;
< 3ms: excellent;
< 5ms: very good;
< 10ms: within the expected;
< 20ms: reasonable;
< 50ms: limit;
> 100ms: bad;

> 1 sec: severe disk contention; and

> 15 sec: serious problems with the storage or disk.

    Use Master
Go


;With Top10FilesWithLargerDiskConsumption
As
(
Select DB_NAME(database_id) AS 'Database Name',
             file_id As 'File Id',
			 io_stall_read_ms As 'IO Stall Read in Ms', 
			 num_of_reads As 'Numbers of Reads',
			 CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS 'AVG Read Stall in MS', 
			 io_stall_read_ms + io_stall_write_ms AS 'IO Stalls',
			 num_of_reads + num_of_writes AS 'Totals IO',
			 CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS 'AVG IO Stall in MS',
			 Convert(Date,GETDATE(),103) as Date
FROM sys.dm_io_virtual_file_stats(null,null)
Where file_id <> 2
)
Select Top 10 * from Top10FilesWithLargerDiskConsumption
Order By [AVG Read Stall in MS] Desc
Go

Rate

4.4 (5)

Share

Share

Rate

4.4 (5)

Related content