PssDiag / SQL Diag Manager is a freely available graphical tool in CodePlex (http://diagmanager.codeplex.com/). It provides easy way to configure what data needs to be collected from SQL Server and Windows Server for troubleshooting SQL Server performance issues.
This tool is used by Microsoft SQL Server support team to collect data to troubleshoot performance issues.
Let us see how to install, configure and collect data
Step 1: Once downloaded the setup from the above specified link, run the Setup.exe and click “Next” on Welcome screen
Step 2: Select your installation folder and then click “Next”
Step 3: Click “Next” to confirm the installation
Step 4: Click “Close” to confirm the installation
Step 5: Once the installation is successfully completed, click on Start Menu and open “PSS Diag” and then click on “PSSDiag Configuration Manager”
Step 6: To configure the PSSDiag, Please specify the following information Machine Name (SQL Server Name), Instance Name (If Default Instance, specify *), Select authentication
By default, SQL Server 2008 is selected, PerfMon counters associated will be selected and for Database Engine, there are 3 trace templates available
Select the Trace template and specify the Maximum size, If you set it to “None”, file size will be unlimited, Please make sure you collect only Trace information for required events, selecting all events will degrade the performance of your SQL Server
Select the custom diagnostics you wish to collect, “SQL Memory Error” and “SQL Blocking” is not selected by default, these 2 are useful Custom Diagnostics to troubleshoot any blocking issues or long running query issues
Even though You see option to Select “SQL Server 7.0” and “SQL Server 2000”, this utility doesn’t support those versions of SQL Server.
Step 7: To configure Analysis service engine collection information, click “Analysis Services” tab, you can customize the PerfMon counters, Trace information and Custom Diagnostics
Step 8: Save the configuration – Once finished configuring, click on “Save” button on top
Specify the path on which you want to save the configuration output file and click “Ok”
Specify whether you are planning to run this on SQL Server 2008 or SQL Server 2008 R2
Once the configuration files are generated and packed as CAB file, the following message will be displayed. Click “Ok” to complete configuration and Exit the tool
Step 9: Run the PSSDiag and Collect the performance data
Open Window Explorer and go to the folder where the PSSD.CAB file is stored and then open it and extract the files. To extract the data, you can use any Zip tool such as 7 Zip or Win Zip or WinRAR
Once extracted, double click on “PSSDiag.cmd” or run it from Command Line
Click “Yes” to proceed with running the utility to start collecting performance data
SQLDiag utility will start with specified configuration and will display series of messages as displayed below
You can let run the tool as long as you want to collect the data. To stop collecting the data and exit the Tool, Press “Ctrl + C”.
The output from SQL Diag will be stored in folder named “Output”, from the folder where you are running the tool, Ex. If you are running the tool from “C:\Workspace\PSSConfig\”, then the output folder will be “C:\Workspace\PSSConfig\output”
The output folder will contain lot of different files containing the information collected.
In the next post, we will see how to import this collected information, run reports and find out how to identify the performance issues.
4 thoughts on “PSSDiag / SQL Diag Manager–How to install, configure and collect performance data from SQL Server 2005/2008/2008 R2 ?–Part 1”
I have VM where non cluster SQL Server 2012 configured.
Now in VM machine I have local admin access and can access machine via login ID say “Administrator”.
Now in SQL Server 2012 I can login via selecting SQL authorisation via login ID say “SA” who has “SYSADM” privileges. But I am unable to login when I select window authentication.
Now I have installed latest version of pssdiag 220.127.116.111 and can configure for:
1. ADM 64
2. SQL Server 2012
3. Connecting using: SA Not windows authentication
4. Others by default
Save it default location.
Then I move to folder where pssdiag is installed then run pssdiag.cmd under build folder.
After green signal till ctrl + C not error popup. then after few min I stopped it and navigate to ouput folder but .trc file was not their.
Question 1. Why trc file not generated.
Other way when I select windows authentication is pssdiag I got attached message.
To fix this I added windows login ID “Authentication” in SQL server user and given sysadm role.
Then again when I select windows authentication is pssdiag I have not got above message in image file but again .trc file not generated.
Please help. and I also want to know in pssdiag, is machine name is host name? and instance name in DB instance?