If you are a DBA, especially Infrastructure DBA, Production DBA or Operations DBA then you don't necessarily always know what type of application load is being handled by your SQL Servers. In some cases you don't even need to know to do your job.
But if you are a performance engineer/DBA or one of your job description includes performance tuning and optimization then it is a very crucial piece of information.
Here is one of the queries I have used.
SELECT DB_NAME(vfs.database_id) dbname ,
mf.name file_name,
mf.type_desc file_type,
CASE WHEN num_of_writes =0 THEN NULL
ELSE num_of_reads /num_of_writes END read_vs_writes,
CASE WHEN num_of_bytes_written =0 THEN
NULL ELSE num_of_bytes_read /num_of_bytes_written END read_vs_writes_bytes ,
(io_stall_read_ms + io_stall_write_ms ) /
(num_of_reads + num_of_writes) avg_io_stall_ms,
*
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
INNER JOIN sys.master_files mf ON mf.database_id = vfs.database_id
AND mf.file_id = vfs.file_id
WHERE mf.type_desc != 'LOG'
AND DB_NAME(vfs.database_id) NOT IN ( 'master', 'msdb', 'tempdb', 'model' )
ORDER BY read_vs_writes DESC;
Please note that the sys.dm_io_virtual_file_stats is a DMV i.e. these data are reset every time the sql server instance is restarted and that is also the only way to reset the data from this DMV. However, you could take a snapshot of it over multiple intervals for comparison.
And here is a query that will show you read vs writes at the table level. You would want to be careful adding indexes to a table that has more writes than reads.
;
WITH work_load_cte
AS ( SELECT TableName = OBJECT_NAME(s.object_id) ,
Reads = SUM(user_seeks +user_scans + user_lookups) ,
Writes = SUM(user_updates)
FROM sys.dm_db_index_usage_statsAS s
INNER JOIN sys.indexesAS i ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
GROUPBY OBJECT_NAME(s.object_id) WITH ROLLUP
)
SELECT DB_NAME() DB ,
work_load_cte.TableName ,
work_load_cte.Reads ,
work_load_cte.Writes ,
CASEWHEN work_load_cte.Writes = 0 THEN NULL
ELSEwork_load_cte.Reads /work_load_cte.Writes
ENDAS read_vs_writes
FROM work_load_cte
ORDER BY read_vs_writes DESC;
