Search This Blog

Thursday, April 26, 2018

How to find out whether your database is transactional, analytical or hybrid?


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_stats AS s
                        INNER JOIN sys.indexes AS i ON s.object_id = i.object_id
                                                       AND i.index_id = s.index_id
               WHERE    OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
               GROUP BY OBJECT_NAME(s.object_id) WITH ROLLUP
             )
    SELECT  DB_NAME() DB ,
            work_load_cte.TableName ,
            work_load_cte.Reads ,
            work_load_cte.Writes ,
            CASE WHEN work_load_cte.Writes = 0 THEN NULL
                 ELSE work_load_cte.Reads / work_load_cte.Writes
            END AS read_vs_writes
    FROM    work_load_cte
    ORDER BY read_vs_writes DESC;