SQL Server works in Non preemtive mode which means that if SQLOS asks it to yeild because it got request from windows OS it will ask SQL server to yeild and SQL Server will listen to it and will yeild or will do as SQLOS has asked it to do. This is because SQL server runs as application and is allocated resources by SQLOS which is monitored by windows O. Preemtive wait types occur when SQL server is executing a task and is interrupted by OS and asked to give up the thread it is using so that can be allocated for other tasks and SQL will do it. It will yeild and will wait till the thread is available this waiting will come under PREEMTIVE-XXX waits.
What is version fo SQL server here is it patched to latest Service pack there was a bug in SQL server 2008 which points incorrect value of preemtive wait types.
Can you run sys.dm_exec_requests DMV and see if the process getting preemtive wait types are suspended or running.
Can you please post output of below query(By Jonathan Kehayias) to capture wait stats
SELECT TOP 10
wait_type ,
max_wait_time_ms wait_time_ms ,
signal_wait_time_ms ,
wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( )
AS percent_total_waits ,
100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( )
AS percent_total_signal_waits ,
100.0 * ( wait_time_ms - signal_wait_time_ms )
/ SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0 -- remove zero wait_time
AND wait_type NOT IN -- filter out additional irrelevant waits
( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',
'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',
'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',
'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',
'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',
'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',
'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',
'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',
'RESOURCE_QUEUE' )
ORDER BY wait_time_ms DES
What other processes are running on system is OS under load how many CPU cores does system have ?
EDIT: After user pasted output
The output does not gives and concrete picture use my query. Also are you running extended events traces because i can see XE wait types. I consider this wait type as harmful and it seems you are not facing a issue. Monitoring tools sometimes overreact so with result you posted I would just think its normal behavior.
EDIT2:
I dont find any issue with the wait stats output you posted. I also assume your server was not restarted recently otherwise waits stats will not be useful.
It's strange how many "problems" CXPACKET is causing :)
You can find my answer to similar question at this link: https://dba.stackexchange.com/a/141399/28611
But I will re-post my answer here as well
The CXPACKET was always the confusing wait type for younger DBAs and some predictably wrong reactions are generally expected. There are multiple aspects of the CXPACKET wait type and I've tried in Troubleshooting the CXPACKET wait type in SQL Server article to bring on the table most of the reasons for high CXPACKET but also to explain the background of the CXPACKET, as the proper understanding of parallelism in SQL Server is the key for understanding
So for those who don't want to get in details, I will post the summary of the article here (but I would definitely suggest reading of the article for complete info about CXPACKET wait type):
Do not set MAXDOP to 1, as this is never the solution
Investigate the query and CXPACKET history to understand and determine whether it is something that occurred just once or twice,
as it could be just the exception in the system that is normally
working correctly
Check the indexes and statistics on tables used by the query and make sure they are up to date
Check the Cost Threshold for Parallelism (CTFP) and make sure that the value used is appropriate for your system
Check whether the CXPACKET is accompanied with a LATCH_XX (possibly with PAGEIOLATCH_XX or SOS_SCHEDULER_YIELD as well). If this is the
case than the MAXDOP value should be lowered to fit your hardware
Check whether the CXPACKET is accompanied with a LCK_M_XX (usually accompanied with IO_COMPLETION and ASYNC_IO_COMPLETION). If this is
the case, then parallelism is not the bottleneck. Troubleshoot those
wait stats to find the root cause of the problem and solution
Best Answer
I believe you will see this symptom if you have a LOT of large query plans that are fighting for memory in order to compile (this has very little to do with running the query itself). To hit this, I suspect you are using an ORM or some kind of application that generates many unique but relatively complex queries. SQL Server could be under memory pressure because of things like large query operations, but on further thought it is more likely just that your system is configured with far less memory than it needs (either there is never enough memory to satisfy all of the queries you're trying to compile, or there are other processes on the box that are stealing memory from SQL Server).
You can take a look at what SQL Server is configured with using:
You can identify the cached plans that required the most compile memory with the following Jonathan Kehayias query, adapted slightly:
You can see how the plan cache is being used with the following:
When you are experiencing high semaphore waits, check to see if these query results vary significantly from during "normal" activity:
And you may also want to look and see how memory is distributed:
And there is some good information here about why you might be seeing a high number of compiles/recompiles (which will contribute to that wait):
You can check for high compile/recompile counts using the following counters:
And you can check for internal memory pressure leading to evictions - non-zero counters here would indicate that something not good is going on with the plan cache:
NOTE Most of these metrics don't have a magic "oh my gosh I need to panic or do something!" threshold. What you need to do is to take measurements during normal system activity, and determine where these thresholds are for your hardware, configuration and workload. When you
panicdo something is when two conditions are true:Optimize for ad hoc workloads
is a great setting for 99% of the workloads out there, but it will not be very helpful in reducing compilation costs - it is aimed at reducing plan cache bloat by preventing a single-use plan from storing the whole plan until it's been executed twice. Even when you only store the stub in the plan cache, you still have to compile the full plan for the execution of the query. Perhaps what @Kahn meant to recommend was setting the database level parameterization to forced, which will potentially provide better plan re-use (but it really depends on how unique all of these high-cost queries are).Also some good information in this white paper about plan caching and compilation.