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

Today, I have interesting experience while working with a client on Comprehensive Database Performance Health Check. I was working with Sr. DBA on optimizing their SQL Server. While working on it, we realized there was an issue with how their file configured for one of their major databases. As soon as we fixed that issue, suddenly their application started to run at rapid speed. However, the joy of fixing the server did not last long as within a few minutes their server got tremendously slow with many sessions suspending in the status of SUSPENDED status and they were waiting on PAGELATCH_UP and PAGELATCH_SH. I have fixed many such scenarios in the past but the way this scenario showed up this was very strange.

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.

When I started to see the queries are suspended with the wait stat of PAGELATCH_UP and PAGELATCH_SH, it was immediately clear to me that this may be due to some internal SQL Server issue which was unrevealed as soon as I fixed the major blocking issue with the file.

Solution

After carefully looking at the situation, and evaluating various different performance tuning configuration issues, I realized that the issue was due to another 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 3 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.

If you have start SQL Server Performance problem, I would love to connect with you and help you resolve it. I love solving challenging issues. Let us work together Comprehensive Database Performance Health Check

Related Blog Posts

How to check SQL Server Wait Statistics – Blog

How to check memory pressure? – Blog

How to move TempDB – Blog

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL FileGroup, SQL Memory, SQL Server, SQL TempDB, SQL Wait Stats
Previous Post
SQL SERVER – 5 Performance Optimizations Must Do for TempDB
Next Post
SQL Server Performance Tuning Practical Workshop – Discovery Phase – In Person Training

Related Posts

4 Comments. Leave new

  • Hi Sir,
    we are getting more PAGELATCH_EX and PAGELATCH_SH wait type for Update query during the peak hours .
    due to this backlog is reaching 7L and update query getting the query has timed out.

    Error: sqlserver.jdbc.sqlserverException: The Query has timed out.

    could you help me on this sir.

    Reply

Leave a Reply

Menu
Exit mobile version