Detecting performance impact of tempdb disk speed with snapshot isolation level in SQLServer

Question

We use SQL Server with snapshot isolation level. Exactly it is a read committed snapshot level. As far as I know, in such case, SQLServer uses the version store in tempdb. In our environment tempdb is stored on slower disks than the main database. So the question is how to detect performance impact of such tempdb placement. And how to argue with my colleagues this point? Is there any useful management views or counters?

Answer 1

It is absolutely right about Snap shot isolation level each time a row is updated, the SQL Server Database Engine stores a copy of the original row in tempdb. And, in turn efficiency of disk latency is challenging.

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

• Snapshot Isolation level does not create Temporary tables ,it stores a copy of row in the TempDB
• database engine retrieves rowversion from Tempdb
• Read/Writes will eventually increase in Tempdb when snapshot isolationlevel is turned on

You can use this script to find out read/writes by database. Irerspective of any isolation level, you can identify total I/O for each database

SELECT name AS 'Database Name'
      ,SUM(num_of_reads) AS 'Number of Read'
      ,SUM(num_of_writes) AS 'Number of Writes' 
FROM sys.dm_io_virtual_file_stats(NULL, NULL) I
  INNER JOIN sys.databases D  
      ON I.database_id = d.database_id
GROUP BY name ORDER BY 'Number of Read' DESC;

For Displaying I/O statistics by physical drive letter :

SELECT left(f.physical_name, 1) AS DriveLetter, 
    DATEADD(MS,sample_ms * -1, GETDATE()) AS [Start Date],
    SUM(v.num_of_writes) AS total_num_of_writes, 
    SUM(v.num_of_bytes_written) AS total_num_of_bytes_written, 
    SUM(v.num_of_reads) AS total_num_of_reads, 
    SUM(v.num_of_bytes_read) AS total_num_of_bytes_read, 
    SUM(v.size_on_disk_bytes) AS total_size_on_disk_bytes
FROM sys.master_files f
INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) v
ON f.database_id=v.database_id and f.file_id=v.file_id
GROUP BY left(f.physical_name, 1),DATEADD(MS,sample_ms * -1, GETDATE());

For Calculating Disk Latency for your different database drives

SELECT  LEFT(physical_name, 1) AS drive,
        CAST(SUM(io_stall_read_ms) / 
            (1.0 + SUM(num_of_reads)) AS NUMERIC(10,1)) 
                          AS 'avg_read_disk_latency_ms',
        CAST(SUM(io_stall_write_ms) / 
            (1.0 + SUM(num_of_writes) ) AS NUMERIC(10,1)) 
                          AS 'avg_write_disk_latency_ms',
        CAST((SUM(io_stall)) / 
            (1.0 + SUM(num_of_reads + num_of_writes)) AS NUMERIC(10,1)) 
                          AS 'avg_disk_latency_ms'
FROM    sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
        JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
                                       AND mf.file_id = divfs.file_id
GROUP BY LEFT(physical_name, 1)
ORDER BY avg_disk_latency_ms DESC;

Hope this Helps