PSSDiag / SQL Diag Manager–How to install, configure and collect performance data from SQL Server 2005/2008/2008 R2 ?–Part 1
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.
One of my friend recently asked me a question, How to view the list of features installed in the SQL Server easily without going through “Programs and Features” or “Services”.
Yes, there is an easy way
SQL Server 2008 / 2008 R2 / Denali provides “Installation Center” using which we can view features of all locally installed SQL Server versions 2000 / 2005 / 2008 / 2008 R2 / Denali including the SQL Express editions
Please follow the below steps to do that
1. Click on “Start” –> “All Programs” –> “Microsoft SQL Server 2008 R2” –> “Configuration Tools” –> “SQL Server Installation Center
2. Click on “Tools”
3. Click on “Installed SQL Server features discovery report”
The following report will be generated and opened in your browser.
The report displays the SQL Server version Name, Instance Name and its ID, Feature Name, Installed Language ID, SQL Server Edition, Version Number and whether its clustered or not.
Output from SQL Server “Denali”
I hope you all find this useful…
SQL Server “Denali” adds a new feature in Error Handling called “THROW”
Current version already has RAISERROR, which you can already use to raise Error from your procedures, then why do we need this new “THROW” option,
Let us see the differences
/* RAISERROR */ BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH DECLARE @ERRORNUMBER INT = ERROR_NUMBER() DECLARE @ERRORMESSAGE VARCHAR(500) = ERROR_MESSAGE() SELECT @ERRORNUMBER, @ERRORMESSAGE RAISERROR(@ERRORNUMBER, 16, -1) END CATCH
In the above code, we are creating Divide by Zero error in the Try Block and then Catching that Error in Catch Block and Display the Error details and then need to return the same error to calling application
Output from SELECT:
Msg 2732, Level 16, State 1, Line 11 Error number 8134 is invalid. The number must be from 13000 through 2147483647 and it cannot be 50000.
RAISERROR(@ERRORNUMBER, 16, –1) causes the above error instead of returning that error to application
See the same implementation using THROW and how it works
/* THROW Demo */ BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR); PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR); THROW; END CATCH
(0 row(s) affected) Error Number: 8134 Error Line: 5 Msg 8134, Level 16, State 1, Line 5 Divide by zero error encountered.
The Error number and Error Line was printed and then the same error was thrown back to calling application
You can use THROW to log errors in CATCH block and return the same error back to calling application easily.
SQL Server “Denali” is the new upcoming version of SQL Server, it has numerous new features
Let us review the new built-in Paging feature “FETCH FIRST”, “FETCH NEXT”
–How to Fetch 1st Page of 10 Rows
DECLARE @PageNumber INT = 1
DECLARE @PageSize INT = 10
ORDER BY [Name]
OFFSET (@PageNumber * @PageSize) – @PageSize ROWS
FETCH FIRST @PageSize ROWS ONLY
–How to Fetch 2nd Page of 10 Rows
SET @PageNumber = 2
ORDER BY [Name]
OFFSET (@PageNumber * @PageSize) – @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;
— How to Fetch all records skipping first 100 Records
ORDER BY [Name]
OFFSET 100 ROWs
— How to implement Customizable Paging support using the new feature
–Create a Personalization table to store PageSettings
CREATE TABLE PageSettings
–Add some Page setting Records
INSERT INTO PageSettings (LoggedInUserName, PageSize) Values (‘bill’,10)
INSERT INTO PageSettings (LoggedInUserName, PageSize) Values (‘gates’,20)
— Create a Sample Stored Procedure to implement customized paging
CREATE PROCEDURE sp_FetchProducts
ORDER BY [Name]
OFFSET 10 ROWS
FETCH NEXT (SELECT PageSize From PageSettings WHERE LoggedInUserName=@LoginName) ROWS ONLY
–Execute the Stored Procedure under login “Bill”
EXEC sp_FetchProducts @LoginName = ‘bill’
Output: Stored Procedure returns 10 Records
–Execute the Stored Procedure under login “Gates”
EXEC sp_FetchProducts @LoginName = ‘gates’
Output: Stored Procedure now returns 20 Records
You can even customize this solution further Table wise as well.
I hope all the SQL developers will enjoy this feature, since it reduces lot of coding for implementing paging using Table Variables or Temp Table or CTEs.
SQL Diag Configuration Tools is an easy to use tool which will help you in configuring SQLDiag.xml to collect performance data from SQL Server 2005/2008/2008 R2
SQL Diag Configuration Tool is a free tool available on Codeplex. You can download it from the following link
Let us walkthrough the steps of using SQL Diag Configuration Tool
After installation, run the tool
Select your desired SQL Server version and then click on “Authentication Method” option available on left side
Specify the Server name and SQL Server Instance Name on which you are planning to use the SQLDiag.xml and then click “Machine Wide Diagnostics” option
Select the Events Logs you want collect as part of performance data and then click on “Instance Specific Diagnostics”
If you want to collect Blocking information, select “Blocking” and the specify the Maximum File Size of log and the maximum number of files and the polling interval on which the Blocking information should be collected from SQL Server
Next Click on “Profiler" Options
Click “Enable SQL Tracing” to collect Profiler information and select a Trace Template.
Available Trace Templates are
After selecting, you can also add further Events you want to collect and then specify the maximum file size, maximum number of files and polling interval on which profiler information should be collected from SQL Server
Next click “Perfmon Options” to proceed
Select the Performance Counters which you want to collect or select the predefined template available and then specify Maximum File Size and the polling interval on which performance counter data should be collected
Next Click on “Producers” to enable the Producers options
Next Click on the “Analyzers” to enable the required Analyzers
Click “Save” to SQLDiag1050.xml and use it as input for SQLDiag to collect SQL Server Performance Data
You can use the “Open” option reload the saved SQLDiag xml file and modify the profiler or perfmon or other above specified options.
I hope you find this useful…
SQL Server Migration Assistant for Access 5.0 has been released and available for download here
New version now supports SQL Server “Denali”