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).
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_
Migrate all currently implemented HADR solutions to use merge replication.
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 lame. 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 Service Pack 33.
THREADPOOL
Check max worker threads for default value of 0 and promptly 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.
No comments:
Post a Comment