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)
5 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.
Your queries need to be tuned.
Thank you so much for your reply.
mine is direct update query sir
Thank you so much for your reply.
mine is direct update query sir
HI,
update statements are getting block with this Pagelatch_up and as soon as stats are updated then they work fine, can you please provide your suggestions here and this is reoccurring every day.