Mat Hayward-Hill shows how to use PSSDIAG and SQL Nexus. First, PSSDIAG:
Once you have worked on enough SQL Server performance issues, you start to develop an approach and lean on the same tools, to collect and present your data. I will walk you through how I troubleshoot a SQL Server performance issue, having no prior knowledge of the system. Typically, the description of the problem goes along the lines of “it was fine last week, but today queries are taking forever or timing out.”
If the issue is with one query, I will start with the Query Plan. Otherwise, when the problem is system-wide, and the SQL instance sits on top of a Windows on-prem or IaaS server, I use PSSDIAG to gather the data.
Then, SQL Nexus:
This post is the second part of how I troubleshoot a SQL Server performance issue like a pro. In Part 1, we used PSSDIAG to collect performance data. Now, we will look at how to load and analyse that data using SQL Nexus.
Before we dive straight into SQL Nexus, there are a few prerequisites that you need to install. I will confess I didn’t get this right on my first attempt so to make it easier for you, I have included the requirement along with the link of where to find it.
Check out both posts.