-> Pssdiag can be downloaded from https://github.com/Microsoft/DiagManager/releases.
-> Download the “latest release”.
-> Extract the file. In my case it was “DiagManager13.0.1600.32”.
-> In the extracted folder, open “DiagManager”.
-> This is what i see in my workstation.
1) Machine Name – Run “Select @@servername” on the SQL server instance for which you need to troubleshoot. In the output the characters before “\” should be the “Machine Name”. You can give “.” also in the machine name, but it will be difficult to understand for which SQL server you created the PSSDIAG at a later stage.
2) Instance Name – Run “Select @@servername” on the SQL server instance for which you need to troubleshoot. In the output the characters after “\” should be the “Instance Name”. If it is a default instance, type “MSSQLSERVER”. You can use “*” also in the Instance Name. But in case you have multiple instances, a “*” will cause the PSSDIAG to collect data for all the instances.
3) Choose appropriate Platform.
4) Choose appropriate Version.
5) Scenario – Select appropriate scenario(s). This is a template when selected, will select appropriate Xevents, trace, perfmon etc from tabs marked 6 to 10 in the screenshot. For example, If I select “General Performance”, things related to it are selected. You can also select additional things from tabs marked 6 to 10 if required.
6) Xevent – Select appropriate Xevents. Select Capture checkbox if you need it or uncheck if you dont need Xevents.
7) Profiler Trace – Select appropriate trace counters. Select Capture checkbox if you need it or uncheck if you dont need trace.
8) Perfmon – Select appropriate perfmon counters. Select Capture checkbox if you need it or uncheck if you dont need perfmon.
9) Custom Diagnostics – Select as appropriate. The default looks perfect always for me.
10) Misc – if you select “Startup” and “Shutdown” checkbox. It will collect the event logs and SQLDIAG DMV’s once during startup and then during shutdown of PSSDIAG. The output folder should be an appropriate location. I avoid C: drive and drives where you have the data, log and backup files.
-> Once the required inputs are keyed in. Click on Save and save the PSSD.zip file.
-> Extract the file and navigate to the extracted location from an admin command prompt.
-> Type “Dir PSSD*” and check if you see pssdiag.cmd.
-> Type PSSDIAG.cmd and press enter. Make sure you see the below,
-> Once you have collected the required data. Press Control + C only ONCE and wait for it to complete.
-> Navigate back to the output folder and you can look at the files manually or load the data in SQLNexus.
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.