Sql server 2005 Waits and Queues

səhifə	8/10
tarix	18.07.2016
ölçüsü	0.86 Mb.

1 2 3 4 5 6 7 8 9 10

Retrieving statements in the waiter list

The stored procedure get_statements_in_waiter_list reports statements in the waiter list that matches the optional parameter @wait_list. If @wait_list is NULL, it lists ALL statements in the waiter list. It can be run at any time to capture statements waiting in the waiter list. For example, you can list any statement waiting for parallelism as follows:

Exec get_statements_in_waiter_list @wait_type = ‘CXPACKET’

The script location is: http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/waitstats/default.mspx. See section "SQL Server 2005 Wait Types" for a list of all wait types.

SQL Server 2005 Wait Types and correlation to other Performance information

Wait Type	Category	In Wait stats Table	Description	Correlation to Other information
ASYNC_DISKPOOL_LOCK	IO	True	Occurs when there is an attempt to synchronize parallel threads that are performing tasks such as creating or initializing a file. SQL Server 2000: During Backup and Restore (for example, including zeroing out pages) threads written in parallel. SQL Server 2005: no longer initializes (for example, zeros out) data files before a restore.	Possible disk bottleneck. See the disk performance counters for confirmation.
ASYNC_IO_COMPLETION	IO	True	Occurs when a task is waiting for asynchronous I/Os to finish. Identify disk bottlenecks, by using Perfmon Counters, Profiler, sys.dm_io_virtual_file_stats and SHOWPLAN. Any of the following reduces these waits: Adding additional IO bandwidth. Balancing IO across other drives. Reducing IO with appropriate indexing. Check for bad query plans. Check for memory pressure.	See section titled “Memory pressure and Disk IO subsystem issues” See PERFMON Physical Disk performance counters: Disk sec/read Disk sec/write Disk queues See PERFMON SQLServer:Buffer Manager performance counters for memory pressure: Page Life Expectancy Checkpoint pages/sec Lazy writes/sec See PERFMON SQLServer:Access Methods for correct indexing: Full Scans/sec Index seeks/sec SQL Profiler can be used to identify which Transact-SQL statements do scans. Select the scans event class and events scan:started and scan:completed. Include the object Id data column. Save the profiler trace to a trace table, and then search for the scans event. The scan:completed event provides associated IO so that you can also search for high reads, writes, and duration. Check SHOWPLAN for bad query plans
ASYNC_NETWORK_IO New	Network	True	Occurs on network writes when the task is blocked behind the network. Verify that the client is processing data from SQL Server.	Check network adapter bandwidth. 1 Gigabit is better than 100 megabits. 100 megabits is better than 10 megabits.
BACKUP New	Backup	True	Occurs when a task is blocked as part of backup processing.
BACKUP_CLIENTLOCK New	Backup	True	Internal Only.
BACKUP_OPERATOR New	Backup	True	Occurs when a task is waiting for a tape mount. To view the tape status, query sys.dm_io_backup_tapes. If a mount operation is not pending, this wait type can indicate a hardware problem with the tape drive.	Check backup tape drive.
BACKUPBUFFER New	Backup	True	Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount.	Check backup tape drive.
BACKUPIO	Backup	True	Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount.	Check backup tape drive.
BACKUPTHREAD	Backup	True	Occurs when a task is waiting for a backup task to finish. Wait times can be long, from several minutes to several hours. If the task that is being waited on is in an I/O process, this type does not indicate a problem.
BAD_PAGE_PROCESS	Memory	True	Occurs when the background suspect page logger is trying to avoid running more than every five seconds which occurs when many suspect pages are encountered.	Suspect pages are captured in the msdb database system table dbo.suspect_pages. Suspect pages can be restored using online page level restore.
BROKER_CONNECTION_RECEIVE_TASK New	Service Broker	False	Occurs when waiting for access to receive a message on a connection endpoint. Receive access to the endpoint is serialized.
BROKER_ENDPOINT_STATE_MUTEX New	Service Broker	False	Occurs when there is contention to access the state of a service broker connection endpoint. Access to the state for changes is serialized.
BROKER_EVENTHANDLER New	Service Broker	False	Occurs when a task is waiting in the primary event handler of the Service Broker. This should occur very briefly.
BROKER_INIT New	Service Broker	False	Occurs when initializing Service Broker in each active database. This should rarely occur.
BROKER_MASTERSTART New	Service Broker	False	Occurs when a task is waiting for the primary event handler of the Service Broker to start. This should occur very briefly.
BROKER_RECEIVE_WAITFOR New	Service Broker	True	Occurs when the RECEIVE WAITFOR is waiting. This is typical if no messages are ready to be received.
BROKER_REGISTERALLENDPOINTS New	Service Broker	False	Occurs during the initialization of a Service Broker connection endpoint. This should occur very briefly.
BROKER_SHUTDOWN	Service Broker	False	Occurs when there is a planned shutdown of Service Broker. This should occur very briefly, if at all.
BROKER_TRANSMITTER New	Service Broker	False	Occurs when the Service Broker message transmitter is waiting for work to do.
BUILTIN_HASHKEY_MUTEX New		True	Can occur after instance startup when internal datastructures are initialized. Does not reoccur after datastructures have been initialized.
CHECKPOINT_QUEUE		False	Occurs while the checkpoint task is waiting for the next checkpoint request.	Checkpoint writes out dirty (for example, changed) data and log pages. Check for disk issues. See PERFMON Physical Disk performance counters
CHKPT		True	Occurs at server startup to tell the checkpoint thread that it can start
CLR_AUTO_EVENT New	CLR	True	Occurs when a task is currently performing common language runtime (CLR) execution and is waiting for a particular autoevent to be initiated.
CLR_CRST New	CLR	True	Occurs when a task is currently performing CLR execution and is waiting to enter a critical section of the task that is currently being used by another task.
CLR_JOIN New	CLR	True	Occurs when a task is currently performing CLR execution and waiting for another task to end. This wait state occurs when there is a join between tasks.
CLR_MANUAL_EVENT New	CLR	True	Occurs when a task is currently performing CLR execution and is waiting for a specific manual event to be initiated.
CLR_MONITOR New	CLR	True	Occurs when a task is currently performing CLR execution and is waiting to obtain a lock on the monitor.
CLR_RWLOCK_READER New	CLR	True	Occurs when a task is currently performing CLR execution and is waiting for a reader lock.
CLR_RWLOCK_WRITER New	CLR	True	Occurs when a task is currently performing CLR execution and is waiting for a writer lock.
CLR_SEMAPHORE New	CLR	True	Occurs when a task is currently performing CLR execution and is waiting for a semaphore.
CLR_TASK_START New	CLR	False	Occurs while waiting for a CLR task to complete startup.
CMEMTHREAD	Memory	True	Occurs when a task is waiting for a thread-safe memory object. The wait time might increase when there is contention caused by multiple tasks trying to allocate memory from the same memory object.	The serialization makes sure that as long as the users are allocating or freeing the memory from the memory object, any other server process IDs (SPIDs) that are trying to perform the same task have to wait, and the CMEMTHREAD waittype is set when the SPIDs are waiting. You might notice this waittype in many scenarios. However, this waittype is most frequently logged when the ad hoc query plans are being quickly inserted into a procedure cache from many different connections to the instance of SQL Server. You can address this bottleneck by limiting the data that must be inserted or removed from the procedure cache, such as explicitly parameterizing the queries so that the queries can be reused or using stored procedures where appropriate.
CURSOR		True	Asynch Cursor thread.
CURSOR_ASYNC		True	Internal only.
CXPACKET		True	Occurs when trying to synchronize the query processor exchange iterator. Consider lowering the degree of parallelism if contention on this wait type becomes a problem. Parallel process waits can sometimes occur when data is skewed. In such cases, one parallel thread may process a larger number of rows while another may process a smaller number of rows and so on. In an OLTP environment, excessive CXPACKET waits can affect the throughput of other OLTP traffic. In a data warehouse environment, CXPACKET waits are expected for multiple proc environments.	Check for parallelism: sp_Configure “max degree of parallelism”. If max degree of parallelism = 0, you might want to use one of the following options: turn off parallelism completely for OLTP workloads: set max degree of parallelism to 1 limit parallelism by setting max degree of parallelism to some number less than the total number of CPUs. For example if you have 8 processors, set max degree of parallelism to <=4.
DBMIRROR_DBM_EVENT New	DBM	True	Internal only.
DBMIRROR_DBM_MUTEX New	DBM	True	Internal only.
DBMIRROR_EVENTS_QUEUE New	DBM	False	Occurs when database mirroring waits for events to process.
DBMIRROR_SEND New	DBM	True	Occurs when a task is waiting for a communications backlog at the network layer to clear to be able to send messages. Indicates that the communications layer is starting to become overloaded and affect the database mirroring data throughput.
DBMIRROR_WORKER_QUEUE New	DBM	False	Indicates that the database mirroring worker task is waiting for more work.
DBMIRRORING_CMD New	DBM	True	Occurs when a task is waiting for log records to be flushed to disk. This wait state is expected to be held for long periods of time.
DBTABLE			Internal only. New Checkpoint request that is waiting for outstanding checkpoint request to complete	See SQL Buffer Manager performance counters: Page Life Expectancy Checkpoint pages/sec Lazy writes/sec
DEADLOCK_ENUM_MUTEX	Lock	True	Occurs when the deadlock monitor and sys.dm_os_waiting_tasks try to make sure that SQL Server is not running multiple deadlock searches at the same time.
DEADLOCK_TASK_SEARCH	Lock	True	Large waiting time on this resource indicates that server is executing queries in addition to sys.dm_os_waiting_tasks and these queries are blocking deadlock monitor from running deadlock search (only one query or deadlock monitor can examine task state at any moment of time). DEADLOCK_TASK_SEARCH wait type is used by deadlock monitor only, queries in addition to sys.dm_os_waiting_tasks use wait type DEADLOCK_ENUM_MUTEX.
DEBUG		True	Occurs during Transact-SQL and CLR debugging for internal synchronization.
DISABLE_VERSIONING New		True	Occurs when SQL Server polls the version transaction manager to see whether the timestamp of the earliest active transaction is later than the timestamp of when the state started changing. If this is this case, all the snapshot transactions that were started before the ALTER DATABASE statement was run have finished. This wait state is used when SQL Server disables versioning by using the ALTER DATABASE statement.
DISKIO_SUSPEND	IO	True	Occurs when a task is waiting to access a file when an external backup is active. This is reported for each waiting user process. A count larger than five per user process can indicate that the external backup is taking too much time to finish.
DLL_LOADING_MUTEX	XML	False	Occurs one time while waiting for the loading of the XML parser DLL.
DROPTEMP		True	Occurs between attempts to drop a temporary object if the previous try failed. The wait duration grows exponentially with each failed drop try.
DTC	DTC	True	Occurs when a task is waiting for an event that is used to manage state transition. This state controls when the recovery of Microsoft Distributed Transaction Coordinator (MS DTC) transactions occurs after SQL Server receives notification that the MS DTC service has become unavailable. This state also describes a task that is waiting when a commit of a MS DTC transaction is initiated by SQL Server and SQL Server is waiting for the MS DTC commit to finish. Waiting for Distributed Transaction Coordinator	Check transaction isolation level
DTC_ABORT_REQUEST	DTC	True	Occurs in a MS DTC worker session when the session is waiting to take ownership of a MS DTC transaction. After MS DTC owns the transaction, the session can roll back the transaction. Generally, the session waits for another session that is using the transaction
DTC_RESOLVE	DTC	True	Occurs when a recovery task is waiting for the master database in a cross-database transaction so that the task can query the outcome of the transaction.
DTC_STATE	DTC	True	Occurs when a task is waiting for an event that protects changes to the internal MS DTC global state object. The state should be held for very short periods of time.
DTC_TMDOWN_REQUEST	DTC	True	Occurs in a MS DTC worker session when SQL Server receives notification that the MS DTC service is not available. First the worker waits for the MS DTC recovery process to start. Then the worker waits to obtain the outcome of the distributed transaction that the worker is working on. This can continue until the connection with the MS DTC service has been reestablished.

1 2 3 4 5 6 7 8 9 10