SQL Server 2016 – AlwaysOn VDI_CLIENT_WORKER Command

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.

I think you are misreading the deprecation notice. Specifically, you seem to be confusing DML (Data Modification Language) with DDL (Data Definition Language). What is being deprecated is the ability to use ROWGUIDCOL in INSERT / UPDATE / DELETE / MERGE statements:

-- DDL statement
CREATE TABLE #Test
(
  ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT (NEWID()) ROWGUIDCOL,
  SomeColumn INT NULL
);

INSERT INTO #Test ([SomeColumn]) VALUES (1), (100);

SELECT * FROM #Test;

DECLARE @RowID UNIQUEIDENTIFIER;
SELECT TOP 1 @RowID = tbl.[ID]
FROM   #Test tbl;

SELECT @RowID AS [RowToUpdate];

-- DML statement
UPDATE tmp
SET    tmp.[SomeColumn] += 2
FROM   #Test tmp
WHERE  ROWGUIDCOL = @RowID;

SELECT * FROM #Test;

If you run the above, only one row will be updated. This example shows that ROWGUIDCOL when used in a DML statement (not in a DDL statement) is dynamically replaced with the actual column marked as ROWGUIDCOL. And this is, in fact, why you are able (or required?) to mark a column as ROWGUIDCOL in a DDL statement: so that you (and other processes) can find it later :-).