OK, I’ll admit it. I’m not a DBA, but that doesn’t mean I haven’t overseen databases and their performance. I was (and probably still am) the guy who installed a Microsoft SQL database by hitting Next, Next, Next. Now, given that my database admin chops aren’t up to snuff, I was always a little bit hesitant when asked, “Why is XYZ system so slow?” … knowing that it’s supported with a back-end SQL database. If I replied with, “Beats me!” I knew I would surely be shown the door. I had to figure out what to look for and how to tweak and tune until I got it right.
Poking around the internet led me to realize that in order to understand and fix SQL performance, I needed to look at and analyze wait types. While some resources listed basic SQL Server performance tips for what seemed to be every wait type, most had the following information in common. So, I compiled a list of common wait types and techniques that every DBA (or wannabe DBA, such as myself) should know.
Most Common Wait Types in SQL Server and What They Mean
ASYNC_NETWORK_IO—The async_network_io wait types point to network-related issues. They are most-often caused by a client application not consuming and processing results from the SQL Server quickly enough. From a SQL Server point of view, there’s nothing you can tune. Engage the network team if there’s a long distance between servers, or the application team to check on application/server resources.
CXPACKET—This wait type is involved in parallel query execution and indicates the SPID is waiting on a parallel process to complete or start. Excessive CXPacket waits may indicate a problem with the WHERE clause in the query. Look at researching and changing Maximum Degree of Parallelism (MAXDOP).
DTC—This wait type is not on the local system. When using Microsoft Distributed Transaction Coordinator (MS-DTC), a single transaction is opened on multiple systems at the same time, and the transaction cannot be concluded until it’s been completed on all of the systems.
LCK_M*—This wait type happens when a query locks an object while another query is waiting to lock the same object. A common scenario is when a query is trying to modify a row while another query is trying to read the same row. Review the day and time the locking occurred and which SQL statements were being executed. Tuning these statements will reduce the session holds on the locks.
NETWORKIO—The async_network_io (in SQL 2005/2008) and networkio (in SQL 2000) wait types can point to network-
related issues, but most often are caused by a client application not processing results from the SQL Server quickly enough.
OLEDB—This wait type indicates that a SPID has made a function call to an OLE DB provider and is waiting for the function to return the required data. This wait type may also indicate the SPID is waiting for remote procedure calls, linked server queries, BULK INSERT commands, or full-search queries.
PAGEIOLATCH_*—Buffer latches, including the PAGEIOLATCH_EX wait type, are used to synchronize access to BUF structures and associated pages in the SQL Server database. The most frequently occurring buffer latching situation is when SQL Server is waiting to read a data file page or workload from storage. These pages and workloads were not cached in memory and need to be retrieved from the disk. Additional memory will help prevent pages from getting pushed out.
SOS_SCHEDULER_YIELD—SQL Server instances with high CPU usage often show the SOS_SCHEDULER_YIELD wait type. This doesn’t mean the server is underpowered; it means further research is needed to find which individual task in a query needs more CPU time. Check the Max Degree of Parallelism (MAXDOP) to ensure proper core usage. Ensure high CPU performance from both within Windows and the system BIOS.
WRITELOG—When a SQL Server session waits on the WRITELOG wait type, it’s waiting to write the contents of the log cache (user delete/update/inserts operations) to the disk where the transaction log is stored and before telling the end user his or her transactions have been committed. Disabling unused indexes will help, but the disk is the bottleneck here, and the transition log should be moved to more appropriate storage.
Getting a Leg Up
While there are many more wait types than listed above, understanding these will give you a leg up when it comes to optimizing and tuning your database performance. I know it sure did for me.