Install and Configure SQLNexus

-> Install “Microsoft System CLR types for SQL server 2012” from https://www.microsoft.com/en-in/download/details.aspx?id=29065. Once you open the link, Click on “Install Instructions”. Search for “SQLSysClrTypes.msi” and Install the file  as per your platform.

-> Install “MICROSOFT REPORT VIEWER” from https://www.microsoft.com/en-us/download/details.aspx?id=35747

-> SQLNexus can be downloaded from https://sqlnexus.codeplex.com/.

-> Save and extract the file. In my case the file is “SQLNexus5.5.01.zip”.

-> Navigate to the extracted location and double click “sqlnexus.exe”.

blog11_3

1) Enter the SQL server instance name where you need to load the PSSDIAG output data and click connect. Usually SQL Nexus should be installed on a test server or on your desktop. It should never be installed on production server and used for loading the data.

2) Create a new database from SQL server management studio and select it in the “Current DB” combo box or select “<New Database>” from “Current DB” combo box and enter proper database name where the PSSDIAG output should be loaded.

3) Click on “Import” which is marked as 3 in the above screenshot and provide the PSSDIAG output folder location. For  example, f:\temp\pssdiag\output. Wait for the import to complete. Once the import completes you can follow the below,

blog11_4

Analyzing the data from SQL Nexus

1) Once the import completes. Click on “SQL Perf Main” and you will see all the available report on the Right side.

2) Click on the required reports and analyze further. You an also look at the PSSDIAG  database directly from the SQL server management studio.

Thank You,
Vivek Janakiraman

Disclaimer:
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.

Install and Configure PSSDIAG

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

blog11_1

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,

blog11_2

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

Thank You,
Vivek Janakiraman

Disclaimer:
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.