High Performance SQL Server Mastering SQL Server Profiler

Mastering SQL

Server Profiler

Brad M McGehee

Mastering SQL Server

Profiler

By Brad McGehee

First published by Simple Talk Publishing 2009

Copyright Brad McGehee 2009

ISBN 978-1-906434-11-3

The right of Brad McGehee to be identified as the author of this work has been asserted by him in accordance with the Copyright, Designs and Patents Act 1988

All rights reserved. No part of this publication may be reproduced, stored or introduced into a retrieval system, or transmitted, in any form, or by any means (electronic, mechanical, photocopying, recording or otherwise) without the prior written consent of the publisher. Any person who does any unauthorised act in relation to this

publication may be liable to criminal prosecution and civil claims for damages.

This book is sold subject to the condition that it shall not, by way of trade or otherwise, be lent, re-sold, hired out, or otherwise circulated without the publisher’s prior consent in any form other than which it is published and without a similar condition including this condition being imposed on the subsequent publisher.

C

ONTENTS

Contents ...3

About the author...7

Introduction...9

Which Version of SQL Server Profiler is covered in this Book?...9

How the Book is Structured ...9

Getting Started with Profiler...13

Why Mastering Profiler can help make you an exceptional DBA...13

The inner workings of Profiler ...14

Profiler terminology...15

Getting Started with Profiler ...17

Summary...28

Working with Traces and Templates ...29

Understanding the Events Selection Options...29

Creating a Custom Trace from Scratch...32

Saving Traces to a SQL Server Table ...43

Capturing Analysis Server Traces...46

Creating and Using Trace Templates...48

Summary...60

Profiler GUI Tips and Tricks...61

Tips on Selecting Profiler Events and Data Columns ...61

Tips on Using Column Filters ...69

Tips on Organizing Columns for Grouping and Analysis ...72

How the "Server Processes Trace Data" Option Affects Traces ...77

How to Set Global Trace Options...78

How to Schedule a Trace's Stop Time ...79

How to Use the Auto Scroll Window ...80

How to Search for Data in a Trace File ...81

How to Identify Slow Running Queries ...85

Creating a Trace to Identify Slow Running Queries...85

Analyzing the Poorly Performing Queries Identified by Profiler...94

Summary...105

How to Identify and Troubleshoot SQL Server Problems ...107

How to Identify Deadlocks...107

How to Identify Blocking Issues...115

How to Identify Excessive Auto Stats Activity...121

How to Identify Excessive Statement Compilations...125

How to Identify Excessive Database File Growth/Shrinkage ...130

How to Identify Excessive Table/Index Scans...134

How to Identify Memory Problems ...139

Summary...144

Using Profiler to Audit Database Activity ...145

Capturing Audit Events...145

Selecting Data Columns ...148

Selecting Column Filters ...149

Organizing Columns...150

How to Conduct an Audit Trace...151

Summary...153

Using Profiler with the Database Engine Tuning Advisor...155

Features and Benefits of Using the DTA...155

How to Create a Trace for Use by the DTA ...156

Summary...170

Correlating Profiler with Performance Monitor ...171

How to Collect Profiler Data for Correlation Analysis ...171

How to Collect Performance Monitor Data for Correlation Analysis...172

How to Capture Profiler Traces and Performance Monitor Logs ...183

How to Correlate SQL Server 2005 Profiler Data with Performance Monitor Data.184

Summary...199

How to Capture Profiler Traces Programmatically...201

Pro and Cons of Capturing Traces Programmatically...201

Capturing Trace Data Programmatically: An Overview...202

Required System Stored Procedures...202

Putting the Pieces Together: Writing Your Own Trace Capture Scripts...205

Creating T-SQL Traces from the Profiler GUI ...212

Using a Trace Function to Query a Trace File...215

Summary...216

Profiler Best Practices ...217

General Profiler Best Practices...217

Creating Traces ...220

Running Traces ...221

Analyzing Traces...223

Performance Monitor ...223

Database Engine Tuning Advisor...224

Summary...225

Profiler Events and Data Columns Explained ...227

Event Categories ...227

Profiler Data Columns...295

Summary...303

A

BOUT THE AUTHOR

Brad McGehee is a MCSE+I, MCSD, and MCT (former) with a Bachelors degree in Economics and a Masters in Business Administration. Currently the Director of DBA Education for Red Gate Software, Brad is an accomplished Microsoft SQL Server MVP with over 13 years' SQL Server experience, and over 6 years' training experience.

Brad is a frequent speaker at SQL PASS, SQL Connections, SQLTeach, Code Camps, SQL Server user groups, and other industry seminars, where he shares his 13 years' cumulative knowledge.

Brad was the founder of the popular community site SQL-Server-Performance.Com, and operated it from 2000 through 2006, where he wrote over one million words on SQL Server topics.

A well-known name in SQL Server literature, Brad is the author or co-author of more than 12 technical books and over 100 published articles. His recent books include "How to Become an Exceptional DBA" and "Brad's Sure Guide to SQL Server 2008".

When he is not traveling to spread his knowledge of SQL Server, Brad enjoys spending time with his wife and young daughter in Hawaii.

I

NTRODUCTION

I have been a SQL Server DBA for about 14 years, and I regard Profiler as one of the most useful of SQL Server's "built-in" tools. Profiler records data about various SQL Server events. This data is stored in a trace file and allows you to view the communications sent from a client to SQL Server, in addition to giving you an insight into its internal workings. This information can be used to troubleshoot a wide range of SQL Server issues, such as poorly-performing queries, locking and blocking, excessive table/index scanning, and a lot more.

For such a potentially powerful tool, Profiler is surprisingly underused. This must be due, at least in part, to the fact that it is occasionally a frustrating tool. The user interface is poor, it lacks many important features, it is poorly documented and, unless you have a lot of experience as a DBA, it is often hard to analyze the data you capture. As such, many DBAs tend to ignore it and this is distressing, because Profiler has so much potential to make a DBA's life more productive.

This is why I wrote this book, Mastering SQL Server Profiler. My goal was to make it easier for you to learn how to use Profiler, analyze the data it provides, and to take full advantage of its potential for troubleshooting SQL Server problems.

WHICH VERSION OF SQL SERVER PROFILER IS COVERED IN THIS BOOK?

Most DBAs are still using SQL Server 2005, so all the examples in this book are based on SQL Server 2005 Profiler. However, the differences between SQL Server 2005 and SQL Server 2008 are minimal. While you may notice some minor cosmetic changes between the two editions, and discover that SQL Server 2008 includes a few new events, every example in this book will work with SQL Server 2008 and everything you learn here is applicable to both SQL Server 2005 and SQL Server 2008.

In fact, most of what is covered in this book also applies to SQL Server 2000, although you will want to use the SQL Server 2005 Profiler tool to access your SQL Server 2000 instances.

HOW THE BOOK IS STRUCTURED

If you are new to Profiler, you will want to start with the first three chapters, which provide the basic foundation you need to begin using Profiler. If you're already familiar with the basics of capturing traces with Profiler, then you can head straight to Chapter 4 and onwards. These chapters are self-contained and can be read in any order that you wish.

C

HAPTER

1:

G

ETTING

S

TARTED WITH

P

ROFILER

If you are new to Profiler, this is where you will want to start. This chapter covers all the basics you need to get you started using Profiler, such as:

•

Understanding how Profiler works

•

An introduction to Profiler terminology, such as events, data columns, filters and traces

•

Learning the basics of how to create, control, and save a Profiler trace

C

HAPTER

2:

W

ORKING WITH

T

RACES AND

T

EMPLATES

This chapter delves deeper into the world of traces. It covers topics such as:

•

How to build custom traces from scratch

•

How to Save Profiler traces to a SQL Server Table

C

HAPTER

3:

P

ROFILER

GUI

T

IPS AND

T

RICKS

At this point, you should have a good understanding of what Profiler can do and how it works. This chapter covers several Profiler tips that will make you more productive, such as:

•

Tips on Selecting Profiler Events and Data Columns

•

Tips on Using Column Filters

•

Tips on Organizing Columns for Grouping and Analysis

•

How the "Server processes trace data" Option Affects Traces

•

How to Set Global Trace Options

•

How to Schedule a Trace's Stop Time

•

How to use the Auto Scroll Window

•

How to Search for Data Inside a Trace File

•

How to Set and Use Bookmarks

C

HAPTER

4:

H

OW TO

I

DENTIFY

S

LOW

R

UNNING

Q

UERIES

Now that you have mastered Profiler basics, you get the opportunity to start making practical use of Profiler. As DBAs, one of the problems we face on an almost daily basis is slow-running queries. These are the kind of queries that keep us up late at night, ruining our sleep and turning us into zombies. This chapter shows you how to:

•

Create a Profiler trace to capture all the information you need to identify and analyze

slow running queries

•

Analyze the poorly-performing queries identified by Profiler

C

HAPTER

5:

H

OW TO

I

DENTIFY AND

T

ROUBLESHOOT

SQL

S

ERVER

P

ROBLEMS

This chapter shows how to create and analyze Profiler traces that will help you uncover a multitude of different problems (possibly problems that you didn't even know you had!). In this chapter, you will learn:

•

How to Identify Deadlocks

•

How to Identify Blocking Issues

•

How to Identify Excessive Auto Stats Activity

•

How to Identify Excessive Statement Compilations

•

How to Identify Excessive Database File Growth/Shrinkage

•

How to Identify Excessive Table/Index Scans

•

How to Identify Memory Problems

C

HAPTER

6:

U

SING

P

ROFILER TO

A

UDIT

D

ATABASE

A

CTIVITY

Many DBAs are tasked with auditing the activity within their databases. Although Profiler is not the ideal tool for auditing, it can still get the job done. This chapter covers:

•

The pros and cons of using Profiler for auditing

•

Available Audit events

C

HAPTER

7:

U

SING

P

ROFILER WITH THE

D

ATABASE

E

NGINE

T

UNING

A

DVISOR

In combination, Profiler and the Database Engine Tuning Advisor can make it much easier for DBAs to identify indexing issues in your databases. In this chapter, you learn:

•

About the features and benefits of using the DTA

•

How to create a trace for use by the DTA

•

How to run a trace for use by the DTA

•

How to Perform a Missing Index Analysis Using the DTA

C

HAPTER

8:

C

ORRELATING

P

ROFILER WITH

P

ERFORMANCE

M

ONITOR

Data captured in Performance Monitor and Profiler can be combined and displayed within the Profiler GUI, making it possible to identify correlations between Transact-SQL execution and resource usage. In this chapter, you learn:

•

How to collect Profiler data for correlation analysis

•

How to collect Performance Monitor data for correlation analysis

•

How to correlate SQL Server 2005 Profiler data with Performance Monitor data

•

How to analyze correlated data

C

HAPTER

9:

H

OW TO

C

APTURE

P

ROFILER

T

RACES

P

ROGRAMMATICALLY

While most DBAs create and collect traces using the Profiler GUI, there are certain advantages to using T-SQL scripts and system stored procedures. In this chapter, you will learn:

•

The pros and cons of capturing traces programmatically

•

How to use T-SQL and system stored procedures to capture traces

•

How to use the Profiler GUI to create T-SQL trace scripts

•

How to use a trace function to perform SELECT queries directly against a physical trace

file

C

HAPTER

10:

P

ROFILER

B

EST

P

RACTICES

There are many different ways to use Profiler to capture trace data. This chapter summarizes some of my thoughts on the most efficient ways to:

•

Create Traces

•

Run Traces

•

Analyze Traces

•

Use Performance Monitor with Profiler

•

Use Database Engine Tuning Advisor with Profiler

C

HAPTER

11:

P

ROFILER

E

VENTS AND

D

ATA

C

OLUMNS

E

XPLAINED

We saved the longest chapter for last. It provides a reference source for each of the 21 Profiler event categories that Profiler has to offer, and to offer an explanation of how they can be used to troubleshoot many different SQL Server problems. It includes a description of those events in each category that will be of the most interest to the DBA along with a digest of the most important data columns.

Now that you know where you are going, jump in with both feet and begin your journey towards mastering Profiler. Learning is always a great adventure, and I hope you enjoy reading this book as much as I did researching and writing it. If you have any questions or feedback, please send them to

Chapter 1

G

ETTING

S

TARTED WITH

P

ROFILER

SQL Server 2005 Profiler is a powerful tool that allows you to capture and analyze events, such as the execution of a stored procedure, occurring within SQL Server. This information can be used to identify and troubleshoot many SQL Server-related problems.

Everybody has to start somewhere and, if you are new to Profiler, this is where you want to begin. In this chapter, we'll cover the basics of how Profiler works, its core terminology, and how to create and save basic Profiler traces.

If you have used Profiler a lot in the past, you may want to skip right to the later, more advanced chapters in the book where we start to put Profiler into action, analyzing slow performing queries, troubleshooting bottleneck, auditing database activity and so on.

After reading this chapter, you will know why Profiler is such an essential tool in the DBA's armoury, and will:

•

Understand how Profiler works

•

Be familiar with core Profiler terminology, such as events, data columns, filters and traces

•

Understand the permissions required to use Profiler and how to start up Profiler

•

Be able to create and control basic Profiler traces and then save a completed trace to a

file

WHY MASTERING PROFILER CAN HELP MAKE YOU AN EXCEPTIONAL DBA

One of the things that separate mediocre DBAs from exceptional ones is that exceptional DBAs know how to take full advantage of the tools available to them. By mastering Profiler, the exceptional DBA can track down and fix SQL Server performance and other problems quickly and efficiently, and even spot potential problems before they cause real difficulties. In this section, we take a look at the many different ways we can use Profiler to help us out in our job.

While the following lists are not totally comprehensive, they do describe the majority of the ways Profiler can be used to help us out as DBAs or developers.

H

OW

P

ROFILER CAN HELP

DBA

S

•

Monitor the activity of the:

o SQL Server Database Engine o Analysis Services

o Integration Services

•

Monitor and identify performance-related problems with front-end application, queries,

T-SQL, transactions, and much more

•

Perform query analysis of execution plans

•

Monitor and identify SQL Server errors and warnings

•

Audit user activity

•

Analyze trace results by grouping or aggregating them

•

Create your own custom traces, and save them, for use whenever you wish

•

Correlate Profiler trace events with System Monitor performance counters to identify

what event(s) are hogging server resources

•

Capture data for analysis in the Database Engine Tuning Wizard

•

Save trace results to a file, a database table, or to XML for later analysis

•

Trace Profiler events programmatically, instead of using only the Profiler GUI

H

OW

P

ROFILER CAN HELP DEVELOPERS

•

View how SQL Server works when interacting with a client

•

Debug T-SQL code and stored procedures

•

Perform stress testing

•

Perform quality assurance testing

As discussed in the book's introduction, the focus of this book is on DBAs, not developers. Because of this, we will be focusing on the type of features described above that are targeted more toward DBAs.

THE INNER WORKINGS OF PROFILER

What may be a surprise to many DBAs and developers is that SQL Server Profiler is only a GUI designed to work with another feature of SQL Server called SQL Trace. It is SQL Trace that is actually doing most of the work when it comes to capturing SQL Server events and storing them for later use. SQL Trace is a feature of SQL Server that can be accessed indirectly with the Profiler GUI, system stored procedures, or programmatically using Server Management Objects (SMO).

In essence, SQL Trace is a very simple tool. Its job is just to capture SQL Server-related communication between a client and SQL Server. It acts similarly to a specialized network sniffer that captures traffic on the network related to SQL Server and allows you to see exactly which events are being sent from the client to SQL Server.

Unlike a network sniffer, which allows you to see every byte transversing the network, SQL Trace only captures and processes SQL Server-specific events. Here's how it works.

First, a SQL Server-related event is created between a client or SQL Server process and SQL Server. Events include many different types of activities and will be described in more detail later. As these events are occurring in SQL Server, it is SQL Trace’s job to capture specific SQL Server events that are of interest and to filter out those of no interest.

Once the events are captured, they are queued up in memory. At this point, they can be sent directly to a database table, to a physical file, or to an SMO-based application.

NOTE

Server Management Objects (SMO) is an object model that can be used to directly access SQL Server, including SQL Trace. In fact, the SQL Server Profiler GUI is actually a SMO-based application that interacts with SQL Trace.

In addition to SQL Server Profiler, SQL Server includes many system-stored procedures that use the SMO object model to interact with SQL Trace. We will discuss these system-stored procedures later on. Developers can also directly access SQL Trace using SMO objects, although this topic is not discussed in this book.

In effect, this means that SQL Trace is a black box that we can't directly access. All we can do is to interact with it indirectly with various tools. In our case, these tools will be Profiler and system-stored procedures.

PROFILER TERMINOLOGY

At school, I hated learning new terminology. Unfortunately, there is some basic Profiler terminology that we need to thoroughly understand before we can proceed any further. We will introduce four key terms now and expound on them later when appropriate.

These terms are:

•

Events

•

Data columns

•

Filters

•

Trace

E

VENTS

I have already used this term several times, without properly explaining what an event is. Essentially, it is the occurrence of some defined action inside SQL Server. The execution of a stored procedure is one example of an event. SQL Server Profiler allows you to capture over 170 different SQL Server-related events. An Event Category is a group of related events. For example, the Stored Procedure event category groups together all events relating to the execution of a stored procedure. This event category will include events that, for example, allow you to capture information about when a procedure started executing, completed executing, and so on. The term Event Class refers to an event, and all of the data columns associated with it.

D

ATA COLUMNS

Every event that can be captured includes a group of related data that describes that event and is stored in what are called data columns. Think of an event as a row in a database and data columns as the columns in a worksheet. Different events are associated with different data columns, and not every data column is available for every event, as you can see in Figure 1-2.

Figure 1-2: The left column above shows some of the available events for the Event Class called Stored Procedures. The columns to the right are the data columns that include the data captured for each event.

Examples of the data columns for a stored procedure event include EventClass, TextData, ApplicationName, Reads, Writes, and Duration. Profiler offers 64 different event columns, although no event uses all of the available data columns. This data is very useful to the DBA or developer when analyzing specific events.

F

ILTERS

Many times you will want to capture information about a certain event, but only when it occurs under specific circumstances, rather than every single time the event occurs. For example, maybe you only want to capture events from a selected user, a specific application, or for a given database. Filters allow you to tell Profiler not to collect the events (rows) that you don't want to save or view.

Figure 1-3: The above is an example of how you create a filter with Profiler. In the above example, we are filtering out all events that are not generated by the database with a DatabaseID of 5.

T

RACES

A trace includes the events and data columns you collect and is usually stored in a physical file for later examination. A trace file can be saved in many ways.

•

Stored in memory of Profiler

•

Exported to a proprietary Profiler file format

•

Exported to a database table

•

Exported to an XML file

In essence, your goal is to use Profiler to capture and save trace files for analysis.

Figure 1-4: This is a small example of an actual trace displayed in Profiler. It shows events as rows and data columns as columns.

GETTING STARTED WITH PROFILER

Now that we have covered the basics, we're going to start using the tool. You will get the most out of this book if you read it sitting in front of your computer, having access to a test or production SQL Server 2005 instance, Management Studio, and SQL Server Profiler. While you may be wary of working on a production instance, you need not be. Most of what we will do with Profiler will have minimal performance impact and can't hurt your production instance. There are a couple of exceptions to this, and I will clearly point them out at the appropriate time.

While a test server is perfectly adequate, the advantage of using a production instance is that you will be able to capture real and interesting events. If you only have access to a test SQL Server instance, you will want to write a script to simulate some database activity, so you can better follow the examples in this book. The following script, for example, does nothing special, except put a small load on your server so that you are able to capture some Profiler Events. As an alternative, there are several SQL Server stress tools on the market (Google "SQL Server stress test") that you may want to download and try. Most of these have free trial periods that will be more than adequate for simulating a SQL Server load for Profiler to trace while you are following the examples in this book.

USE [AdventureWorks]

EXECUTE dbo.uspGetEmployeeManagers 1 GO

SELECT *

FROM Production.Product ;

GO

SELECT *

FROM Production.Product

WHERE [ReorderPoint] > 500

OR [ReorderPoint] < 1000 ;

GO

SELECT *

FROM Production.ProductInventory ;

GO

SELECT *

FROM Sales.SalesOrderDetail ;

SELECT *

FROM Production.TransactionHistory ;

GO

P

ERMISSIONS REQUIRED TO USE

P

ROFILER

As you might imagine, you just don't want just anyone to create, view, or play back a Profiler trace. If that were possible, anyone could see the data moving between client applications and SQL Server. By default, only an SA, or a member of the SYSADMIN group, can create and run a Profiler trace, or replay an existing trace. In SQL Server 2005, it is now possible for SAs or SYSADMINs to grant Profiler permission to any SQL Server user. This, of course, is a potentially dangerous permission to assign, so it must be done with utmost discretion.

To assign SQL Server Login ID permission to use Profiler, an SA or member of the SYSADMINs group must grant the ALTER TRACE permission, like this:

USE master;

GRANT ALTER TRACE TO LoginID;

GO

To remove this permission, use this statement:

USE master;

REVOKE ALTER TRACE FROM LoginID;

GO

H

OW TO START UP

P

ROFILER

There are a couple of ways you can start up the Profiler GUI. Use whichever option is more convenient for you and appropriate to the operating system you are using. The following example assumes that you are using Vista as your desktop OS to run Profiler.

If you are at the Desktop, go to:

Start Æ All Programs Æ Microsoft SQL Server 2005 Æ Performance Tools Æ SQL Server Profiler.

Or, if you have Management Studio or the Database Engine Tuning Advisor already open, then in each case you can go to:

Tools Æ SQL Server Profiler

Or, if you prefer the Command prompt, type in the following and press ENTER.:

profiler90

Personally, I think all of the above options are too much work. I prefer to create a Profiler shortcut and add it to my Quick Launch bar.

At this point, you can begin following along with me on your own computer. It is a great way to speed up your mastery of Profiler.

G

ETTING FAMILIAR WITH THE

P

ROFILER

GUI

In this section, we take a brief look at the Profiler GUI, so we have a frame of reference for when we drill down into the details of this tool. Once Profiler is launched, using any of the techniques described above, you get the less-than-intuitive and informational screen shown in figure 1-5:

Figure 1-5: The Profiler GUI.

I'm betting that Microsoft must have spent over a million dollars just to design this screen, as it is so useful. Or maybe they saved a million dollars. I'm not sure which.

Seriously though, am I the only one who finds this user interface a little hard to figure out? For example, what's your first step? Even the icons at the top of the screen confuse me. But again, I have problems reading most street signs, so perhaps I am not a good person to offer an opinion.

To get Profiler up and running, click on the File menu (see Figure 1-6 below). From here, it gets easier, I promise.

Figure 1-6: The File menu is where you begin using Profiler.

We will eventually examine all the options available in this menu, and the rest of the GUI, but for now, selecting "New Trace" is the best way to get started. After selecting "New Trace", you get a screen similar to the one shown in Figure 1-7:

Figure 1-7: You must always log in to Profiler before you can use it.

As we already discussed, not just anyone can use Profiler to capture SQL Server trace data. So before we can begin using Profiler for any task, we have to log in for SQL Server to verify that we have the proper permissions to use Profiler.

In order to log in, we must tell Profiler what kind of server we want to Profile (Database Engine or Analysis Services), the name of the server we want to access with Profiler, and the kind of authentication we want to use in order to have our credentials verified. This is all straightforward, and is exactly how we log in to Management Studio.

Assuming we have the proper credentials and have selected a running instance of SQL Server, we will see the following screen after clicking on "Connect."

Figure 1-8: The Trace Properties screen is where you start to create a trace.

As you can see, there are lots of options on the first tab of this screen ("General"), and a second tab ("Events Selection") that we haven't even looked at yet. To keep things easy, we are going to start out by explaining the top two parts of this screen, then save the rest for later sections. No point overwhelming ourselves too soon.

So, let's first take a closer look at the top portion of the Trace Properties screen, as seen in Figure 1-9:

Figure 1-9: You should assign traces a name so you can easily refer to them.

One of the first things you need to do when you create a new Profiler trace is to give it a name. If you don't, then Profiler will automatically assign an easy-to-remember name (not), such as Untitled-1, for you. Assign a descriptive name that will make it easy for you to remember what this particular trace is for.

Notice that the next three boxes are grayed out. This is great; these are all filled in for you. If you are not in a big hurry, you might want to verify that the information is correct:

•

Trace Provider Name should be the name of the SQL Server instance you are going to

Profile

•

Trace Provider Type should be the version name of the SQL Server instance Profile

you are going to capture

•

Version is the numerical version of the SQL Server instance you are profiling

Here's some "inside info". SQL Server Profiler can capture Profiler traces of both SQL Server 2000 and 2005. Events and data columns will vary somewhat with each version, but that is to be expected as the feature set between product versions is somewhat different.

If any of these is not what you expect, you probably logged in to the wrong instance and need to exit Profiler and start over again.

Now let's take a brief look at the second, "Use the template", section of this screen, as seen in Figure 1-10:

Figure 1-10: Templates are a powerful feature of Profiler.

So what exactly is a template? Remember when we defined events, data columns, filters, and traces earlier? Essentially, a template is a predefined trace, which includes predefined events, data columns, and filters. Profiler comes with a variety of templates you can use, or you can create your own. Once you create your own template, you can use it over and over again, saving a lot of time.

We are going to return to the theme of templates later chapters, but for now all you need to understand is that in Profiler, the Standard template is the default, which means that the events and data columns that define this template have already been selected and are ready for use. You can see which events are covered by this template by clicking on the "Events Selection" tab, although we will save the details of that to the next chapter.

This means that, without any further work, simply click the "Run" button and you can start collecting trace data, which is exactly what we will do next!

C

APTURING BASIC TRACES

Finally, we get to do some fun stuff! Up to this point, all we have covered is background information. Now we get to perform our first trace. The goal of this section is to show you how to start, pause, and stop traces, and learn when you should use each option. We will be using the Standard (default) predefined trace template, and accept all the Profiler default options.

NOTE

If you want to follow the examples, but don't have a production SQL Server to connect to, consider running the T-SQL code provided earlier. You will need to run it in Management Studio each time you want to simulate some SQL Server activity.

Please note before you start running any trace in Profiler, that the collected events are stored in the RAM of the computer where Profiler is running. So, if you are at the server, valuable RAM is being used by Profiler to store this data. That is why it is a good idea to run Profiler on a computer other than the SQL Server you are monitoring. This way, SQL Server doesn't have to compete for memory with Profiler. At some point, it is possible for your computer to run out of RAM to hold all the Profiler events. When this happens, your computer begins to use the Operating System swap file for additional memory, greatly slowing down your computer. Later in the book we will talk about how to avoid this problem. For now, just keep your traces short to prevent any unexpected problems.

H

OW TO START A TRACE Let's start at the beginning. Step 1: Start Profiler.

Step 2: Select FileÆNew Trace

Step 3: The "Connect to Server" screen appears. Log in to the SQL Server instance you want to profile. This can be any running SQL Server instance.

Step 4: You should now be at the "Trace Properties" screen. For now, we are going to leave all the options at their default setting. This is because our goal now is to learn how to start a trace, and nothing more.

Step 5: To start your first trace, click on "Run" at the bottom of the screen. Congratulations! You have just learned how to start a Profiler trace. The trace screen now appears (see figure 1-11). As new trace events occur, you will see them appear on the screen. As you can imagine, the events you see in the figure below will be different from the events you will be capturing.

While we are at this screen, there are a few things you need to learn. First, be aware that each row on the screen is an event that has been captured by Profiler. Second, each column on the screen represents a different data column. Notice again that not all events include all data columns. This trace has many data columns, and they are not all shown on this screen. You can scroll to the right to see them all.

The screen itself is divided into two areas. The top portion of the screen lists the events. The bottom portion of the screen (the gray area) shows you the complete contents of the TextData data column. If you can't see all the TextData in the lower window, you can choose to resize the height of the two windows, or you can scroll either of the two windows up and down to view the TextData.

At the very bottom left of this screen, notice the message "Trace is running" which tells you that the current state of the trace. Other messages that can appear here are "Trace is paused" and "Trace is stopped."

At the bottom right of the screen, you see several different types of information. First is the Line and Column number of the row that is currently selected by the cursor. In this case, it is Line 104 and Column 1. "Rows" refers to the total number of rows (or events) that have been captured so far. When a trace is running, this number generally increments very quickly.

As you view a live trace, new events are continually added and displayed on the screen. In our example, new events are being added to the bottom of the top window.

H

OW TO PAUSE A TRACE

Pausing a trace means exactly what is says. When you pause a trace, you are telling Profiler to stop collecting events and wait until you either restart the trace or stop it. When you restart a paused trace, any events that occurred between the time you paused and unpaused it are gone and can't be recovered. But when you restart (unpause) the trace, Profiler will again begin collecting events, adding them to the screen from the moment you unpaused.

Pausing and unpausing a trace can be handy when you are trying to "capture" an event with the Profiler without collecting more events than you need. Think of pausing as a way as to control when you collect data, and when you don't, for short Profiler trace captures.

There are two ways to pause Profiler from the GUI. First, you can select the File menu at the top of the screen and select "Pause Trace." Or, you can click on the "Pause Trace" icon, which looks like this:

Figure 1-12: You can pause a trace by clicking on the Pause icon.

If you haven't done so already, give it a try and pause the trace. When the trace is paused, you will see "Trace is paused" at the bottom left of the screen.

To unpause a trace, you can either select the File Menu at the top of the screen and select "Run Trace", or you can click on the "Start Selected Trace" icon, which looks like this:

H

OW TO STOP A TRACE

When you decide to stop a trace, you are telling Profiler that you are completely done with this trace and do not want to restart it later. If you attempt to start a trace after it has been stopped, it will delete the event data from the old trace from memory and start fresh. So, only stop a trace if you know that you don't want to restart it again.

There are two ways to stop a Profiler trace from the GUI. First, you can select the File menu at the top of the screen and select "Stop Trace." Or, you can click on the "Stop Trace" icon, which looks like this:

Figure 1-14: You can stop a trace by clicking on the Stop Trace icon.

After stopping a trace, you will see the message "Trace is stopped" at the bottom left of the screen. Once a trace is stopped, you can view it or save it for later viewing. We will learn how to save a trace later in this chapter.

H

OW TO CLEAR A TRACE

Clearing a trace allows you to remove the current trace results from RAM. If you have saved these results, the saved results aren't affected by this option; only the RAM is cleared. I rarely need to use this option, as starting a new trace automatically clears the current trace.

There are two ways to clear a Profiler trace using the GUI. First, you can select the Edit menu at the top of the screen and select "Clear Trace Window." Or, you can click on the "Clear Trace Window" icon, which looks like this:

Figure 1-15: You can clear a trace from memory by clicking on the Clear Trace Window icon.

Once you have cleared a trace, you can start a new trace at any time.

H

OW TO SAVE A COMPLETED TRACE TO A FILE

In many cases, you will want to save your Profiler traces. You may not have time right away to analyze the trace, you may want more time to analyze the data, you may want to share the data with others, or to archive the trace results so you can compare them to traces taken later. In this section, we take a look at how to save a trace after it has been captured and stored in RAM. In the next chapter, I'll explain how to save a trace as it is being run.

Traces can be stored in a trace file (.trc format) or directly in a database table. In almost all cases, you will want to store traces in the trace file format, as it is the most efficient way to store them. Should you decide you want to store your traces in a database table, we will show you how to do that in a later chapter.

If you are following on your computer, start the default trace we have already been discussing. Be sure that some trace events have been captured in the trace window.

Step 1: Stop the trace. While it is not required to stop a trace before you save it, it makes sense that you would only really need to save a trace once it is complete and has been stopped.

Step 2: From the main menu, select File Æ Save, and you will get a standard Windows save dialog box. You can choose to save trace files anywhere you want. By default, trace files are stored under My Documents. You must give the trace file a name. So choose any folder and filename that works for you, then click "Save," and the file is saved. If you have accidentally selected a name already used, you will be asked if you want to overwrite the older file.

Figure 1-16: Trace files are saved like any other files in Windows.

The trace has now been saved. Notice that the trace is still in RAM. Saving a trace does not affect the trace currently in RAM. At this point, you can choose to clear the trace, start a new trace (which clears the current trace), or exit Profiler, which also clears the trace from RAM.

H

OW TO LOAD A SAVED TRACE

Once you have created and saved a trace, it is very common for a user to want to reload it into the Profiler GUI for additional analysis. For our example, let's assume that we want to load the trace we just created in the previous section. If you are following on your own computer and have not done so yet, exit Profiler, so we can all start from the beginning.

Follow these steps to load an existing trace file. Step 1: Start Profiler.

Figure 1-17: How to open an existing trace file.

Step 3: A new screen appears.

Figure 1-18: Select the existing trace you want to open.

Select the trace you want to open and click on the "Open" button, and the trace will appear, just like it did after you did the initial trace capture.

SUMMARY

Believe it or not, you have just mastered the basics of getting Profiler up and running. Without wishing to scare you, though, it has to be said that we have barely scratched the surface of what Profiler is capable of and we have a long way to go. But we have made a good start.

Chapter 2

W

ORKING WITH

T

RACES AND

T

EMPLATES

As you have probably guessed, there is a lot more to Profiler than using the default template and simply clicking "run". More usually, you'll want to create and save your own custom traces, tailored to the specific problem you are trying to solve.

The first goal of this chapter is to introduce you to the fundamentals of creating a new trace using a simple example. This will include selecting events and data columns, applying filters, ordering columns, as well as running the trace and saving it to a file or to a database table. Although this book is focused on profiling database engine events, I'll also briefly show you how you can apply the same knowledge of creating traces to profiling Analysis Server events.

We will then return to the topic of templates. Creating your own custom traces is a fundamental skill, but it would be a mistake to dismiss the built-in templates that Profiler provides, as they can save you a lot of time and effort.

We'll examine these built-in templates first, then investigate how to modify them to create your own custom templates, finally to build your own template from scratch. We'll even see how to import and export templates, as Exceptional DBAs often maintain a collection of Profiler Trace templates that they share with co-workers and other DBAs.

UNDERSTANDING THE EVENTS SELECTION OPTIONS

In Chapter 1, we spent all of our time on the "General" tab of the Profiler GUI, barely glancing at the "Events Selection" tab. Before we start creating custom traces, we need to quickly review this tab. Its name is not very accurate, since not only does it allow you to select events, but also to sort events, select data columns, and to filter events.

If you leave all the default options on the General tab, and switch over to Events Selection, you'll see the screen shown in Figure 2-1:

Figure 2-1: The Default Events Selection screen

NOTE:

Figure 2-1 is not quite the "default" Events Selection screen. I've expanded the data columns so you can read their names in full. You do this by simply dragging the right edge of the column across, or double-clicking on it. Unfortunately, changes in column width are not saved, so the next time you bring up Trace Properties window, you will have to repeat this step.

The first column on the screen is "Events". This is where you select one or more events to be captured in your trace. Here, we can see four event categories (in bold) and six events. All the remaining columns are the available data columns associated with each event. A checked box in a column means that the data column will be captured for that event. The existence of an empty box means that the data column in question is available for the given event, but is currently not set to collect data. For example, in figure 2-1, the TextData data column is available for the RPC:Completed event, but is not selected. If there is no box, then the data column is not available for that event – for example The CPU, Reads, Writes and Duration data columns are not available for the ExistingConnection event.

In Figure 2-1, Profiler is showing you a default selection of events and database columns: those that comprise the Default template. In fact, this is just a small subset of the events and data column that are available to you. In order to see them all, simply check the "Show all events" and the "Show all columns" check boxes in the lower right portion of the screen:

Figure 2-2: The Events Selection screen showing all events.

The screen is now showing you only "event categories," not specific events. This is why there are no longer any options to select or deselect the data columns. To see specific events, you have to click on the plus sign next to each event category, at which point the check boxes for the data columns will reappear. Directly below the rows and columns that represent the event and column data to be collected are two Help boxes. In Figure 2-3 below, you see that the "Broker" event category is named in the top Help box, and below that is a brief description of the events found in this event category.

Figure 2-3: This Help box is for events.

In Figure 2-4 below, the bottom Help box says "ClientProcessID (no filters applied), with a short description below. This Help box describes the data column the mouse is hovering over. So if you move the mouse to other data columns, you will see different Help information for each different data column. The words "no filters applied" tell you that no filter has been applied to this data column. We will learn more about filters shortly.

Figure 2-4: This Help box is for data columns.

Now that we understand how the two Help boxes work, let's take a quick look at the remaining part of the Trace Properties screen, as shown in Figure 2-5 below.

Figure 2-5: This portion of the Trace Properties screen allows you to perform many more tasks than you might imagine.

As we have seen, the top options shown in Figure 2-5 allow you to show, or not show, all the available events and data columns. I leave these options on when I am selecting which events and data columns to collect for a particular trace, so I can see what is available. I am getting old and can't remember as well as I used to, but once I have selected all the events and data columns I want to trace, I often deselect these two options. When you do this, only the events and data columns you have selected remain, making the screen a bit easier to read.

The "Column Filters..." button allows you to filter out any events (together with their data columns) that you don't want to capture. The "Organize Columns..." option is used to perform two tasks. First, you can use it to arrange the order of the data columns on the screen when a trace is running. Second, it allows you to group events by a single event type. Both these are large subjects and deserve their own sections; they are discussed later.

Now that you know your way around both tabs, we can move on to see how to create a new trace from scratch. While you don't have to follow the order I have outlined below, it is a practical approach and you might want to follow it closely until you have gained more experience with Profiler.

CREATING A CUSTOM TRACE FROM SCRATCH

In Chapter 1, we used all the default options and the default template to create a Profiler trace. In this section, you will learn the fundamentals of creating a new trace from scratch, using a simple example. In later chapters of this book, we will apply the fundamentals we learn here to more practical and complex solutions, but the fundamentals remain the same.

We won't look at every possible option, as there is no point confusing you with complex information before you have mastered the basics. Essentially, to create a new trace, you follow these steps:

1.

Start Profiler and log in to a SQL Server instance

2.

Create a new trace definition and assign it a name

3.

Specify that you want to save the trace file, and how to save it

4.

Select the specific events you want to trace

5.

Select the data columns for each event you want to trace

6.

Create any filters you might want to use

7.

Order the data columns, along with deciding what column to group by

8.

Start the trace

S

TEP

1:

S

TART

P

ROFILER

If you are following on your own computer, start Profiler using one of the options described in Chapter 1, so that the following introductory screen appears.

Figure 2-6: This is where we always start when creating a new trace.

S

TEP

2:

C

REATE A NEW TRACE DEFINITION

Next, select FileÆ New Trace, then log on to the SQL Server instance you want to practice with. Once you have logged in, the Trace Properties screen appears.

Figure 2-7: The Trace Properties screen is where we create new traces.

Now we can begin creating our own new trace. Next to "Trace Name" give this trace some name you will remember, such as Profiler_Practice, or whatever you prefer.

Figure 2-8: Enter a name for your first trace.

Next to "Use the template," use the drop-down menu to select "Blank." This tells Profiler that you don't want to use a pre-existing template and that you want to set all the trace options manually. The "Blank" option is at the very top of the drop-down list, so you may have to scroll up to see it.

Figure 2-9: Select the "Blank" template.

S

TEP

3:

S

AVING THE TRACE TO FILE

Up to this point, we have not done much different from before. Now it is time to do something new. In Chapter 1, as we were running the trace, the data was stored only in RAM, which is the default behavior. Only after the trace was complete did we save the results to file.

For this new trace, we want Profiler to store the results in a physical file on a local hard disk, as well as in RAM, as the trace data is being collected. As you gain more experience with Profiler, you will find yourself wanting to save trace files, and specifying this now can save a little time.

NOTE:

A little later in the chapter, I'll show you how to save a trace directly to a database table (although this is not recommended, because of performance issues).

Figure 2-10: Automatically saving a trace to a file.

To tell Profiler that you want the trace to be automatically saved as a trace file, select the option "Save to File." When you click on this option, you immediately get a "Save As" dialog box:

Figure 2-11: You have to tell Profiler where to store the trace file.

By default, traces are stored under "My Documents". If you would prefer to store traces somewhere else, that's fine. Notice that the "File Name" for the trace is the same name you assigned the trace. You can use this name, or choose a different name, whichever option works best for you. In addition, trace files have a .trc extension. While the file format used to store trace data is proprietary, it is an efficient way to store traces. We can, as we will discover later, convert the trace data into different formats, but for now, we will stick with the .trc format, which works well.

Once you assign the trace file a name, you are returned to the Trace Properties screen, which now looks slightly different,

Figure 2-12: Once you choose to save a trace file, you have several options to choose from.

The first option you have available is to "Set maximum file size (MB)." The default value is 5 MB. Depending on how busy your server is, and how many events and data columns you are collecting, it doesn't take long for a trace file to get huge. This option allows you to control the size of the saved trace file. You can choose any file size you want, up to the available space on your disk.

The next option, "Enable file rollover" is directly related to the "Set maximum file size (MB)." By default, this option is selected. What this means is that when your trace file reaches 5 MB (or whatever size you specify), a new file will be created to store the trace data. The new file name will be the original .trc filename with a number appended to it, such as Profiler_Practice_1. This rollover effect will continue until you stop the trace or you run out of disk space. If you don't select this option, the trace will automatically stop when the maximum file size you specified is reached.

As a DBA, you must decide what the best compromise is between file size and file rollover. For now, I suggest you accept the defaults. When you have gained some experience, you will be in a better position to be able to determine what values work best for you.

However, be warned: when you begin practicing using Profiler, carefully watch the size and quantity of your rollover files. Depending on your server, you can create a lot of data very quickly, much faster than you might expect. You don't want to receive a nasty "Out of Disk Space" error. And one more tip: be sure to delete traces once you no longer need them. Again, they can take up a lot of disk space, potentially causing you future problems.

The next option is called "Server processes trace data," which we will save for the next chapter, as it is an advanced option.

S

TEP

4:

S

ELECTING THE EVENTS TO TRACE

When I create a trace, the first thing I like to do is to select the events I want to capture. Since this is a practice trace, we don't want to select a lot of events. Our goal is learn the overall picture now, and we will focus on specific details later in this book. For our practice example, let's assume that we want to trace all Stored Procedure events. In order to select these events, we have to click on the plus sign next to the "Stored Procedures" event category, and the events specific to stored procedures are displayed on the screen. See Figure 2-12 below.

Figure 2-13: Selecting specific events to trace.

As you can see, the "Stored Procedures" event class has many specific events associated with it. In addition, you can see from the figure above that there are different data columns associated with each event.

To capture all the Stored Procedure events in the Stored Procedures category, click on each of the events as shown below. Not all are displayed on the screen, so you will need to scroll down a little to view and select them all.

Figure 2-14: We have elected to trace all stored procedure events.

Essentially, that is all there is to selecting events to trace. You can select as many events as you want, although I should warn you that the more you select, the more data you collect and the more SQL Server resources you consume.

S

TEP

5:

S

ELECTING THE DATA COLUMNS

Now that we have selected the events we want to trace, our next step is to select the data columns associated with the events we want to capture, for which we want to collect and store information. As you select your desired events, every data column available for that event is automatically selected.

Figure 2-15: By default, when you select an event, all the data columns associated with that event are automatically selected.

While you can leave them all selected if you want, it is always a better choice to only select those you need, in order to minimize resource overhead. This means you need to deselect those columns you don't need.

However, to keep things simple for this example, let's keep all of the data columns selected. We will learn later in the book which data columns are the best selected, and which data columns are best not selected.

S

TEP

6:

A

PPLYING FILTERS

Now we enter new territory. We are going to learn how to apply a filter to a trace. Remember, a filter is a way to reduce the number of events we collect in a trace. For example, if we only want to collect trace events for a single connection, or for a single database, we can do simply that. This reduces the amount of data that is stored in our trace. Using filters is optional, but very useful when isolating what data is collected during a trace.

Figure 2-16: Select which trace events to capture using this screen.

On the left-hand side of this screen, you see all the available data columns for all the events you previously selected. On the right-hand side you see where you can add criteria to filter which events are selected and which are not.

You can apply a filter to any of the data columns available. A common filter column is DatabaseName, in order to collect events only from a given database, or to exclude other databases. For example, if Reporting Services is installed on the server you are profiling, you may find that you often use a "Not like ReportServer" filter on the DatabaseName column, in order to prevent your traces being cluttered with events fired on the ReportServer database.

In our practice example, let's choose a filter on the Duration data column, so that we only capture those events that have a duration longer than 100 milliseconds. This way, we filter out very short-running events and only see events than take more than 0.1 seconds to execute. To do this, select the Duration data column and enter 100 in the "Greater than or equal" box.

Figure 2-17: We have just added a filter based on the duration data column.

Once you have specified a filter, you can continue to add more. In our case we are done, so we click on "OK" and the filter has been saved for this trace. To verify this, you can click on the "Column Filters..." button again, and you'll see a filter icon next to the Duration column (not shown).

S

TEP

7:

O

RDERING THE DATA COLUMNS

Now that a filter has been added, our next step is to select the order in which we want the data columns to be displayed when the trace is run, and to specify whether we want to group events according to a given data column. This is an optional step, but one that you will find quite useful for many traces. To organize columns, click on the "Organize Columns" button, and the following screen appears.

Figure 2-18: The Organize Columns screen

By default, when the Organize Columns screen appears, there is no grouping by events, and data columns are sorted alphabetically, with the exception of EventClass, which always appears first. If you wish to change the order in which the columns are displayed, simply select an event and click the "Up" or "Down" buttons.

For our practice trace, let's say that we want to group events by Duration and leave the remaining data columns in their default order. To group by Duration, click on Duration on the screen, and then click on the "Up" button until it moves up under "Groups," as shown in Figure 2-18:

Figure 2-19: With the above setting, captured events will be grouped by duration.

Can you believe it, we are now done creating our practice trace, and it is ready to run. So let's run it.

S

TEP

8:

R

UNNING THE TRACE

To run your new trace, click on the "Run" button at the bottom of the screen, and the trace begins. You will see a screen similar to the one below, except that your events will look different, as I am using a different database and application than you are. If the server you are monitoring is not being accessed by stored procedures, or if all of them are faster than 0.1 seconds, or if your servers are not doing much, you won't see many results (because of the events and filter we selected). On the other hand, you may be seeing hundreds of events being collected very quickly. It all depends on your server's current workload.

Figure 2-20: This is what a running trace looks like when it is grouped by duration. If you don't group by duration, the screen will look different, as we will see later.

In Figure 2-20, you see that events are sorted by duration, with the shortest running events shown at the top, the longest running at the bottom. The first figure in the Duration column is the elapsed time in milliseconds and it is followed, in brackets, by the number of events that ran in that duration.

The first row shows 35 events with what appears to be no duration. This is not really true. Some events don't have a specific duration. They occur, but their duration is not measured. Because of this, they have a duration of NULL and they show up at the top of the screen without any apparent duration.

After that, you see other events, starting with a duration of 277 milliseconds all the way to 6,022 milliseconds. In each case, only one event has run in each of these discrete time periods.

To view the actual events, you must click on the plus sign next to the row you want to examine. Let's start with clicking on the 35 events with a NULL duration.

Figure 2-21: The above events don't have a discrete duration.

While I don't want to get into a lot of detail now, you can see that some of the events above represent a stored procedure being executed, and show whether its execution plan was found in SQL Server’s buffer cache.

Let's now take a look at one of the discrete events that were captured:

Figure 2-22: Here is an example of a stored procedure being completed. Note that the duration of this stored procedure execution was 277 milliseconds.

In the above figure, we see that a particular stored procedure ran for 277 milliseconds. Because of the small size of the screen, I can't show you all of the data columns. But if you are following on your computer, you can scroll over and see all the various data columns that have been collected.

S

TEP

9:

S

TOPPING THE TRACE

Once you've collected enough data, stop the trace by clicking on the "Stop Selected Trace" icon.

Congratulations, you have now created and run your first trace. At this point, you would usually review the collected trace data to identify whatever issue you were looking for. Once you were done, you would exit Profiler. Because we gave the trace a name and saved it as a trace file, this file has automatically been saved on disk for us at the location previously specified.

So, if you exit Profiler now, Profiler will exit, but the trace you made is still saved and can be viewed any time you like by selecting File | Open | Trace File.

If you are done analyzing your trace, you can either archive the trace (store it somewhere so that you can find it again) or delete it to save space.

SAVING TRACES TO A SQL SERVER T

ABLE

Until this point, we have only talked about saving Profiler traces directly to disk. There is a good reason for this, and that is performance. For the least impact of using Profiler, traces should be originally stored on disk using the standard Profiler file format.

Is this the only option? No. Profiler also allows you to store a Profiler trace directly into a SQL Server table (see Figure 2-5), bypassing a disk file entirely. While this option works fine, it puts a lot of overhead on SQL Server that can negatively affect SQL Server's performance. Because of this, saving a Profiler trace directly to SQL Server is not recommended.

Now you ask, what if I want to store my Profiler traces in SQL Server, not only for the ease of querying the data, but to store it in order to maintain a baseline of Profiler activity? That's not a problem. Instead of saving Profiler trace data directly to a SQL Server database, you should first save it to disk. Then you can import the Profiler trace data into a SQL Server database later. While this is an extra step, it is necessary if you don't want to hurt the performance of the production SQL Server instances you trace. Within the SQL Server Profiler GUI, it is very easy to save a Profiler trace to a SQL Server table. To begin, you have to have a trace loaded into Profiler. To save this file into a SQL Server table, go to the Main menu and select File | Save As | Trace Table.

Figure 2-23: Profiler traces can be saved in SQL Server tables using the Trace Table option.

Once you select this option, a new window appears (see figure 2-34 below) prompting you to log into the SQL Server instance where you have the database containing your Profiler traces.

Figure 2-24: Log into any SQL Server instance where you want to store Profiler traces.

Next, you must specify the name of a pre-existing database where you want to store the trace table (see figure 2-25 below). Ideally, you should have a database designed for this specific purpose. In addition, you have to assign the trace table a name. Give it a name that makes it obvious what it is so you can find it later.

Figure 2-25: Select the database where you want to store Profiler traces.

Once you click the OK button, the file is automatically moved from Profiler to the database. A new table is automatically created for you, and the data stored in it.

Figure 2-26: This is what the trace looks like inside a SQL Server table.

Once the data has been stored in a SQL Server database, you can access the data just as you would with any other SQL Server data. Many DBAs create their own scripts to analyze the data; others use Reporting Services to create custom reports.

CAPTURING ANALYSIS SERVER TRACES

The focus of this book is on administrative DBAs who work with the SQL Server engine, not Analysis Services. However, starting with SQL Server 2005, DBAs do now have the ability to capture Analysis Services traces, and the process is very similar to the one we've just seen, so what you have already learned can be reapplied here.

To start an Analysis Services Profiler trace, start Profiler and select File | New Trace, just as you do create any trace in Profiler. The only difference is that you select Analysis Services as the Server type:

After you select the appropriate server and login information, click on "Connect" and the Trace Properties screen appears:

Figure 2-28: Gee, this screen looks familiar.

As you can see, the General tab is identical to what we have seen before. The only difference is that there are only two built-in templates, plus the blank template available.

Figure 2-29: The "Events Selection" tab above works just like the "Events Selection" tabs we have seen throughout the entire book.

At first glance, this screen looks very similar to the "Event Selection" tabs we have already seen. In fact, it works identically, but what is different is that Analysis Services has its own set of unique events and data columns, distinct from the database engine, so in fact this is an entirely different world that we have not seen before.

We are confronted with 11 Event Categories, 42 events, and 32 data columns. There is enough material here for another book, and that's why we won't be discussing any of the details of the events and data columns available for Analysis Services.

However, all other aspects of using Profiler are identical so, if you want, you can use what you already know to start experimenting with Analysis Services traces and see how they can help make your data warehousing projects perform better.

CREATING AND USING TRACE TEMPLATES

Now that you know how to create your own trace, it's time to explore further the idea of trace templates. As you might expect, creating custom traces can be a lot of work, especially if you take the care to select only those events and data columns that you really need, apply the appropriate filters, organize the columns as you want them, and so on.

Once you've taken the time to define a trace exactly as you want it in order to investigate a certain SQL Server issue, you certainly would not want to have to go through all that work again next time the issue arose. Instead, you will want to save your custom trace as a trace template, so that you can run it again and again, as required.

Fortunately, Profiler provides a variety of built-in (predefined) templates, and also allows you to define you own. We'll start our investigations with the former.

P

REDEFINED

P

ROFILER TEMPLATES

From the General tab in Profiler, you can click on the down arrow of the "Use the Template" drop-down box to see all the available templates:

Figure 2-30: Predefined Profiler templates are listed.

Profiler comes with eight predefined templates for the Database Engine, plus the blank template. At first glance, you may or may not be able to guess what some of these templates are designed to capture. To be honest, until I played with each template, I couldn't figure out what each one was really designed to do, as the template names are not obvious.

To learn how to use predefined templates, we don't have to take an in-depth look at each one. What I want to do is to talk about the first two templates in some detail, so you fully understand the concepts behind templates. The last six templates will then only need a brief mention.

S

TANDARD

(

DEFAULT

)

This template captures six different events in four event categories: Security Audit, Sessions, Stored Procedures, and TSQL. It also includes many different data columns, many of which are not included