SQL Server latch waits - index contention & performance issues how DMVs can help you to get information?
What kind of performance issues you see on day-to-day basis within your environment?
The following elements are important factors that can add fuel to the fire (problem):
|
Faulty hardware |
• |
Hardware that is not configured correctly |
• |
Firmware settings |
• |
Filter drivers |
• |
Compression |
• |
Bugs |
• |
Other conditions in the I/O path |
I/O is important factor to consider when it comes to the performance issues, such as blocking, locking and slow response time that will cause stretching of resources on the server. In this case using SYSMON and other monitoring tools will get you the latch contention and timeouts to the application. So what are these latches and why they are important when it comes to performance, also important for indexes.
A latch is a lightweight version of a lock, in generate they do not hinder performance or concurrency and in any case if you are having performance issues then using SYSMON for latch wait timeouts to determine is inital stage to monitor. By default the latch wait time is a good indication for your hardware disks performance, as it will tell about a process that is taking how long to access data, read and write from the disk. For instance if a latch wait time of 1.3 seconds is telling you that your disk subsystem is a bottleneck or it could be a poor design of the disk subsystem, or from table scans or from choosing the improper RAID type or from just a lack of spindles to achive the IO that SQL is asking for.
Using SQL Server 2005 the DMVs can be handy to get more information on system internals behaviour, so for the Latch Wait Times these relevant DMVs are as follows, such as dm_exec_requests that will get you wait type and wait resources, dm_os_latch_stats for wait counts on BUF and nonbuf latches. Also the dm_wait_stats will get you granular information on latches, further if you have disk related issues then using dm_io_pending_io_requests DMV can get you how the snapshot IO requests are performing with additional information by using dm_io_virtual_file_stats to get per file.
Within SQL Server 2000 (SP4) latch waits are more often visible within blocking column of sysprocesses table, where in IO related issues you will see more on EX_LATCH and SH_LATCH for acquired & waiting stats of blocking. But for SQL Server 2005 (using DMVs) or get blocking information for the wait PAGEIOLATCH_SH will not get your blocking information when it occurs. Similarly for EX_LATCH & SH_LATCH wait types the contents of memory buffer that holds the database is trying to read a page in memory that hasn't been pulled the complete information yet. Also the latching issues occurs when your TEMPDB is not located within proper RAID configuration, as it is a special database in the sense that it often the target of frequent drop/create tables. This requires frequent allocation/deallocation which results in hot spots on pages like SGAM and PFS. Splitting tempdb into multiple files is common technique to minimize this contention and also avoid (whereever necessary) using the caching of tempdb objects in 2005 to help in reducing contention on system table pages.
So how to reduce the contention, using the methods of creating multiple data files and avoiding AUTOGROW and SHRINK the TEMPDB data file. Not only the latching issues there are other wait types you need to monitor when you have a problem with slow response, they are: WRITELOG, CXPACKET, RESOURCE_SEMAPHORE_QUERY_COMPILE & RUNNABLE status that are occuring frequently or showing for longer time when you are monitoring the user connections against a database. Not only that when you observer the high average row lock or latch waits, then what it says is the average row lock or latch waits are computed by dividing lock and latch wait milliseconds (ms) by lock and latch waits. The average lock wait ms computed from sys.dm_db_index_operational_stats represents the average time for each block. So when you see high number of deadlock occurrence then use the Profiler “Graphical Deadlock” under Locks event to identify the statements involved in the deadlock (SQL 2005). Not only hardware related sometimes network latching also causes the issues, such as high network latency coupled with an application that incurs many round trips to the database
So to know more about these contention and information on I/O that will need to fine tune, refer to SQL2005-IOBasics whitepaper, SQL Server performance monitoring requirement and specifications, heard about baseline and benchmarking? and methods of Identify and troubleshoot slow running queries in SQL Server blog posts here.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.