Transcript SQL Server Profiler

Module 17
Tracing Access to SQL
Server 2008 R2
Module Overview
• Capturing Activity using SQL Server Profiler
• Improving Performance with the Database Engine Tuning
Advisor
• Working with Tracing Options
Lesson 1: Capturing Activity using SQL Server Profiler
• Overview of SQL Server Profiler
• Available Tracing Output Options
• Commonly used Trace Events
• Commonly used Trace Columns
• Filtering Traces
• Working with Trace Templates
• Demonstration 1A: Capturing Activity using SQL Server
Profiler
Overview of SQL Server Profiler
SQL Server Profiler is a tool used to trace activity against SQL
Server.
• Based on the SQL Trace programming interface
• Used in many scenarios such as debugging, performance
monitoring, deadlock monitoring, and many others
• Replay functionality for stress testing
Available Tracing Output Options
• Profiler traces can be saved in tables and files
• For file output, ensure that you configure:

Maximum file size (default of 5MB is almost always too small)

Enable file rollover (if appropriate)
Commonly used Trace Events
Events:
The occurrence of an action
within SQL Server

Categories:
Groups of related trace
events
Example Event
Description
SQL:BatchCompleted
A SQL Server batch completed
SQL:StmtCompleted
A T-SQL statement completed
RPC:Completed
A remote procedure call (stored
procedure) completed
Audit Login / Audit Logout
Login or Logout occurred
Deadlock Graph
An XML description of a deadlock
was captured
Commonly used Trace Columns
Trace columns represent data (often attributes) that can be
captured when an event occurs.
• Avoid capturing too many columns, to minimize trace size
• Not all events provide data in every column
• Columns can be used to group event data
Filtering Traces
• Avoid capturing too many
events, to minimize:

Trace size

Performance impact

Complexity during analysis
• Filter is only applied when
the filtered column is
supported by the selected
event
Working with Trace Templates
Trace templates are predefined (or user-defined) sets of
event classes and trace columns.
• SQL Server provides a number of standard templates
• Users can save trace templates from existing trace
configurations
Demonstration 1A: Capturing Activity using SQL
Server Profiler
In this demonstration, you will see:
•
How to create and run a trace using SQL Server Profiler
•
How to use a trace to capture SQL Server activity
•
How to save the trace to a file
Lesson 2: Improving Performance with the
Database Engine Tuning Advisor
• Overview of Performance Tuning
• Available Options for Performance Tuning
• Introduction to the Database Engine Tuning Advisor
• Database Engine Tuning Advisor Options
• Demonstration 2A: Using the Database Engine Tuning
Advisor
Overview of Performance Tuning
Define
Goals
Analyze
Results
Select
Tools
Monitor
Outcomes
Identify
Metrics
Implement
Changes
Available Options for Performance Tuning
• Database Engine Tuning Advisor
• SSMS

Activity Monitor

Standard Reports

Custom Reports
• Dynamic Management Objects
• SQL Server Data Collection
• SQL Server Profiler
• SQL Trace
• SQL Server Extended Events
• Reliability and Performance Monitor
Database Engine Tuning Advisor
• Used to suggest index and statistics changes for improving
performance
• Processes workloads captured by SQL Server Profiler as
traces
Workload
Database
Engine Tuning
Advisor
Database and Database Objects
Reports and
Recommendations
Database Engine Tuning Advisor Options
• Limit the time for analysis

Analysis can take a long time for large schemas and workloads
• Determine the types of recommendations that should be
returned
Demonstration 2A: Using the Database Engine
Tuning Advisor
In this demonstration you will see how to use the Database
Engine Tuning Advisor to generate indexing and
partitioning recommendations for a query
Lesson 3: Working with Tracing Options
• Overview of SQL Trace
• SQL Trace vs. SQL Server Profiler
• Demonstration 3A: SQL Trace
• Retrieving Trace Output
• Replaying Traces
• Default Trace
• Combining Traces with Reliability and Performance Monitor
Logs
• Demonstration 3B: Combining Traces with Reliability and
Performance Monitor Logs
Overview of SQL Trace
SQL Trace is a programming interface that is based on system
stored procedures and is used for capturing activity against
SQL Server.
• SQL Server Profiler can be used to script SQL traces
• Events can be sent to files or SMO objects
• SQL Server Profiler uses SMO to create and consume traces
SQL Trace vs. SQL Server Profiler
SQL Trace
SQL Server Profiler
• Defined through procedures
• Use via a Graphical tool
• Runs directly within the
• Utilizes SQL Trace
database engine
• Writes events to files or SMO
• Used for:
• Write to files or database
tables
• Used for:

Long term monitoring

Debugging on test systems

Performance-critical traces

Short term analysis

Large traces

Small traces
Demonstration 3A: SQL Trace
• In this demonstration, you will see:

How to script a trace using SQL Server Profiler

How to start a SQL Trace

How to viewing a SQL Trace using SQL Server Profiler
Retrieving Trace Output
• SQL Server Traces are written to files
• Traces can be analyzed by:

Opening the trace in SQL Server Profiler

Importing the trace into a SQL Server table
CREATE TABLE dbo.tracetable
(
TextData varchar(max) NULL,
BinaryData varbinary(max) NULL,
...
);
INSERT INTO dbo.tracetable
SELECT * FROM
fn_trace_gettable('L:\Traces\adworks.trc',default);
Replaying Traces
• Multithreaded playback engine to reproduce activity

Validate configuration changes

Test performance changes
• Specific Events and Columns must be present in trace file

Use TSQL_Replay trace template
Default Trace
• Traces mostly configuration changes for troubleshooting

Written to log directory of SQL Server

Uses up to five 20MB trace rollover files
• Is on by default

Can be configured using sp_configure:
EXEC sp_configure 'show advanced options' , 1;
RECONFIGURE;
EXEC sp_configure 'default trace enabled' , 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options' , 0;
RECONFIGURE;
Combining Traces with Performance Monitor Logs
• Correlate Performance Monitor with SQL Trace events in
SQL Server Profiler
Demonstration 3B: Combining Traces with
Performance Monitor Logs
• In this demonstration, you will see how to combine a SQL
Server trace with a performance monitor log.
Lab 17: Tracing Access to SQL Server
• Exercise 1: Capture a trace using SQL Server Profiler
• Exercise 2: Analyze a trace using Database Engine Tuning
Advisor
• Challenge Exercise 3: Configure SQL Trace (Only if time
permits)
Logon information
Virtual machine
623XB-MIA-SQL
User name
AdventureWorks\Administrator
Password
Pa$$w0rd
Estimated time: 45 minutes
Lab Scenario
The developers for the new marketing application are
concerned about the performance of their queries. When
the developers were testing the application they were
working with small amounts of data and performance was
acceptable. The developers are unsure that they have
created appropriate indexes to support the application.
You will use SQL Server Profiler to capture traces of
application execution. You will then analyze the traces using
the Database Engine Tuning Advisor.
If you have time, you will configure traces using the SQL
Trace system stored procedures.
Lab Review
• In what situations would you use SQL Trace rather than
SQL Server Profiler?
• How would you limit the contents of your trace to events
that are related to a specific database?
Module Review and Takeaways
• Review Questions
• Best Practices