Applications Connecting to SQL Server timing out
- Excessive blocking with the wait resource being tempdb PFS page
- High CPU utilization on the server
sys.dm_exec_requests reported blocking and the head blocker looked like this
session_id ecid wait_type resource
---------- ----- --------------- --------------------
58 4 PAGELATCH_UP 2:7:32352
58 1 CXPACKET
58 5 CXPACKET
58 6 CXPACKET
58 3 CXPACKET
58 2 CXPACKET
58 0 CXPACKET exchangeEvent id=Pipe7dd014b80
The wait resource was always reported at tempdb PFS page (2:7:(8088*x)).
The head blocker and the blocking session were executing a SELECT against a XML document.
As explained here parsing a XML document would create work table in tempdb. In this situation the application was parsing a lot of XML documents. Hence the contention on tempdb was justified.
- tempdb had 5 data files and the data files had initial size of 100 MB (with autogrowth of 10%). As per the recommendations here, increased the tempdb files to 8 and increased the initial size to a higher value to reduce the auto growth.
- Also enabled trace flag 1118. This forces uniform extent allocations instead of mixed page allocations.
- Enabled trace flag 1117. This will auto grow all the files in the filegroup whenever one of the files tries to auto grow.
- Since this instance was running on SQL Server 2012, applied the latest Service Pack + CU as recommended in http://support.microsoft.com/kb/2964518
The tempdb contention got reduced to some extent still the CPU utilization was very high(above 85%). Because of this the application was still timing out.
- Changed the Power Plan to “High Performance” as explained in http://support.microsoft.com/kb/2207548. This didn’t help much.
- As you notice in the sys.dm_exec_requests output, all threads in the session are waiting on CXPACKET wait type for one thread to complete its work. Hence set the appropriate value for max degree of parallelism (3 in this case) as explained here.
CPU utilization came down from 85% + to 20-30%