[스크랩] MSSQL WRITELOG wait 타입 관련

COMMIT TRAN 명령이 WRITELOG wait type으로 많이 남아있따..

병목의 한 부분으로 볼 수 있다.

관련해서 writelog에 대한 블로그글을 몇몇 검색해 보았다.

http://daily-sql.tistory.com/entry/%EC%95%84%EB%AC%B4%EB%A6%AC-%EC%84%B1%EB%8A%A5%EC%9D%B4-%EC%A2%8B%EC%9D%80-ssd%EB%A1%9C-%EB%B0%94%EA%BF%94%EB%8F%84-writelog-IO-%EB%B3%91%EB%AA%A9%EC%9D%B4-%EC%82%AC%EB%9D%BC%EC%A7%80%EC%A7%80-%EC%95%8A%EB%8A%94-%EA%B2%BD%EC%9A%B0

아무리 성능이 좋은 ssd로 바꿔도 writelog I/O 병목이 사라지지 않는 경우

이슈:

SQL Server 2005 32bit Enterprise Edition, 

wait 분석을 해보니 writelog 가 가장 큰 문제. 

transaction log 용 디스크는 100만 iops 를 보장하는 독립된 ssd 임(ldf만 존재함)

ldf 의 사이즈는 10GB

vlf 는 30개

활성 vlf는 2개 정도 있음,

디스크 응답속도는 1us 

키:

http://blogs.msdn.com/b/sqlcat/archive/2013/09/10/diagnosing-transaction-log-performance-issues-and-limits-of-the-log-manager.aspx 

Outstanding I/O Limitation때문에 발생하는 문제

1. Amount of "outstanding log I/O" Limit.

a. SQL Server 2008: limit of 3840K at any given time

b. Prior to SQL Server 2008: limit of 480K at any given time

c. Prior to SQL Server 2005 SP1: based on the number of outstanding requests (noted below)

2. Amount of Outstanding I/O limit.

a. SQL Server 2005 SP1 or later (including SQL Server 2008 ):

i. 64-bit: Limit of 32 outstanding I/O’s

ii. 32-bit: Limit of 8 outstanding I/O’s

b. Prior to SQL Server 2005 SP1: Limit of 8 outstanding I/O’s (32-bit or 64-bit)

솔루션:

64비트로 올리거나 상위버전으로 올리면 해결

------------------------------------------------------------------------------------------------------------------

http://sqlsql.tistory.com/383

wait type 과 의미를 천천히 정리해보자

생각날 때 추가해 보도록 하지요....쉽게~ 대충 정리하고 이쁘게 나중에 공식문서로 만들어보죠~

일단은 마구 적습니다. 그리고 나서 하나하나 시나리오를 만들어서 테스트하고 책으로 출간 sqltag book 3?

절대적으로 신뢰하지 말고 테스트 해봐야 합니다. 틀린게 보이면 언제든지 말해주세요~

 

io_completion 은 tempdb 를 사용해 sort spill 을 하거나, 어떤 동기 i/o 오퍼레이션을 할때 나타난다. 이걸 mdf ldf 를 잘 살펴보고 io 를 분산하라고 하면 안된다. io_completion 은 직접적인 data page 의 io 대기가 아니다. tempdb 를 잘 보살펴야 한다. sort 를 제거하던지....

async_io_completion backup alter database create database 등을 할 때 나타날 수 있다.

sleep_task 는 hash match 연산을 하면 일어날 수 있다. 역시 tempdb 를 잘 보살펴야 한다. hash_match 를 안하게 하던지

async_network_io 는 select 하는 데이터양이 많아 네트워크로 보내는 데이터가 많으면 일어날 수 있다. 데이터를 줄이던지 fetch count 를 적절히 크게 하던지 등등

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Threading;

namespace ConsoleApplication3

{

    class Program

    {

        static void Main(string[] args)

        {

            SqlNetTest sqlNetTest = new SqlNetTest();

            for (int i = 0; i < 5; i++)

            {

                sqlNetTest.execute();    

            }

            

        }

    }

    class SqlNetTest

    {

        private List<NetTestResult> netTestResults = new List<NetTestResult>();

        private string ConnectionString

        {

            get

            {

                return "Data Source=xxxxxx;Initial Catalog=xxxxx;User ID=xxxxxx;Password =xxxxx";

            }

        }

        public void execute()

        {

            this.netTestResults = usp_netTest(this.ConnectionString);

            foreach (var a in netTestResults)

            {

                Console.WriteLine("name : {0}, type {1}", a.name, a.type);

            }

         

        }

        private List<NetTestResult> usp_netTest (string ConnectionString)

        {

            List<NetTestResult> netTestResults = new List<NetTestResult>();

            using (SqlConnection conn = new SqlConnection(ConnectionString))

            {

                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())

                {

                    cmd.CommandText = "dbo.usp_netTest";

                    cmd.CommandType = CommandType.StoredProcedure;

                    SqlDataReader read = cmd.ExecuteReader();

                    while (read.Read())

                    {

                        NetTestResult netTestResult = new NetTestResult();

                        netTestResult.name = read.IsDBNull(read.GetOrdinal("name")) ? "" : read.GetString(read.GetOrdinal("name"));

                        netTestResult.type = read.IsDBNull(read.GetOrdinal("type")) ? "" : read.GetString(read.GetOrdinal("type"));

                        netTestResults.Add(netTestResult);

                        Thread.Sleep(100);

                    }

                    read.Close();

                }

                conn.Close();

            }

            return netTestResults;

        }

    }

    public class NetTestResult

    {

        public string name { get; set; }

        public string type { get; set; }

    }

}

-- 이하 sql script

alter proc usp_netTest

as

select cast(name as char(8000)) name, type from sysprocesses  

이렇게 테스트를 한다고 치면, client cursor 이며 결과셋이 작으면 sql server 의 net buffer 에 있는 결과를 다 가져가 버린다. 시간은 수 ms 나 0초가 나온다. 그런데 결과를 일부러 크게 만들어서 sql net buffer 를 다 채우고도 나갈 결과가 더 있는 경우 클라이언트가 다시 요청할때 비워진 버퍼를 sql 이 채우게 될것이다. 이것이 async network io 대기이며, 이것은 클라가 천천히 루프를 돌거나 (임의로 threadsleep 을 준 경우나) 대역폭이 가득차 빨리빨리 요청하더라도 sql 의 net buffer 를 빨리 비우지 못하는 경우일 것이다. 

oledb 는 연결된 서버나 자신의 sql server 외 연결된 데이터 억세스에서 대기가 일어난 경우이다.

latch_xx 는 비 데이터페이지의 대기이다. 메모리 동기화를 위해서 일어나는 대기이다 디스크가 빠르던 느리던 상관없다.

sos_schedulder_yield cpu 를 많이 쓰는 경우 일어나는 대기이다. 사용할 quantum time 을 다 쓰고 scheduler 를 양보할 때 나타난다.  4ms 동안 하나의 logical core 를 다 쓰고 다시 resource wait 나 runnable queue 에 들어가는 경우이다. 수 us 쓰고 나갈꺼라고 생각했는데 비선점점형 스케줄링에 의한 강제양보까지 기다린다니 얼마나 많이 cpu 를 쓰겠는가? 

resource_semaphore memory grant 에 시간이 걸릴때 이다. 메모리를 늘이거나 메모리를 안쓰게 하거나 메모리를 적게쓰게 튜닝한다. 아무리 메모리가 많아도 oltp 는 어쩌고 저쩌고 나도 안다.......쿨럭~ 쉽게 가자고~

cmemthread 역시 메모리 동기화 문제이다. enter critical section exit critical section 으로 동기화 하는데 오랜 시간이 걸린 경우이다. 생각해보라 1) 특정 메모리 영역이 가득차 있고 추가 메모리를 할당할수 없는 상태에서 메모리를 달라고 하면 enter critical section 한 뒤에 메모리를 받을수가 없다. 아니면 2) numa node 당 메모리를 줄 수 있는 부분이 있는데 이 부분을 core 단위로 분리하면 실제 메모리가 있는 상황에서는 동기화 문제를 효과적으로 분배할 수 있다. tempdb iam gam sgam 대기 걸리면 tempdb 쪼개잖아~ 그럼 확 빨라지는 경우를 생각해 봐라

writelog 로그 플러시 시간이 오래 걸리는 경우이다. 이건 disk sub-system 이 병목이 되는지 살펴보고 더 많은 로그를 써야 한다면 데이터베이스를 쪼개서 더 많은 ldf 파일을 쓰도록 하는 방법이 있다. 또는 안 쓰는 인덱스를 지워 트랜젝션 로그를 적게 쓰도록 하면 된다. 혹은 버전을 올린다. 버전을 올리면 더 빨라진다. 무슨버전에서 바뀌었는지 기억이 나지 않는다......아는사람 알려주기~

cxpacket 병렬처리에서 thread 별로 일할 데이터를 골고부 배분하지 못하면 이 대기가 크게 나타날 수 있다. 왜냐하면 코디네이터 하나는 전체 처리기간동안 cxpacket 대기를 찍을것이고, 일꾼들 중 1개 thread 에 100개의 일량을 받고 나머지 3개 thread 에 일량을 받지 못하거나 너무 적게 받았다면 이 대기는 무척 커질수 있다. 코디네이터 100 + 일꾼 3 * 100(99) 의 대기가 발생한다. (한넘을 열심히 일하잖아 ^^) 그런데, 4개의 thread 에 동일하게 일량이 배분되면 코디네이터 25 의 대기가 발생한다. 일량 배분 알고리즘과 강제 일량 분배에 대해서는 쿼리테크닉이 필요하다. 

threadpool 이것은 worker thread 가 소진되어 thread 를 할당받지 못할경우 발생할 수 있다. max worker thread 를 늘여주거나 병렬처리나 lock 을 줄여 thread가 소진되지 않도록 쿼리를 잘 짠다. 

pageiolatch_xx 이건 뭐 디스크 열라게 긁어서 그런거다 쿼리를 효율적으로 바꾸어 읽어야 할 데이터만 읽도록 하던지 메모리를 늘여 디스크에서 안 읽게 하던지 디스크를 빠르게 하던지 선택의 폭은 다양하다 

checkpoint_queue  체크포인트 끝부터 체크포인트 시작까지 대기시간 (체크포인트 하고있는 시간은 빠진다) 하루종일 체크포인트 치고 있으면 이 시간이 짧을것이다. (안부장님) 

logmgr_queue 이것은 i/o 병목이라고 보지 않는다. 그냥 다른넘들도 잘 트랜젝션 로그 쓰기를 시도하고 있구나 정도의 참고수치가 될 듯 하다. 다른 워커가 로그 블럭이 비워지기를 기다리고 있는것을 나타내며 기다리는 아이 몇 넘 * 시간 으로 증가한다. 

Used by the background worker "Log Writer" to wait on a queue for requests to flush log blocks to the transaction log. This is an "optional" wait type see Important Notes section in blog You should be able to safely ignore this wait type unless you believe a problem exists in processing log blocks to flush to the transaction log. 

This wait type is not a wait indicating I/O bottlenecks. It is only for waiting for other workers to request log block flushes. Note that on SQL Server 2005 this wait type will not show up in sys.dm_exec_requests because the Log Writer task does not show up there.

logbuffer 이게 i/o 병목이다. 워커쓰레드가 트랜젝션 로그블럭을 로그버퍼에 쓰려고 하는데 로그버퍼가 없어 대기하는 상황이다.  다른 워커쓰레드가 writelog 가 블럭을 잡고 있기 때문이다. 

Used to indicate a worker thread is waiting for a log buffer to write log blocks for a transaction

This is typically a symptom of I/O bottlenecks because other workers waiting on WRITELOG will hold on to log blocks. Look for WRITERLOG waiters and if found the overall problem is I/O bottleneck on the storage system associated with the transaction log

 

똑똑한 아저씨들 링크

http://blogs.msdn.com/b/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx

다른거 유용한거 있으면 댓글 받아요~ 같이 만들어 보아요~

 

------------------------------------------------------------------------------------------------------------------

https://blogs.msdn.microsoft.com/sqlsakthi/2011/04/16/what-is-writelog-waittype-and-how-to-troubleshoot-and-fix-this-wait-in-sql-server/

What is WRITELOG waittype and how to troubleshoot and fix this wait in SQL Server

<<This blog has been updated on 12/23/2014>>

What does WRITELOG waittype mean?

SQL Server Transaction Log entries are first written serially to in-memory structure called Log block and due to various conditions listed below in this blog, then the entire log block is flushed to disk. The size of this log block can be between 512 bytes and 64 kilobytes. For more information about the size limit of different types of I/O performed by SQL Server, visit http://blogs.msdn.com/b/sqlcat/archive/2005/11/17/493944.aspx

Now, WRITELOG waits represents the time, SQL Server is waiting on the I/O to complete after a Log block in cache is flushed from Memory to Disk (Thus why having a ultra fast disk like Flash based disks to store your LDF can speed up this I/O and it reduces your transaction latency)

Before we continue further, please read an Excellent article from SQL Customer Advisory Team at http://blogs.msdn.com/b/sqlcat/archive/2013/09/10/diagnosing-transaction-log-performance-issues-and-limits-of-the-log-manager.aspx which talks about a throttle limit called “Outstanding I/O limit” and “Outstanding log I/O Limit”.  So this is another condition where you will notice WRITELOG wait type when outstanding I/O limit is reached.

So, given below are the conditions (but not limited to) when a log block will be flushed to disk:

1. COMMIT XSN (or ABORT XSN)

2. CHECKPOINT

3. LOG block is full

4. Outstanding I/O limit or Outstanding log I/O Limit reached

Transaction commits when you run a COMMIT TRAN (incase of Explicit transaction) or whenever SQL Server commits the transaction on behalf of you (Auto commit)

It is important to note that multiple active transactions can be interleaved in the log block, and if any one transaction commits, the buffer memory is written to the transaction log file by the log writer. No need to worry about what happens to data integrity when multiple transactions interleave at same time because SQL Server implements synchronization objects like spinlocks, mutex to control the access to log buffer.

To monitor your database log buffer flush and it’s size, refer the the perfmon counters given below:

SQLServer: Database object counter	Displays
Log Bytes Per Flush	Number of bytes in the log buffer when the buffer is flushed.
Log Flushes/sec	Number of log flushes per second.
Log Flush Wait Time	Total wait time (milliseconds) to flush the log.
Log Flush Waits/sec	Number of commits per second that are waiting on log flush.

So, now you know that when you see WRITELOG wait type in your system, all the wait time shown for this wait type is time spent by SQL Server waiting for the Log I/O to finish and anything you can do to improve the performance of transaction log I/O will reduce the wait time. Also, remember that having multiple transaction log files for one database doesn’t help here because log I/O is a serial operation. Given that WRITELOG is a disk based wait, you can do some design changes so you make SQL Server to do less LOG I/O, one such design change is given below.

LOG I/O depends on whether you choose IMPLICIT or EXPLICIT Transaction:

During a troubleshooting scenario with one of Microsoft customer, where INSERT’ing 60000 records to a 2 column sample table took 8 minutes on a specific server while the same operation takes ,< 20 seconds in two other identical server. I was asked to to proove that it is a Disk problem and not a configuration issue within SQL Server that is different between these 3 servers.

This insert was running in a WHILE loop without any EXPLICIT Transaction. When I looked into the customer wait Stats (using sys.dm_os_wait_stats), WRITELOG was the top wait type in terms of total wait time on the server where this command is taking 8 minutes.

I explained about the WRITELOG wait type and conditions when LOG Blocks will be flushed to disk to the Customer.

To show a demo about difference between Autocommit and EXPLICIT transaction which changes the behavior of Log Flush, changed his code:

Before:

WHILE @i < 60000

Begin

INSERT INTO table values (1,’Name’)

End

After:

BEGIN TRAN

WHILE @i < 60000

Begin

INSERT INTO table values (1,’Name’)

End

COMMIT TRAN

Second batch with EXPLICIT transaction completed in just 2 seconds (compared to 8 mins when Auto commit transaction) in the same problematic environment. The reason is because every Auto Commit is flushing the transaction log buffer to disk for every INSERT but in the case of EXPLICIT XSN, log buffers are flushed only when they are full or due to any of the 4 conditions mentioned above, so the less log block flush, less the time we wait for I/O which decreases transaction latency. (Note that this was a test environment so almost, no other requests were running at that time)

Also we knew that the comparison might be skewed if there is a autogrowth happening in the background so we ensured that Initial Size for LDF was set before the testing started.

There is another difference in the case of EXPLICIT XSN which we noticed:

1. In Auto commit XSN, CHECKPOINT will run quite often and will complete soon because the amount of dirty buffers will be less when it runs so often

2. In EXPLICIT XSN, CHECKPOINT will run after a longer interval and it will run for longer duration because it has more work to do because of huge backlog of dirty buffers so you will see a spike in CHECKPOINT I/O if you monitor using Perfmon counters

 

Now, it made sense to the customer that instead of 60,000 LOG I/O, we just be sending <10 LOG I/O to disk when we use Explicit transaction which made transaction to complete sooner.

DMV sys.dm_tran_database_transactions helped us to understand WRITELOG wait time for every transaction w.r.t  the transaction begin time. Also sys.dm_io_pending_io_requests helped us to understand the outstanding I/O and whether the I/O is still pending from Disk subsytem or is it pending from OS and how long it is pending.

Please refer to the blog http://blogs.msdn.com/b/sql_pfe_blog/archive/2013/03/28/observing-sql-server-transaction-log-flush-sizes-using-extended-events-and-process-monitor.aspx to understand how Extended events can help you to monitor the log flushes.

So to summarize,

If you see WRITELOG wait type, before adding more disk, check how frequently you transactions commits and how good is your disk response time. Now, decide whether you need to optimize current disk holding transaction log or using Flash arrays or any other ultra fast disk subsystem.

Feel free to post your comments if you have any questions or if you need more information.

Further Reads:

http://blogs.msdn.com/b/psssql/archive/2011/01/07/discussion-about-sql-server-i-o.aspx

http://msdn.microsoft.com/en-us/library/cc917726.aspx

http://blogs.msdn.com/cfs-filesystemfile.ashx/__key/CommunityServer-Components-PostAttachments/00-09-98-45-35/Microsoft-SQL-Server-IO-Internals.pptx

Sakthivel Chidambaram, SQL Server Support

------------------------------------------------------------------------------------------------------------------

https://blogs.msdn.microsoft.com/psssql/2009/11/02/the-sql-server-wait-type-repository/

The SQL Server Wait Type Repository…

12/10/2009: Significant updates to the blog. Check back in 2010 for more updates to the wait type table

 

As part of my talk at the 2009 US PASS Summit here in Seattle called Inside SQL Server Wait Types, I’m creating this blog post as a reference point that can be used to find out information about wait types in SQL Server 2005 and 2008.  My hope is that if you have a question about a wait type you encounter with SQL Server 2005, 2008, or beyond, you will use this blog post as the starting point. This blog post should also be used as an enhancement to what you can find at the following SQL Server Books online topic:

sys.dm_os_wait_stats (Transact-SQL)

Why use this blog post over other references? Because information in this blog post will be based on the the actual product source code and empirical testing of scenarios that cause these wait types.

The Wait Type Table

This table is to be used a repository of wait types as found in sys.dm_os_wait_stats in SQL Server 2005 and 2008.  The current list is based on waits found in my recent talk at PASS, requests in the comments of the blog, or ones I’ve seen in BOL, newsgroup postings, etc I think you will find interesting. There is not documentation for all wait types. Not sure there ever will be. But if you see one you think I need to document, please comment this blog post.

Attached to this blog is the wait type table in the form of a spreadsheet. This will allow you to search and sort the data as necessary. However, for purposes of ensuring the information is searchable on he web, I’ve also pasted in the contents of the spreadsheet as part of this section. Any updates to the table will be in the form of an updated spreadsheet attachment and a repost of the pasted able. By default I’ll sort the pasted table by Type in ascending order.

You may ask “Where are all of the Wait Types documented?”. Well no where actually. Some are documented in the BOL as I’ve listed above. My table certainly won’t list out all ~485 wait types that are listed in sys.dm_os_wait_stats. But I will document ones i think may help you and include ones you send comments on you would like to know more information by commenting this blog. one thing that will help you is that I will try to document as many wait types that I can that you can typically “ignore” such as Usage=Background and waits that you can actually safely ignore because they simply are never used in the code. Below the wait type table I’ll also show you other waits I’m working on.

The spreadsheet has the following format:

Type – the name of the wait type as found in sys.dm_os_wait_stats

Area – This describes what part of the code the wait is used such as I/O, SQLOS, Network, Memory, Broker, Buffer, Query, …. Think of this as a keyword you can search on to find out wait types associated with a particular topic.

Usage – this is a Description of how the wait type is used within the engine. In other words why does the wait type exist. The possible values are:

Resource: Waiting for a resource to become available or complete such as I/O, Network, Thread, Memory

Sync: Waiting for synchronization to access a resource or code such as locks, latches, or other memory structures

Forced: Waiting is forced by the code such as Yielding or Sleep

External: These are waits that indicate code is running in Preemptive Mode

Background: Waits used mainly by background tasks to indicate they are waiting for work to do

Ignore: Waits that we could probably remove from the DMV as they are not even used in the code

Version – SQL Server 2005, SQL Server 2008

Description – A description of what this wait type means, where it is used in the code, and how you might encounter it

Action – Suggestion on possible actions you should take if you see this wait type show up as a possible “problem”

Wait_Type	Area	Usage	Version	Description	Action
ASYNC_IO_COMPLETION	I/O	Resource	SQL 2005;SQL 2008	Used to indicate a worker is waiting on a asynchronous I/O operation to complete not associated with database pages	Since this is used for various reason you need to find out what query or task is associated with the wait. Two examples of where this wait type is used is to create files associated with a CREATE DATABASE and for “zeroing” out a transaction log file during log creation or growth.
CHECKPOINT_QUEUE	Buffer	Background	SQL 2005;SQL 2008	Used by background worker that waits on events on queue to process checkpoint requests. This is an “optional” wait type see Important Notes section in blog	You should be able to safely ignore this one as it is just indicates the checkpoint background worker is waiting for work to do. I suppose if you thought you had issues with checkpoints not working or log truncation you might see if this worker ever “wakes up”. Expect higher wait times as this will only wake up when work to do
CHKPT	Buffer	Background	SQL 2005; SQL 2008	Used to coordinate the checkpoint background worker thread with recovery of master so checkpoint won’t start accepting queue requests until master online	You should be able to safely ignore. You should see 1 wait of this type for the server unless the checkpoint worker crashed and had to be restarted.. If though this is technically a “sync” type of event I left its usage as Background
CXPACKET	Query	Sync	SQL 2005;SQL 2008	Used to synchronize threads involved in a parallel query. This wait type only means a  parallel query is executing.	You may not need to take any action. If you see high wait times then it means you have a long running parallel query. I would first identify the query and determine if you need to tune it. Note sys.dm_exec_requests only shows the wait type of the request even if multiple tasks have different wait types. When you see CXPACKET here look at all tasks associated with the request. Find the task that doesn’t have this wait_type and see its status. It may be waiting on something else slowing down the query. wait_resource also has interesting details about the tasks and its parallel query operator
DISKIO_SUSPEND	BACKUP	Sync	SQL 2005; SQL 2008	Used to indicate a worker is waiting to process I/O for a database or log file associated with a SNAPSHOT BACKUP	High wait times here indicate the SNAPSHOT BACKUP may be taking longer than expected. Typically the delay is within the VDI application perform the snapshot backup.
FT_IFTS_SCHEDULER_IDLE_WAIT	Full-Text	Background	SQL 2008	Used by a background task processing full-text search requests indicating it is “waiting for work to do:”	You should be able to safely ignore unless some unexplained FTS issue. High wait times are normal
IO_COMPLETION	I/O	Resource	SQL 2005; SQL 2008	Used to indicate a wait for I/O for operation (typically synchronous)  like sorts and various situations where the engine needs to do a synchronous I/O	If wait times are high then you have a disk I/O bottleneck. The problem will be determining what type of operation and where the bottleneck exists. For sorts, it is on the storage system associated with tempdb. Note that database page I/O does not use this wait type. Instead look at PAGEIOLATCH waits.
KSOURCE_WAKEUP	Shutdown	Background	SQL 2005;SQL 2008	Used by the background worker “signal handler” which waits for a signal to shutdown SQL Server	You should able to safely ignore this wait. You should only see one instance of this wait but in SQL Server 2008 what will be unusual is the wait time will show up as 0 in sys.dm_os_wait_stats. Other DMVs like sys.dm_exec_requests will show the SIGNAL_HANDLER with a high wait time of this type.
LAZYWRITER_SLEEP	Buffer	Background	SQL 2005;SQL 2008	Used by the Lazywriter background worker to indicate it is sleeping waiting to wake up and check for work to do	You should be able to safely ignore this one. The wait times will appear to “cycle” as LazyWriter is designed to sleep and wake-up every 1 second. Appears as LZW_SLEEP in Xevent
LOGBUFFER	Transaction Log	Resource	SQL 2005; SQL 2008	Used to indicate a worker thread is waiting for a log buffer to write log blocks for a transaction	This is typically a symptom of I/O bottlenecks because other workers waiting on WRITELOG will hold on to log blocks. Look for WRITERLOG waiters and if found the overall problem is I/O bottleneck on the storage system associated with the transaction log
LOGMGR_QUEUE	Transaction Log	Background	SQL 2005; SQL 2008	Used by the background worker “Log Writer” to wait on a queue for requests to flush log blocks to the transaction log. This is an “optional” wait type see Important Notes section in blog	You should be able to safely ignore this wait type unless you believe a problem exists in processing log blocks to flush to the transaction log. This wait type is not a wait indicating I/O bottlenecks. It is only for waiting for other workers to request log block flushes. Note that on SQL Server 2005 this wait type will not show up in sys.dm_exec_requests because the Log Writer task does not show up there.
MISCELLANEOUS	Ignore	Ignore	SQL 2005;SQL 2008	This really should be called “Not Waiting”.	This may have been used in SQL 2000 but for 2005/2008, it is not used for any valid wait. It is simply the default wait in a list and isn’t used to indicate any real waiting. This type shows up twice in sys.dm_os_wait_stats in SQL 2008 but the “other” instance is an older unused wait type in the code. We should be able to remove it.
PREEMPTIVE_XXX	Varies	External	SQL 2008	Used to indicate a worker is running coded that is not under the SQLOS Scheduling Systems	I will specific PREEMPTIVE_XX wait types or groups of them in 2010. Be sure to read the Important Notes section for bug where this wait type is being over counted by the engine in some situations. Note also that when you see this wait_type in sys.dm_exec_requests the status of the request is RUNNING not SUSPENDED. This is because the engine doesn’t really know if the thread is waiting or running “external” code.
REQUEST_FOR_DEADLOCK_SEARCH	Lock	Background	SQL 2008	Used by background worker “Lock Monitor” to search for deadlocks.  This is an “optional” wait type see Important Notes section in blog	You should be able to safely ignore this one as it is just and indication the lock monitor thread is temporarily sleeping before it wakes up to do work. This wait type should never exceed 5 seconds in one “wait” as this is the interval the lock monitor wakes up to check for deadlocks
RESOURCE_QUERY_SEMAPHORE_COMPILE	Query	Resource	SQL 2005; SQL 2008	Used to indicate a worker is waiting to compile a query due to too many other concurrent query compilations that require “not small” amounts of memory.	This is a very complicated problem to explain. The problem is more than just concurrent compilations. It is the amount of memory required by the compilations. Typically this problem is not seen on 64bit systems. The biggest thing you can do is find out why you have so many compilations. Furthermore, a high amount of “query memory” can result in less memory available for compilations so check what other users are consuming high query memory.
RESOURCE_SEMAPHORE	Query	Resource	SQL 2005; SQL 2008	Used to indicate a worker is waiting to be allowed to perform an operation requiring “query memory” such as hashes and sorts	High wait times indicate too many queries are running concurrently that require query memory. Operations requiring query memory are hashes and sorts. Use DMVs such as dm_exec_query_resource_semaphores and dm_exec_query_memory_grants
SOS_SCHEDULER_YIELD	SQLOS	Forced	SQL 2005;SQL 2008	Used to indicate a worker has yielded to let other workers run on a scheduler	This wait is simply an indication that a worker yielded for someone else to run. High wait counts with low wait times usually mean CPU bound queries. High wait times here could be non-yielding problems
SQLTRACE_BUFFER_FLUSH	Trace	Background	SQL 2005;SQL 2008	Used by background worker	You should be able to safely ignore unless some unexplained problem with SQLTrace files not getting written to disk properly.
THREADPOOL	SQLOS	Resource	SQL 2005; SQL 2008	Indicates a wait for a  task to be assigned to a worker thread	Look for symptoms of high blocking or contention problems with many of the workers especially if the wait count and times are high. Don’t jump to increase max worker threads especially if you use default setting of 0. This wait type will not show up in sys.dm_exec_requests because it only occurs when the task is waiting on a worker thread. You must have a worker to become a request. Furthermore, you may not see this “live” since there may be no workers to process tasks for logins or for queries to look at DMVs.
WRITELOG	I/O	Sync	SQL 2005; SQL 2008	Indicates a worker thread is waiting for LogWriter to flush log blocks.	High waits and wait times indicate an I/O bottleneck on the storage system associated with the transaction log
XE_DISPATCHER_WAIT	XEvent	Background	SQL 2008	Used by a background worker to handle queue requests to write out buffers for async targets	You should be able to safely ignore this unless you believe a problem is occurring with processing of events for async targets. Since this works on a queue you can have bursts of high wait times especially when no XEvent sessions are active.
XE_TIMER_EVENT	XEvent	Background	SQL 2008	Used to indicate a background task is waiting for “expired” timers for internal Xevent engine work	You should be able to safely ignore this one. Just used by the Xevent engine for internal processing of its work. If something was possibly wrong with Xevent processing you might see if this thread ever “wakes up”

Work to be done:

• Go through and find all wait types that are not used in the code and mark these in the table with Ignore
  
• Broker waits. JonthanK has commented in the blog that many broker waits overall are documented at http://blogs.msdn.com/sql_service_broker/archive/2008/12/01/service-broker-wait-types.aspx
  
• Go through BOL documentation on wait types, add them to this table and clarify more details about them
  
• Research exact usage of FT_IFTSHC_MUTEX. It appears from the code this is a sync type of wait for access to specific code for FTS but not apparent on its exact usage.
  
• Add specifics for PREEMPTIVE_XXX wait types

Important Notes

In this section, I list out important notes to keep mind about wait types:

1) We have a found a bug in the way PREMPTIVE_XXX wait types work in SQL Server 2008. In some cases, the engine will count a wait for a PREEMPTIVE_XX wait when the code really didn’t switch to preemptive mode. The scope of how often and which PREEMPTIVE_XXX wait types are affected is difficult to predict. This problem was first reported to me after my PASS talk when a customer ran DBCC FREEPROCCACHE. A large number of PREEMPTIVE_OLEDBOPS waits showed up. This is an example of where the engine marked a PREEMPTIVE_OLEDBOPS wait to free up internal information about a plan in cache when it should only apply to plan with linked servers for non SQL Server providers. We know other situations may apply, but I suspect PREEMPTIVE_OLEDBOPS is one of the wait types that is being “over-counted” in SQL Server 2008.

2) One thing I found as part of this research as I was not aware of is that some wait_type names can appear in SQL 2005 in DMVs like sys.dm_exec_requests but not sys.dm_os_wait_stats. An example of this is CHECKPOINT_QUEUE. In SQL Server 2008, some of these wait_type names were changed to be what I’ll call “optional”. By default the “optional” types show up in sys.dm_os_wait_stats. This explains an explosion of rows in this DMV in SQL Server 2008 not just because we added more. As part of this change, we added an undocumented trace flag 8050 which if enabled when querying sys.dm_os_wait_stats will exclude the “optional” wait types. However, When you enable this trace flag in SQL Server 2008 the number of rows is only reduced by about 50 so don’t think that this is an approach to filter out wait types you can “ignore”. But it does explain why some appeared in DMVs in SQL Server 2005 but not in sys.dm_os_wait_stats. So in the wait type table if you see a wait type that says it applies to both SQL Server 2005 and 2008, but you don’t see this wait type in sys.dm_os_wait_stats in SQL Server 2005, you will now know why.

 

Other Resources

 

SQLCAT Waits Stats Per Session CodePlex

SQLCAT SQL 2005 Waits and Queues Whitepaper

What’s Next?

I want this blog post and the attached spreadsheet to be a living document and help feed enhancements to the SQL Server Books online. So if you encounter a question on a wait type that is confusing or not listed in this post, please post a comment to this blog post or email psssql@microsoft.com. This isn’t to obtain “free support” for a problem but to be used to help enhance and create the right content for the community on this topic. I’ll be posting regular updates to this blog throughout 2010 so take a specific feed and look for updates.

Bob Ward
Microsoft

SQL Server Wait Type Table.xlsx

Tags Wait Types

------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------