SQLDiag and SQLNexus Use the tools support uses. - PowerPoint Presentation

Slide1

SQLDiag and SQLNexus

Use the tools support uses.

Lisa Gardner

Premier Field Engineer

Slide2

Introduction to

SQLDiag and Diag

ManagerIntroduction to SQL NexusConfiguring, Collecting, and Importing DataAnalyzing the Results

Slide3

SQLDiag & Diag Manager

Slide4

SQLDiag

Command line utility that ships with SQL Server

Located in the installation

Binn

directory

Gathers

perfmon

logs, error logs, profiler traces, blocking information,

etc

Requires and XML configuration file

This XML file specifies what to collect Can add custom collectors – allows you to grab the information you needYou execute a PSSDIAG file, which in turn uses SQLDIAG under the coversPSSDIAG  SQLDIAG  Collectors

Slide5

What I use to create a pssdiag

for youGUI tool used to create configuration fileFree download from Codeplex

The more you configure to trace, the more impact you may have on performance Use trace sparingly

Diag

Manager

Slide6

This is the REAL power of using

Diag Manager & SQL NexusDiag Manager can capture any scripts you specify and SQL Nexus can import them into a database

Once imported, you can run your own diagnostic scripts to find problemsMore on this later…

Capturing Custom Data Collections

Slide7

Custom Collections are added to the CustomDiag.XML file in the _MyCollectors

folderIt is usually quicker to modify this XML file to add collections than it is through the UI

Capturing Custom Data Collections

Slide8

Add your SQL scripts to the _MyCollectors

DiagManager folderC:\Program Files (x86)\Microsoft\

Pssdiag\CustomDiagnostics\_MyCollectors

Make sure the

resultsets

have a tag that uniquely identifies them

We will use this tag to import the data into SQL Nexus

Diag

Manager Custom Collection

Slide9

Configure a collection with Diag Manager

Show custom collectorsStart a collection

Show Data being collectedReview collection error logsStop a collection

DEMO – Collect Data

Slide10

SQL Nexus

Slide11

Tool used to import and report on SQLDiag

outputAllows you to develop custom collections and reportsAvailable on Codeplex

: http://sqlnexus.codeplex.com/

This means that the source code is available

RML Utilities must be installed prior to installing SQL Nexus

RML Utilities for SQL Server (x86) –

http://www.microsoft.com/en-us/download/details.aspx?id=8161

RML Utilities for SQL Server (x64) –

http://

www.microsoft.com/en-us/download/details.aspx?id=4511

SQL Nexus

Slide12

Built-in reports provide a nice GUI for blocking, wait statistics, resource utilization, etc.

SQL Nexus Reports

Slide13

Explore Import OptionsImport the data

Demo – Import Data into SQL Nexus

Slide14

SQL Nexus uses a custom import process that you can take advantage ofBy modifying a XML configuration file, you can have SQL Nexus import your custom data collection from

PSSDiagAdd the name of the

rowset in the TextRowsetsCustom.xml file Located where you installed SQL NexusTip: You must have entered something in your custom data collection to identify the

rowset

so SQL Nexus can import it

SQL Nexus Custom Diagnostics

Slide15

Show the XML configuration fileView the collections

Import dataShow tables for custom diagnostics

DEMO – Import Custom Data

Slide16

Performance Analysis of Logs (PAL) Tool

Slide17

Nice free tool used to analyzer Perfmon

logsAllows you to set custom thresholds or use thresholds already configured for your workloadThere is a SQL Server workload that looks at SQL Server counters

Available on Codeplex: http

://pal.codeplex.com

/

Does take some analysis time, so be prepared to wait if you need to analyze a lot of

perfmon

information

Performance Analysis of Logs (PAL) Tool

Slide18

Answer each option carefully as it will impact the output reportChoose the SQL Server 2005/2008 Threshold Option

Use the ThresholdFile tab to create a

perfmon counter template file to easily collect the data

The PAL Wizard

Slide19

The MS Chart Controls are required to execute PALPAL will install fine without them

http://www.microsoft.com/en-us/download/details.aspx?id=14422

MS Chart Controls for PAL

You’ll receive this error if the controls are not installed.

Slide20

Graphs show thresholds

PAL Output

Alerts summarized in time slices

Slide21

The output is color coded to let you know the areas to focus onYou do have some control over this through the threshold files

Not everything in red actually means somethingYou must know what to look for

PAL Output

Slide22

Analyzing the Results

Slide23

PAL is great for overall system performanceBenchmark

Get acquainted with a workloadLong durationPSSDIAG/Nexus

More targeted performance analysisNeed to view SQL internal resources (waits, blocking chains, query plans)

Short timespan for collection

When to Use Which Tool?

Slide24

Look at Bottleneck AnalysisReview Performance Counters

Identify Expensive QueriesDig in to the Nexus databaseLook at solving the biggest bottleneck first then collect data again

Now What?

Slide25

DEMO – Tips for

Data Analysis