This was a very interesting situation and at the end of we had found the culprit for the slow query and resolved it. Today we will discuss the experience.
SQL SERVER – Detecting CPU Pressure with Wait Statistics
I eventually expressed them it is possible to detect CPU pressure with Wait Statistics. We explained to them my method during the Health Check.
SQL SERVER – Changing Max Worker Count for Performance
I wrote a blog post about SQL SERVER – Best Value for Max Worker Count, after reading this blog post, I have received lots of emails.
SQL SERVER – Query Specific Wait Statistics and Performance Tuning
After a quick research we figured out the issue was with memory requirement for their query and we used to query specific wait statistics.
SQL SERVER – What is Wait Type Parallel Backup Queue?
Only official documentation which I could find was about wait statistics related to Parallel Backup Queue is very little available online.
SQL SERVER – SUSPENDED Sessions Waiting on PAGELATCH_UP and PAGELATCH_SH – Solution
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.
SQL SERVER – Automatic Seeding of Availability Database ‘DB’ in Availability Group ‘AG’ Failed With an Unrecoverable Error
While deploying Always On Availability Group with a client, they found that when they use automatic seeding, the database was not shown on secondary. After digging more, we found that it was there earlier in “Restoring” state when seeding was in progress but then it automatically disappeared from the list of databases on secondary. Here is the option in the wizard.