SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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_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
            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;

Mission: SQL Homeostasis

Drupal is a Senior SQL Server consultant with over 20 years of experience in engineering innovative SQL Server solutions for high growth organizations. A truly SQL Server specialist and IT generalist, Drupal is a certified MCDBA, Oracle OCP-DBA and IBM WebSphere Administrator. He is also ITIL V3 certified at the Foundation and Intermediate levels as well as PMP.


Leave a comment on the original post [sqlpal.blogspot.com, opens in a new window]

Loading comments...