Archive

Posts Tagged ‘SQL Diag Manager’

SQL Nexus–How to generate Reports from Performance Data collected and imported ?–Part 3

June 5, 2011 2 comments

In previous posts we saw  How to install, configure and collect performance data from SQL Server 2005/2008/2008 R2 ? and  How to import Performance Data using SQL Nexus ?

In this post we will see How to generate Reports in SQL Nexus 

The following are the list of reports available

image

Blocking and Resource Wait Statistics Report

clip_image001

Bottleneck Analysis Report

clip_image002

Bottleneck Analysis –Drill down sub report

clip_image003

Bottleneck Analysis –Drill down sub report – Wait Statistics

clip_image004

clip_image005

SQL Server 2005 Performance Statistics Reports

clip_image006

Bloaking and Wait Statistics and Bottleneck Analysis reports are linking to Reports we saw earlier

Spinlock Statistics Report – Spinlock is a lightweight, user mode synchronization object used to protect a specific structure. The goal of this report is to see there are no collissions and spin.

clip_image007

clip_image008

SQL Server 2008 Performance Statistics Reports

Blocking and Wait Statistics, Bottleneck Analysis and Spin Lock Stats reports links to the above reports we saw.

clip_image009

Query Hash Report – This report show you the list of queries with details of How many times it executed, How many query plans were created, Total CPU time taken, Total Duration to execute the query and Total Logical IO Reads

clip_image010

clip_image012

Query Hash Report – Performance Overview Drilldown report

clip_image013

clip_image014

Query Hash Report – Top Unique Batches Drilldown charts

clip_image015

Query Hash Report – Top Unique Batches Details report

clip_image017

Query Hash Report – Resource Consumption report

clip_image018

clip_image019

clip_image020

clip_image022

clip_image024

Query Hash Report – Top Unique Batches Details Report

clip_image028

Interesting Events Report shows the number of times events like Auto Stats Updation, Lock Escalation, Missing column statistics, etc… is occurring along with Trace Event ID, you can also drill down into each event and see the query caused the event as well

clip_image029

Interesting Events – Drilldown Report

clip_image030

clip_image032

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.

%d bloggers like this: