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 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.
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)