(NON-SERIOUS) The Not So Pro Guide to SQL Server Wait Statistics

The following guide to SQL Server wait statistics (as seen in the sys.dm_os_wait_stats DMV) is complete jibberish. It should not be taken seriously under any circumstances and if followed on a production environment don't mention my name. In fact blame Derek.

Please!! DO NOT DO any of this, it's a joke (it's barely that to be fair).


ASYNC_NETWORK_IO

Asynchronously blame the network and storage teams for not upping their game. 

CXPACKET

Attempt to pronounce parallellellellellism correctly then set MAXDOP to 1.

PAGELATCH

Mention TempDB and contention in the same sentence. Delete TempDB

BACKUP_

Delete any long running backup jobs. If wait persists then delete all backup jobs.

PAGEIOLATCH

Point menacingly at storage team, shout IO and YOUR FAULT a lot. Double server RAM when nobody is looking and set SQL Server Max Memory to 2147483647.

LCK_

Well documented issue that is only truly resolved by adding NOLOCK to all queries. Often a less seasoned SQL Server professional will try to advise that this is bad practice. Smile politely, tell them they're too pessimistic and ignore them. Send an email welcoming all stakeholders to the turbo times.

HADR_

Upgrade all currently implemented HADR solutions to use merge replication instead.

IO_COMPLETION

Databases are way too big, shrink them. Occasionally databases won't shrink, this is a known issue that can be resolved by removing all indexes.

DATABASE MIRRORING_

See HADR_ wait

PREEMPTIVE_OLEDBOPS

It's not a wait type, it's a movement. 

OLEDB

Someone has set the OLE database to single user mode or in business critical conditions the OLE database does not exist and urgently requires adding to the instance.

SOS_SCHEDULER_YIELD

Very common wait type indicating that available CPU resource is laughable. Immediately quadruple the current amount of CPU's. If using enterprise edition then double again to maximise gains.

MSQL_XP

Non valid wait. Your stored procedures are so awesome they've been awarded XP points.

PWAIT_GWPROXYCONN_PENDINGQUEUE_RWLOCK

Install Welsh language pack.

QDS_HOST_INIT

Clearly it's the QDS host, innit. 

RESOURCE_SEMAPHORE

Widely considered as the too much hassle wait. Tell anyone that asks that you recently read online that memory grants are particularly slow at this time of year and it should pick up in the summer when you migrate to the cloud.

CXCONSUMER

Well documented bug in SQL Server 2017; install SP1.

THREADPOOL

Check max worker threads for default value of 0 (no wonder they're running out), set to 1.

TESTTHREAD

Locate whoever is testing in production and tell them to stop, unless it's cursors.

WRITELOG

Set all databases to simple recovery. Stop SQL Server and delete all transaction log files as you don't them anymore. 

SLEEP TASK

AKA the wakey wakey wait, schedule a restart of SQL Server during the next team meeting.

Comments

Popular posts from this blog

DP-900 Microsoft Azure Data Fundamentals - Exam Prep

Reset