SQL SERVER – SUSPENDED Sessions Waiting on PAGELATCH_UP and PAGELATCH_SH – Solution

Today, We faced very intersting thing, On one of Our SQL Serevr database were many sessions suspending in the status of SUSPENDED and they were waiting on PAGELATCH_UP and PAGELATCH_SH and application working very slowly.

Suspended Status

Suspended Status means that the request currently is inactive because it is waiting on a resource and there is a good chance the request will start once the needed resource will be available.

Solution

Problem is misconfiguration of the TempDB. I noticed that there was a single tempdb file on the same drive as their data drive and was creating a performance issue. 

We immediately moved that file to a different drive and added few more (in our case 7 more) TempDB datafiles (ndf) and our performance issues were automatically resolved and all the queries got unsuspended. 

After a few minutes, the entire backlog was cleared the client got unparallel performance.

Tempdb files per core recomendation

On Microsoft website, it recommends :

"Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs."