Archive

Posts Tagged ‘SQL Nexus’

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 !!!

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 !!!