Yesterday I was experiencing extremely slow connectivity to the SQL Server with connections via management studio taking 5+ seconds where normally connecting to the server occurs within seconds. This issue both happened when connecting to the SQL Server on the actual server VM and then also from client pcs. Executing requests on their own seemed to be fine once connected, we just appeared to have issues connecting to the SQL Server it self.
CPU Utilisation had spikes but averaged around 75% with memory also around the 85 % mark suggesting resources were available for clients to connect.
Given that we are not experiencing the issues right now, is there any way I can troubleshoot what was causing it and if not and should we experience the issues again, is there anything I should look for in particular with Extended Events when troubleshooting?
Thanks
Best Answer
There is no way to see performance in the past unless you have a monitoring system in place during the event in question.
Assuming you don't have a monitoring solution in place, you could be prepared in case this happens in future.
For instance, you could look at wait stats over a time period using my wait stats capture script. The script shows what SQL Server actually waited on over a short period of time. For instance, if you happened to run that script while the problem in your questions was occurring, it's possible the output might show thread starvation via the
THREADPOOLwait. Check Paul Randal's site for details about that wait.