Archive

Posts Tagged ‘SQLDiag’

SQL Nexus–How to import Performance Data collected using PSSDiag ?–Part 2

May 31, 2011 4 comments

In the last post, we saw how to install, configure and collect performance data from SQL Server 2005/2008/2008 R2, in this post, we will how to import that data using SQL Nexus and get ready to prepare Performance Analysis Reports

SQLNexus is a free tool developed by SQL Server support team and used for importing and analyzing performance data collected using SQL Diag and PSSDiag. Its helps identifying the Bottle necks, blocks, CPU consuming queries, Wait, Locks information, etc…

SQLNexus is freely available for download from http://sqlnexus.codeplex.com/

To run SQLNexus, you need to install the following prerequisites

.Net Framework 3.5

SQL Server 2008 SP1 Report viewer control (http://www.microsoft.com/downloads/details.aspx?FamilyID=bb196d5d-76c2-4a0e-9458-267d22b6aac6&DisplayLang=en)

SQL Server 2005 or above – To store imported performance data. You can also use SQL Server Express Edition

ReadTrace (RML Utilities for SQL Server)

x64 – http://www.microsoft.com/downloads/en/details.aspx?FamilyId=B60CDFA3-732E-4347-9C06-2D1F1F84C342&displaylang=en

x86 – http://www.microsoft.com/downloads/en/details.aspx?FamilyId=7EDFA95A-A32F-440F-A3A8-5160C8DBE926&displaylang=en

After installing all prerequisites, download the SQLNexus from the above site and store it any local folder and then extract the zip file SQLNexus3.0.0.0.zip

image

Double click or Run “SQLNexus.exe”

image

On loading of the application, it will prompt you to connect to SQL Server instance on which you want to create the SQLNexus database, specify the Server Name, Authentication information and click “Connect”

Its not recommended to run this on your production server, since it will create lot of performance loads while importing the data and running the reports

If RML Utilities is not installed, you will get the below error message

image

Without RML Utilities, Trace files (.trc) will not be imported

image

On successful connection, you will see the above Main Dashboard screen, To start the import process, click on “Import” link left hand bottom side of the screen

image

Click Folder Browse icon and select the folder on which your Output data from PSS Diag and SQL Diag tool is stored.

Please make sure there is no spaces in the folder names, If you have space in the folder name, you will  not be able to import the Trace files

To customize the import options, click on “Options”

image

Select the required options and if you want to retain the options you selected, click on “Save My Options”, for any reason, if you need to reset all the options you selected to default options, click on “Restore Default Options”

Use “Drop Current DB Before Importing” option to drop and recreate the database every time, this option will be helpful, if you want to load clean set of data every time.

Once finished customizing the options, click on “Import” button to proceed with the import

image

You will receive the above warning message, if you have selected “Drop Current DB Before Importing” option. click “Yes” to proceed with drop database and import

image

The above screenshots shows the import process and list of various files imported along with the number of records imported and also shows us whether the file imported successfully or not.

If you notice, the second line, Import for DBCC_MEMORYSTATUS_Startup.out file has failed, this is a known issue, there is no report currently available for DBCC MEMORYSTATUS, due to which this import will fail always, to follow up about this issue, please visit this discussion link CodePlex discussion forums http://sqlnexus.codeplex.com/workitem/11455 

Now the import has completed successfully.

Click on File Menu and then click on “Run All Reports” to generate the reports

image

After the reports are generated, the Report link icons will change as follows

image

Since we don’t have any data related to SQL Server 2000, it will remain unchanged.

In the next post, we will explore the reports generated by SQLNexus and see various information available through them.

I hope you all find this information useful. Please post your comments !!!

Advertisements

PSSDiag / SQL Diag Manager–How to install, configure and collect performance data from SQL Server 2005/2008/2008 R2 ?–Part 1

May 29, 2011 4 comments

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

clip_image001

Step 2: Select your installation folder and then click “Next”

clip_image002

Step 3: Click “Next” to confirm the installation

clip_image003

Step 4: Click “Close” to confirm the installation

clip_image004

Step 5: Once the installation is successfully completed, click on Start Menu and open “PSS Diag” and then click on “PSSDiag Configuration Manager”

image

Step 6: To configure the PSSDiag, Please specify the following information Machine Name (SQL Server Name), Instance Name (If Default Instance, specify *), Select authentication

clip_image006

Trace Template

By default, SQL Server 2008 is selected, PerfMon counters associated will be selected and for Database Engine, there are 3 trace templates available

image

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

Custom Diagnostics

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

image

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.

clip_image007

Step 7: To configure Analysis service engine collection information, click “Analysis Services” tab, you can customize the PerfMon counters, Trace information and Custom Diagnostics

clip_image008

Step 8: Save the configuration – Once finished configuring, click on “Save” button on top

clip_image009

Specify the path on which you want to save the configuration output file and click “Ok”

clip_image010

Specify whether you are planning to run this on SQL Server 2008 or SQL Server 2008 R2

clip_image011

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

clip_image012

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

clip_image013

clip_image014

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”

clip_image015

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.

SQL Diag Configuration Tool

May 1, 2011 Leave a comment

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

http://sdct.codeplex.com/ 

Let us walkthrough the steps of using SQL Diag Configuration Tool

After installation, run the tool

image

Select your desired SQL Server version and then click on “Authentication Method” option available on left side

image

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

image

Select the Events Logs you want collect as part of performance data and then click on “Instance Specific Diagnostics”

image

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

image

Click “Enable SQL Tracing” to collect Profiler information and select a Trace Template.

Available Trace Templates are

image

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

image

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

image

Next Click on the “Analyzers” to enable the required Analyzers

image

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…

%d bloggers like this: