SQL Server Performance using Dynamic management Views Part 2

In this part we will see the use of DMVs in the following areas

1. TempDB uusage and Tuning Temp DB
2. I/O usage and optimzation of I/O

1. TempDB usage in SQL Server

Apart from using temporary (#) and gloabl temporary (##) table , tempDB will be used in the following sceario

1. When DBCC CHECKDB command is used                                
2. using hash join, use of SORT_IN_TEMPDB option while creating index and use of  CTEs
3. Internal objects: work table (cursor, spool)
4. Large object (LOB) variables
5. Service Broker
6. Temporary objects: global/local temp table, table vars
7. Temporary objects in  SPs and cursors
8. Version store: General,MARS,online index,Triggers,snapshot isolation level

1. 1. How to tune tempDB

There is only one tempdb for a Server, and all user databases wiil be using it, hence tuning tempDB is necessary.

1. Store tempdb in sepearate fast SAN disk, which allows faster reads and write
2. Create more than one tempDB file equivalent to number of CPUs (Number of Tempdb files = # CPUs), with equal size.

3. How to see tempDB usuage using DMV
The DMVB sys.dm_os_waiting_tasks can be queried against column wait_type like ‘PAGE%LATCH_%’ and resource_description like ‘2:%’ (for latches on TempDB pages, dbid = 2)
can be used to see tempdB usuage.

2 Monitoring I/O Performance

Major contributors of I/O activity are the following operations.

1. Moving database pages between memory and disk
2. Checkpoint operations, lazy writer, reads issued against database files, etc
3. Transaction log file operations
4. TempDB operations

2.1 DMV’s to monitor I/O

sys.dm_os_wait_stats where column wait_type like ‘PAGEIOLATCH%’, sys.dm_io_pendion_io_requests
To find IO wating sys.dm_io_virtual_file_stats can be used
For Cumulative data related to IO performance / file level granularity sys.dm_exec_query_stats with *_reads, *_writes columns


We will see Performance counters to be used for SQL Server and threshold values in Part 3.
Happy reading...