So, Jason Massie (http://twitter.com/statisticsio) had a blog post (http://statisticsio.com/Home/tabid/36/articleType/ArticleView/articleId/324/Is-8020-a-90rsquos-Estimate.aspx) that Kendal Van Dyke (http://twitter.com/sqldba) alerted me to at SQL Saturday in Atlanta .  It does this by taking the number times an index was used for a read operation versus update operations using sys.dm_db_index_usage_stats.  Coincidentally, I was working on a chapter in a book about DMVs and working on a section on this very subject.  Initially I did my calculations solely it in a much different manner altogether just thinking about the amount of data moved to and from the physical file using sys.dm_io_virtual_file_stats.

After reading his post, I added not only a look using the index usage stats DMV, but also using sys.dm_io_virtual_file_stats as well.  My findings on my server was actually pretty interesting.  I found that while in operations my read/write ratio could be 50/50 at times, when looking at magnitude of data moved in and out of the server the ratio could be 95/5 in favor of data read.  This was clearly a clue that something isn’t quite right with our indexing or our data utilization (this is a very active server, mostly with batch/webservice OLTP operations through CRM!)

In the book, I do more breakdown of drives/objects, etc, but at a high level, I find that this is a pretty interesting comparison to look at how the read/write ratio.  For this blog entry I am just including the the following three queries, which I did go back and change to exclude log files based on some of the very interesting discussion in the comments.

Note too that sys.dm_io_virtual_file_stats could include things like backups and sys.dm_db_index_usage_stats could include maintenance too so it is highly expected that to get the most useful numbers that you employ some technique to capture numbers periodically and you exclude certain times of day when you might be doing non-standard work.  What this means to you may be different to every company.  For example, you may only want to know the read/write characteristics during steady working hours, and not overnight when nightly processes are being run.  Or you might be tuning the backup window and doing backups are you main concern.  These three queries could open your mind to lots of different ideas of how to use these DMV objects to tune your system.

–uses a like comparison to only include databases you desire
DECLARE @databaseName SYSNAME, @excludeLogFilesFlag bit
SET @databaseName = ‘%’ –‘%’ gives all databases
SET @excludeLogFilesFlag = 1 –excludes log files by default because they would not be considered in the sys.dm_db_index_usage_stats numbers

SELECT ‘Ratio bases on magnitude of data – sys.dm_io_virtual_file_stats’
SELECT  CAST(SUM(num_of_bytes_read) AS DECIMAL)
        / ( CAST(SUM(num_of_bytes_written) AS DECIMAL)
            + CAST(SUM(num_of_bytes_read) AS DECIMAL) )
                                   As RatioOfReads,
        CAST(SUM(num_of_bytes_written) AS DECIMAL)
        / ( CAST(SUM(num_of_bytes_written) AS DECIMAL)
            + CAST(SUM(num_of_bytes_read) AS DECIMAL) )
                                   AS RatioOfWrites,
        SUM(num_of_bytes_read) as TotalBytesRead,
         SUM(num_of_bytes_written) as TotalBytesWritten
FROM    sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
         join sys.master_files mf
            on mf.database_id = divfs.database_id
               and mf.file_id = divfs.file_id
WHERE   DB_NAME(divfs.database_id) LIKE @databaseName
  and   (mf.type_desc <> ‘LOG’ or @excludeLogFilesFlag = 0)

SELECT ‘Ratio bases on numbers of operations-sys.dm_io_virtual_file_stats’
SELECT  CAST(SUM(num_of_reads) AS DECIMAL)
        / ( CAST(SUM(num_of_writes) AS DECIMAL)
            + CAST(SUM(num_of_reads) AS DECIMAL) )
                                   As RatioOfReads,

        CAST(SUM(num_of_writes) AS DECIMAL)
        / ( CAST(SUM(num_of_reads) AS DECIMAL)
            + CAST(SUM(num_of_writes) AS DECIMAL) )
                                   AS RatioOfWrites,
        SUM(num_of_reads) as TotalReadOperations,
         SUM(num_of_writes) as TotalWriteOperations
FROM    sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
         join sys.master_files mf
            on mf.database_id = divfs.database_id
               and mf.file_id = divfs.file_id
WHERE   DB_NAME(divfs.database_id) LIKE @databaseName
  and   (mf.type_desc <> ‘LOG’ or @excludeLogFilesFlag = 0)

SELECT ‘Ratio bases on numbers of operations – sys.dm_db_index_usage_stats’
SELECT  case when (SUM(user_updates + user_seeks
                    + user_scans + user_lookups) = 0)
               then NULL
             else (
        CAST(SUM(user_seeks + user_scans + user_lookups)
                                               AS DECIMAL)
        / CAST(SUM(user_updates + user_seeks
                    + user_scans + user_lookups)
                                              AS DECIMAL)
                  ) end
                                           AS RatioOfReads,

        case when (SUM(user_updates + user_seeks
                    + user_scans + user_lookups) = 0)
                 then NULL
             else (

        CAST(SUM(user_updates) AS DECIMAL)
        / CAST(SUM(user_updates + user_seeks
                   + user_scans + user_lookups) AS DECIMAL)
                  ) end
                                          AS RatioOfWrites,
       SUM(user_updates + user_seeks
                    + user_scans + user_lookups) as
                                       TotalReadOperations,
       SUM(user_updates) as TotalWriteOperations
FROM    sys.dm_db_index_usage_stats AS ddius
WHERE   DB_NAME(database_id) LIKE @databaseName