Technical Article

Files with larger disk consumption


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

;With Top10FilesWithLargerDiskConsumption
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


4.4 (5)




4.4 (5)