Although there are many means of obtaining performance information from SQL, that’s exactly the problem. You can setup performance counters, look at statistics in SQL itself, look at logs, perform a trace in SQL, look at system information and so on. You can also correlate things like performance logs together with SQL traces to get a bigger picture. This is labor intensive though and only includes two sources of information. Wouldn’t it be great if you could compile all of the pertinent information together into meaningful reports and do so easily? Well you can with PSSDiag and SQL Nexus.
PSSDiag and SQL Nexus are not very intuitive and there isn’t a lot of documentation for these tools. You will also notice quite a lot of steps on this page. I know it may look complex, but the steps are actually quite simple and I could have condensed it into just a handful. However, I wanted to make sure anyone could do this.
Once you get this setup and do it a few times, it will give you an entirely new picture of your SQL Servers. You will be coming up with rapid answers without all the slaving over your system that you typically have to do to gather this information. You can do this! Let’s get started!
Obtaining the Software
- Visit the Microsoft PSSDiag site here to download the PSSDiag tool.
- Read the instructions provided on the GitHub site to install PSSDiag.
- Note the installation folder as you will need this later on.
- Go to the CodePlex site here and download the SQL Nexus tool.
- Read the instructions provided on the GitHub site to install SQL Nexus.
- Go to this CodePlex page to download and install ReadTrace.
Setting Up PSSDiag
- Run the PSSDiag Tool by selecting the “Pssdiag Configuration Manager” item in the Pssdiag folder in the Start Menu.
- Select the appropriate processor architecture from the images under Platform
- If unsure use Intel Inside Pentium for 32-bit or AMD 64 for 64-bit systems.
- Select the tab at the top of the window corresponding to the SQL Server version you will be collecting information against.
- In the Diag Manager window that appears set the Machine name field to the name of your SQL Server.
- Set the Instance name field to the instance name of your SQL Server or astrisk (*) if it’s the default instance.
- Set the Connect using fields appropriately for your authentication method.
- In Machine-wide Diagnostics check the following items you would like to capture data for with regard to the system SQL Server is running on.
- Event logs: Windows Event Logs.
- Startup: Windows system startup.
- Shutdown: Windows system shutdown.
- Perfmon: Performance Monitor counters selected in checked listbox below.
- Max file size: The maximum amount of data to capture in MB.
- Interval (sec): The polling sample window in seconds.
- Checked listbox: Check all perfmon counters to capture data for (check the SQL Server counters if you aren’t sure).
- In Instance-specific Diagnostics check the following items you would like to capture data for with regard to the specific SQL Server instance noted in the Instance name field (#3 above).
- SQLDIAG: Collect data from general purpose SQL collection utility for logs, data files, etc.
- Startup: SQL Server startup.
- Shutdown: SQL Server shutdown.
- Database Engine (tab):
- Trace: Gather SQL Server trace information.
- Template: Use a predefined tracing template to define collection events.
- Max file size: The maximum amount of data to capture in MB.
- Checked listbox: Define a custom set of collection events to gather data for.
- In Custom Diagnostics check the miscellaneous other tools you want to gather data from.
- Note: For each of the items in this listbox, highlight the item and read the Instructions window contents at the bottom of the tool window for more information.
- Rearrange the order of collection by using the up and down arrows.
- Save the configuration package and tools by clicking the Save button on the toolbar.
- Leave the existing paths and file names (should be \Build and \Customer folders).
- If another dialog appears asking you for the SQL Server version, select the appropriate option.
- Close the PSSDiag Tool.
Run the PSSDiag Collection Tool
- Open Windows Explorer and navigate to the PSSDiag installation folder you noted when installing PSSDiag (e.g. C:\Pssdiag).
- Navigate to the Customer folder inside of the root PSSDiag installation folder (e.g. C:\Pssdiag\Customer).
- Choose a folder you wish to use when running this collection tool, we’ll call it the collection folder (e.g. C:\Users\Me\Documents\PssDiag).
- Copy the pssd.cab file into your new collection folder.
- In Windows Explorer (it won’t work otherwise) double-click on the pssd.cab file and it will open as if it were just another folder.
- Select all of the files and copy them (i.e. CTRL+A then CTRL+C).
- Go back up a folder so you’re in your collection folder.
- Paste the files you just copied into the collection folder (i.e. CTRL+V).
- Note: Here is where you will want to spend a few minutes getting your system ready. You don’t want to be collecting information that’s not related to the issue, such as starting your development tools or closing all the open apps you have running.
- Once you’re ready to start collecting data, find the file called “pssdiag.cmd” and double-click it.
- You will see a command prompt window open up and possibly a System Information window pop up. Don’t do anything until you see the text, “SQLDIAG Collection started. Press Ctrl+C to stop.”
- Once you see the above statement in the command window start your test scenario.
- This could be using an app that’s been running slowly and uses a database on the server you’ll be collecting information against.
- It could be running a set of scripts that aren’t performing the way you expect.
- Just focus on only performing the steps that you want to get more insight into.
- Once you are done running your test scenario, press CTRL+C.
- When the command prompt displays the prompt, “Terminate batch job (Y/N)?”, press Y and then ENTER.
Setting Up the SQL Nexus Tool
- Run the SQL Nexus Tool by selecting the “SQL Nexus” item in the “SQL Nexus 3.0” (or some future version) folder in the Start Menu.
- Choose a SQL Server that you would like to store your SQL Nexus database on.
- In the Connect to Server dialog that pops up, enter the Server Name you just chose and authentication information to that server.
- If you have never started SQL Nexus before, it will automatically connect to the server and create the “sqlnexus” database.
- If you have created this database before, but deleted it and get a message that it still exists, check the SQL data folder to ensure the database and log files are deleted.
- You will now be presented with the SQL Nexus tool.
Running the SQL Nexus Tool
- Technically the tool is already running from the step you just perfomed; if not follow step 1 in the Setting Up the SQL Nexus Tool section above.
- Click the Import item in the Data panel on the left-side tool window.
- Set the Source path field to the “output” folder inside the collection folder you created previously and click OK if you browsed for the folder.
- If you have imported previously with this database click the Options link and select Drop Current DB Before Importing.
- If you don’t do this you’ll get an error since you can only perform one import into the database.
- Click the Import button.
- If you chose to drop the current DB before importing, click Yes on the “Danger” dialog box that appears.
- Wait for all of the Data Import steps to complete and then close the Data Import window.
- Now start opening items from the Reports panel on the left-side tool window.
- Navigate through clickable links in the reports to see all that the reports can show you.
That’s it! You’ve just setup the tools, collected data, analyzed it and viewed reports. After setting everything up for the first time you can start with step 9 in the “Running the PSSDiag Collection Tool” in the future. If you want to change what sources you’re collecting data from you’ll need to go back through all of the sections to create a new configuration and collection package.
Let me know what you think and if this is helpful by leaving a comment.