With sufficient thrust, pigs fly just fine.

Menu

Performance Analysis with PSSDiag and SQL Nexus

Overview

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!

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.

Summary

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.

Post navigation

2 thoughts on “Performance Analysis with PSSDiag and SQL Nexus”

Excellent effort! Thanks!
I almost gave up on these tools. But so many recommend them that I understand it is worth the trouble to get it going. Unfortunately I still failed half way. I reached “Run the PSSDiag Collection Tool” step 10. I realized had to launch command prompt with Admin privileges. This helped me one step further. I launched pssdiag.cmd and noticed how SqlDiag tool was initiated. I then receive for lines with the following identical error: “SQLDIAG Failed to open System\CurrentControlSet\Control\Session Manager\Environment . Function result: 5. Message: Access is denied.”
Any suggestions on how to overcome this?

Do you have local admin privileges? It is having trouble opening an area of the registry. You will have to run all software in administrative mode so it can access all the resources it needs. It will probably be launching other processes so you might just have to turn off access control all together. Let me know how it goes.

I have an Excel file where I'm connecting to 3 different SQL servers to come up with data for a worksheet. Here's what is happening in the VBA procedure. Connect to SQL Server 1 ("SqlSrv1").Run query on SqlSrv1 returning around 13K records in an ADO RecordSet ("Rs1").Truncate worksheet ("WrkSht1").Copy Rs1 to a WrkSht1 […]

I have a procedure where I need to select data using 3 different queries from 3 different SQL Servers. I've got it setup now so that the data will be pulled into 3 different recordsets. What I need to do is iterate the first and ensure that any occurrences of the 2nd recordset within the first get removed. Then I need to ensure that only those that exis […]

Hello, I am running win7 rc 64bit build 7100 in Dual monitor mode. I have a problem finding/moving an applications window that has gone out of the viewing area. I am new to Vista/Win 7 interface and not familiar with how to move a window with the arrow keys as I do with XP by right clicking on the running application in the taskbar and selecting Move. The ap […]

I just ran across something interesting. I compiled my code, ran a Code Analysis on the solution and wound up with a few CA2202 "Do not dispose objects multiple times" warnings. This was kind of odd since I didn't think I'd done anything like that. After checking through my code I was sure I hadn't. However, the warnings persisted. T […]

I've got the following piece of code which should test a connection by attempting to open it. Whether it succeeds or fails the connection should then be closed and manually disposed of. Here are my two attempts at this. Standard Code var sqlConn = new SqlConnection(csb.ConnectionString); try { sqlConn.Open(); } catch { failed = true; } finally { sqlConn […]

I'm attempting to instantiating the following class in order to retrieve network adapters. System.Management.SelectQuery.SelectQuery(string, string) In order to eliminate network adapters I'm not interested in retrieving, I'm specifying multiple conditions in the 2nd parameter. var query1 = new SelectQuery("Win32_NetworkAdapter", […]

Hi!I'm new to sql and would appreciate it if anyone could show me the query that i need to type to get the server version information - I do not have direct access to the sql database (doing some work on an old website that runs on windows box w/ coldfusion :p)appreciate it!

I have a quick question regarding the Authenticated Users "group". I used to be a systems administrator, but I'm a bit rusty since I've been a software developer for the last 10 years. A conflict with data center operations (DCO) group at work lead me to get another opinion. The question is this... is the authenticated users group a domai […]

I have an SSRS 2005 report that I want to enable for multi value. However, I always run into the issue with the way it passes multiple values. How exactly do I get a multi-value parameter setup and working on both the report AND SQL side? I used the MS example of checking the multi checkbox for the parameter and then using a WHERE Column IN (@MultiParam), bu […]