How to reduce THREADPOOL wait type

The THREADPOOL wait type is recorded when a task is waiting to get assigned to a worker thread. You would see this wait type appear in sys.dm_os_waiting_tasks. These are some of the causes I’ve seen causing the THREADPOOL wait type during SQL performance troubleshooting 1) High amount of connections require queries to run queries simultaneously. 2) SQL Server reaches the max worker threads. 3) High levels of long blocking – may force worker thread requests 4) Extended stored procedures Some other symptoms you may see: Thread pool starvation i.e no more worker threads to process requests By default max worker... Read more →

How to understand CXPACKET wait type resource description in Sys.dm_os_waiting_tasks

Question : When checking on waitin tasks through the Sys.dm_os_waiting_tasks view , I noticed in the resource description column some references which are unfamiliar to me . The wait type was a cxpacket and the resource description was similar to: exchangeEvent id=Pipe5677568b0 WaitType=e_waitPipeNewRow nodeId=2 How do I parse these details to make sense? Answer: The resource description column is a "description of the resource that is being consumed". For different types of wait types there are different nomenclatures. For the wait type cxpacket , the breakdown meanings are: exchangeEvent id={Port|Pipe} WaitType= nodeId= Believe it or not , this is useful... Read more →

How to troubleshoot Flush Cache error message in SQL Server Logs

Question: I’m getting a Flush Cache message in the SQL Server Logs. FlushCache: cleaned up 3606 bufs with 476 writes in 69033 ms (avoided 4 new dirty bufs) for db 7:0 What does it mean regarding sql server performance? Are there methods I should be using to troubleshoot this message Answer: The impact on SQL Server performance will depend on understanding the context . Checkpoint logging causes this message to appear in the SQL Server logs. Just to be specific, these messages only started appearing in the logs since 2012. Prior to 2012 you needed to turn on a trace... Read more →

Fix: –NESTING_TRANSACTION_FULL wait type and how to reduce it

Question: There was a failure on a regular SQL Server job. There are now repeated errors focused on NESTING_TRANSACTION_FULL. Double checked all usual resource indicators such as CPU,IO,Memory and all appear to be within normal boundaries Timeout occurred while waiting for latch: class 'NESTING_TRANSACTION_FULL', I checked the waiting tasks and discovered the wait type as LATCH_EX with resource description as : NESTING_TRANSACTION_FULL (0000000E64CA80F8) Answer:This is a tough nut to crack. I’ve experienced this problem in the past under different circumstances. These are some of the methods I’ve used to solve or alleviate the symptoms. Without full understanding whether it was... Read more →

How to Reduce the WRITE_COMPLETION wait type

SQL Server Books online defines WRITE_COMPLETION as “Occurs when a write operation is progress”. This definition doesn’t give a deep insight into the activity types which trigger the WRITE_COMPLETIONB wait type The definition is fairly generic and requires some analysis to identify when it occurs and if it’s causing a problem. Some examples of when WRITE_COMPLETION occurs: 1) Copying pages from the model database into a database during the CREATE DATABASE process 2) Creating Global Allocation Map (GAM) pages 3) Create and mark Page Free Space (PFS) pages byte-map 4) Expanding a Database data file grow command will initiate page... Read more →