Monitoring Tempdb I/O Performance

Performance Monitor 

The PerfMon counters that you should be interested in are as follows:  

➤Avg. Disk sec/Transfer   

➤Avg. Disk sec / Read  

➤Avg. Disk sec/Write 

 

You’l l find these grouped under Logical Disk, which shows the logical drives and drive letters presented in Windows as you would see them in Explorer; and Physical Disk, which shows the drives as Windows sees them internally. The counters themselves all provide the average latency in milliseconds for I/O requests. “Avg. Disk sec/Transfer” is the combined average for both reads and writes to a drive. This counter provides the simplest measurement for regular long-term monitoring. 

“Avg. Disk sec/Read” and “Avg. Disk sec/Write” separate the requests into read and write measurements, respectively, that can be useful for determining how to configure disk controller cache. For example, if you’re seeing poor read performance and excellent write performance, you might want to optimize the cache for reads. 

 

SQL Server DMVs 

 

SELECT  DB_NAME(database_id) AS 'Database Name',file_id,        io_stall_read_ms / num_of_reads AS 'Avg Read Transfer/ms',        io_stall_write_ms / num_of_writes AS 'Avg Write Transfer/ms' 

FROM    sys.dm_io_virtual_file_stats(-1, -1) 

WHERE   num_of_reads > 0        AND num_of_writes > 0 ; 

 

Thresholds Microsoft suggests the following performance thresholds for disk latency on drives containing SQL Server database files: 

Database data files:  

➤Target: <10ms ➤Acceptable: 10 –20ms 

 ➤Unacceptable: >20ms 

 

Database log files: 

 ➤Target: <5ms  

➤Acceptable: 5–15ms 

 ➤Unacceptable: >15ms 

You should use these thresholds for guidance only because some systems will never be able to achieve the target latency. 

 

SELECT  SUM(total_page_count)*8/1024 AS 'tempdb size (MB)',    SUM(total_page_count) AS 'tempdb pages',        SUM(allocated_extent_page_count) AS 'in use pages',        SUM(user_object_reserved_page_count) AS 'user object pages',        SUM(internal_object_reserved_page_count) AS 'internal object pages',        SUM(mixed_extent_page_count) AS 'Total Mixed Extent Pages' 

FROM    sys.dm_db_file_space_usage ; 

 

SELECT TOP 5 * 

FROM    sys.dm_db_task_space_usage 

WHERE   session_id > 50 

ORDER BY user_objects_alloc_page_count + internal_objects_alloc_page_count 

SELECT  *FROM    sys.dm_db_session_space_usage 

WHERE   session_id > 50 

ORDER BY user_objects_alloc_page_count + internal_objects_alloc_page_count DESC ;