wait types

In this post, I look at the OLEDB wait type.

Books Online lists this as

“Occurs when SQL Server calls the SQL Server Native Client OLEDB Provider. This wait type is not used for synchronization. Instead, it indicates the duration of calls to the OLE DB provider.”

“OLEDB” stands for Object Linking Embedding Database. OLEDB is an API used by SQL Server when retrieving data from a remote server or datasource.

Linked server queries, remote procedure calls, BULK INSERT operations, full text search and queries to external data sources, for example Excel all contribute to OLEDB wait type.

The wait type occurs when the remote system or network connection is not fast enough and so the calling server has to wait for the results to be returned.

Guidance on what to check and ideas on how to reduce OLEDB wait type

If the stats for OLEDB wait type are high then there are some things you can do:

1. Look at your data distribution – are the linked server calls absolutely necessary?
2. If the data being referenced is static then consider copying the data to local database and query it there.
3. Consider using replication to bring the data local.
4. If the data source is some file such as Excel, can you reference it locally instead of over a network connection?
5. Is it feasible to import the Excel file into the database?
6. Tune the remote query if you can. If it is a server which you are able to administer or you can speak with the DBA of that system, then find ways to make that query run faster – check indexes, check statistics etc.
7. Have your network admins check over the connection between the server and data source. Is it fast enough?
8. OLEDB may be a symptom of another wait type. For example check RESOURCE_SEMAPHORE wait type – is this high?

In my last post, I wrote about how SQL Server schedules tasks to be executed. It’s important to be  able to understand this when when trying to analyse wait types and statistics in SQL Server.

For this post, I will be looking at the CXPACKET wait type and what you can do to reduce it.

This is one wait type that can be misjudged.

I mentioned before that the wait types need translation – MSDN describes this as:

Occurs when trying to synchronize the query processor exchange iterator. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem.

This is a parallel processing wait type where many threads are used to satisfy the query. Each thread deals with a subset of the data. The idea is that if there are more threads working on different parts of the data set, it can be more efficient.

Parallel execution can therefore be a highly effective way of dealing with large sets of data, for example in a data warehouse/reporting server. However, in OLTP systems, it could well have a negative impact on performance.

CXPACKET wait type occurs when there are threads taking longer to execute than the other threads in that query. The completed threads are left in waiting state until the last thread can complete.

I’ll go into more detail about these settings in the next section but by default your SQL Server is configured for parallelism as the “max degree for parallelism setting” is set at zero.

For a query then to qualify for parallel execution, its estimated (or cached) cost needs to exceed the “cost threshold for parallelism” setting.

So in an example scenario on a server with 8 cores, if a parallel query executes with “max degree of parallelism” set at zero, then up to 8 cores can be used in the query. As each core completes its work, it waits until all cores have completed.

The result is CXPACKET waiting and the potential to delay other SPIDs wanting a piece of CPU time to process their request.

Assessing causes of CXPACKET wait type is vitally important

You have to remember that CXPACKET waits are normal for multiprocessor servers because of parallel execution. So simply adjusting your server configuration in an effort to reduce this wait type could have an overall negative impact on performance.

Understanding whether the system is an OLTP, DSS or combination of both is critical.

It’s no good simply assuming that because you have CXPACKET waits, that it is automatically a problem. If CXPACKET is one of the top reported wait types on your SQL Server, the next thing to consider is, what are the causes and is this wait type normal for my workload?

There may be other contributing factors. Is CXPACKET a symptom of something else? For example, it could be as simple as a missing index or out of date statistics. Your SQL Server then tries to compensate by generating a query plan that uses parallelism in an effort to optimize the query, or rather, to make the scan operation go faster.

How to reduce CXPACKET wait type by changing settings

There are some settings which can affect how your queries use parallelism.

1. Server level – “Max Degree of Parallelism”
2. Server level – “Cost Threshold for Parallelism”
3. Query hint – MAXDOP

Max Degree of Parallelism – specifies the number of CPU cores to be used in parallel query execution at the server level. The default value is 0 which means that SQL Server will use all available cores. Setting this to 1 turns off parallelism. Here is an example which instructs the SQL Server to use no more than 2 cores for parallel execution:

EXEC sp_configure 'max degree of parallelism',2
GO
RECONFIGURE WITH OVERRIDE
GO

Cost Threshold for Parallelism –  the estimated elapsed time in seconds before the server will consider parallelism in the query execution. Here is an example setting this value to the estimated elapsed time of 10 seconds:

EXEC sp_configure 'cost threshold for parallelism', 10
GO
RECONFIGURE WITH OVERRIDE
GO

MAXDOP – a query hint where the number of cores is specified for the query. Here is an example using MAXDOP for 2 cores:

SELECT Column1, Column
FROM SomeTable
WHERE (Column1 > @Value1 AND Column2 < @Value2)
OPTION (MAXDOP 2)

Note that you can also change max degree of parallelism and cost threshold for parallelism settings using Management Studio. These settings can be found under the Properties->Server Properties->Advanced section.

Summary and guidance points for CXPACKET wait type and parallelism

• OLTP – parallelism can have a negative impact on performance.
• OLAP/reporting benefits from parallelism.
• Look at your plan cache, which of your queries use parallelism?
• Check query plans – do you have missing indexes or out of date statistics?
• What other waits are occurring that might be causing CXPACKET?
• Consider using MAXDOP to fine tune specific queries.
• Set max degree of parallelism and cost threshold for parallelism to best suit your server’s function.
• Do not adjust max degree of parallelism without proper analysis of your servers workload/queries.

In my previous post, I touched upon SQL Server wait types and in order to understand wait types in SQL Server, it’s important to know first how the server schedules tasks.

On each CPU core, only a single thread or “worker” can be running at any given time. Each CPU, be it logical or physical inside your SQL Server is assigned a scheduler and it is responsible for managing the work which is carried out by threads.

You can view the schedulers on your SQL Server by running this code:

SELECT * FROM sys.dm_os_schedulers;

Schedulers are responsible for managing user threads and internal operations. If you run that DMV, you can see these types of status in there:

• VISIBLE ONLINE (DAC) – thread scheduler used to manage the DAC (Dedicated Administator Connection)
• VISIBLE ONLINE – user thread schedulers
• HIDDEN ONLINE – internal operation thread schedulers

So depending on how many CPUs you have in your SQL Server, you will see from this DMV that there can be quite a few schedulers available.

More CPUs = more schedulers = more work can get done 🙂

There are some other status’ which you might see:

• HOT_ADDED – schedulers created in response to a CPU which was added whilst the server was online (hot added CPU)
• VISIBLE OFFLINE – user schedulers which are mapping to CPUs which are offline in the affinity mask.
• HIDDEN OFFLINE – schedulers assigned to internal operations mapped to CPUs which are offline in the affinity mask.

Thread status

If you’ve ever looked at your process list in SQL Server either by using sp_who2 or activity monitor, you may have noticed that there is a column in there called “Status” (activity monitor in Management Studio 2012 calls this “Task State”)

There are some status’ which help you to understand how the queries execute, they are RUNNING, SUSPENDED and RUNNABLE

RUNNING – the thread is actively running on the CPU.

SUSPENDED – thread is waiting for a resource to become available. Thread is on the “waiter list”.

RUNNABLE – resources are now available to the thread but as only one thread can execute on a CPU at any given time, the thread has to wait its turn. It goes to the bottom of the “RUNNABLE queue” and finally returns to a RUNNING state when it is its turn to run as controlled by the CPU scheduler.

It goes to the bottom of the “RUNNABLE queue”

Please note that this behaviour can change if using RESOURCE GOVERNOR (Enterprise Edition)

The size of the “RUNNABLE queue” for each scheduler can be seen by looking at the value for the “runnable_tasks_count” column from the sys.dm_os_schedulers DMV.

Threads can transition around the different states until their work is completed. When on the waiter list, a thread can wait for some time before it changes state. There is no set order in which they are then changed from SUSPENDED to RUNNABLE.

In order to view what each thread is waiting for, you can look at the sys.dm_os_waiting_tasks DMV:

SELECT * FROM sys.dm_os_waiting_tasks;

 Summary points

• Each CPU (logical or physical) is assigned a scheduler.
• Only one thread can be running on a CPU at any given moment.
• Threads are either running on the CPU (RUNNING), on the waiter list (SUSPENDED) or in the runnable queue (RUNNABLE).
• Use sys.dm_os_schedulers to view schedulers on your SQL Server.
• Use sys.dm_os_waiting_tasks to view the tasks which are in the wait queue, waiting for resource to become available.

Ok, so in this post, I wanted to go over SQL Server wait types and statistics  – what they are and how to check them.

My idea is then to start a series of posts which go into more detail about individual SQL Server wait types, their causes and how to improve them. At the time of writing this post, I do not know about all of the wait types in SQL Server and there are many of them.

Some of them I have experience with, some of them I haven’t so this is a partially a learning experience for me, as well as hopefully providing you with informative content so that you can better understand your own SQL Server wait types.

So let us begin…. 🙂

What are SQL Server Wait Types?

When troubleshooting performance issues in SQL Server, it is difficult to know where to start as there is so much to look at.

The best way is to look at your SQL Server at a high level and then drill down to the root causes. As well as capturing hardware/OS and SQL Server related counters using Perfmon, looking at the wait types on your SQL Server is essential to this process.

Ultimately this is more efficient than just assuming that something is, when it may not actually be.

If you, the DBA are more efficient, management gets more for their money and we all know that time is money. SQL Server wait stats provide you with that high level information which you need to succeed.

Whenever a process inside SQL Server has to wait for something, the time spent on that is tracked. So for example, the wait could be tracked because of delays reading data, writing data or some external process.

The waiting process is assigned a wait type. The wait types are not terribly descriptive and require translation into something more meaningful which I will attempt to do in later posts.

If you can reduce waits in SQL Server, you will achieve better overall performance.

How to view your SQL Server wait types and statistics

There is a DMV which you can use to return an aggregated view of system waits in milliseconds.

SELECT * FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC

The results will show many waits, some of them however are system waits and are not useful for performance tuning.

I prefer to use this version which was written by SQL Server expert Glenn Berry. It sorts the “wheat from the chaff” and returns the really useful wait stats from your SQL Server instance, with the worst offenders at the top of the list.

-- Isolate top waits for server instance since last restart or wait statistics clear
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN
('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR'
,'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT'
,'BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE'
,'FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT'
,'XE_DISPATCHER_JOIN','SQLTRACE_INCREMENTAL_FLUSH_SLEEP'))
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95 OPTION (RECOMPILE); -- percentage threshold
GO

If you want to view the current waits as they are occurring, you can do this too. Here I am applying the same filters as in Glenn’s script above:

SELECT * FROM sys.dm_os_waiting_tasks
WHERE wait_type NOT IN
('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR'
,'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT'
,'BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE'
,'FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT'
,'XE_DISPATCHER_JOIN','SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
ORDER BY wait_duration_ms DESC;

Something to note is that if SQL Server is restarted, then all wait statistics are reset.

Additionally, you can run this to reset them:

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

Permissions needed….

The user must have VIEW SERVER STATE permission