I wrote this script after studying about I/O statistics.
This view is based on sys.dm_io_virtual file status
This looks at the physical file reads and writes, and how many times it stalled.
If it is stalling, you can dig deeper to see what is causing the stall… then you can partition the offensive table/object etc.
Cool system view!
-----------@Mydatabase-----------------------
-- type in name of database between the ''
-- or type in 'all' between the ''
--------------------------------------------
declare @Mydatabase nvarchar(255)
set @Mydatabase = 'all'
--------------------------------------------
if @Mydatabase = 'all'
begin
select
db_name(database_id)
as database_name,
file_name(file_id)
as file_name,
sample_ms,
num_of_reads,
num_of_bytes_read,
io_stall_read_ms,
num_of_writes,
num_of_bytes_written,
io_stall_write_ms,
io_stall,
size_on_disk_bytes
from
sys.dm_io_virtual_file_stats(null, null) ;
end
else
begin
declare @database int
set @database = db_id(@Mydatabase) ;
select
db_name(database_id)
as database_name,
file_name(file_id) as file_name,
sample_ms,
num_of_reads,
num_of_bytes_read,
io_stall_read_ms,
num_of_writes,
num_of_bytes_written,
io_stall_write_ms,
io_stall,
size_on_disk_bytes
from
sys.dm_io_virtual_file_stats(@database, null) ;
end
