SQL Server: Collecting and Analyzing Trace Data
Learn how to collect trace data and use it to help with tasks like troubleshooting performance and workload benchmarking, applicable for developers and DBAs from SQL Server 2005 onwards
Authored by:
Jonathan Kehayias
Duration: 3h 52m
Level: Intermediate
Released: 7/30/2012
Features:
Duration: 3h 52m
Level: Intermediate
Released: 7/30/2012
Features:
Course Rating:
You are currently not signed in. Please sign in to access subscriber-only content.
Currently using: {{getCurrentPlayerName()}} [Change]
| expand all | collapse all | Progress | Duration | |
|---|---|---|---|
 Introduction |
|
00:05:17 | |
|
Introduction
|
|
02:00 | |
|
Collecting Trace Information
|
|
01:12 | |
|
Workload Analysis
|
|
00:52 | |
|
Course Structure
|
|
01:13 | |
|
SQL Trace Architecture |
|
00:09:54 | |
|
Introduction
|
|
01:10 | |
|
What is SQL Trace?
|
|
00:56 | |
|
What are Events?
|
|
01:17 | |
|
The Trace Controller
|
|
00:58 | |
|
Event Producers
|
|
00:38 | |
|
Traces
|
|
00:29 | |
|
I/O Providers
|
|
00:51 | |
|
When to use SQL Trace
|
|
01:03 | |
|
Security Considerations
|
|
01:34 | |
|
Summary
|
|
00:58 | |
|
Using SQL Server Profiler |
|
00:43:47 | |
|
Introduction
|
|
02:08 | |
|
What is Profiler?
|
|
01:35 | |
|
Creating New Traces
|
|
01:42 | |
|
Demo: Creating a New Trace from Scratch
|
|
03:39 | |
|
Demo: Creating a New Trace from a Template
|
|
03:23 | |
|
Demo: Creating a New Trace from a Trace File
|
|
02:22 | |
|
Filtering Trace Events
|
|
02:37 | |
|
Trace Templates - Introduction
|
|
01:32 | |
|
Trace Templates - SP_Counts and Standard
|
|
01:04 | |
|
Trace Templates - TSQL and TSQL_Duration
|
|
01:01 | |
|
Demo: TSQL_Duration Template
|
|
01:47 | |
|
Trace Templates - TSQL_Grouped and TSQL_Locks
|
|
01:05 | |
|
Trace Templates - TSQL_Replay and TSQL_SPs
|
|
02:05 | |
|
Trace Templates - Tuning
|
|
00:57 | |
|
Demo: Trace Templates
|
|
05:16 | |
|
Saving Captured Data
|
|
01:23 | |
|
Demo: Saving Captured Data
|
|
05:59 | |
|
Tips for Tracing with Profiler
|
|
02:06 | |
|
Summary
|
|
02:06 | |
|
Server-Side Trace |
|
00:28:48 | |
|
Introduction
|
|
02:04 | |
|
Performance Comparison
|
|
02:19 | |
|
The SQL Trace DMVs
|
|
01:01 | |
|
Demo: Trace DMVs
|
|
02:49 | |
|
Programmatically Creating Traces
|
|
01:53 | |
|
sp_trace_create
|
|
02:29 | |
|
sp_trace_setevent
|
|
01:07 | |
|
sp_trace_setfilter
|
|
01:49 | |
|
sp_trace_setstatus
|
|
01:20 | |
|
Demo: Programatically Creating Traces
|
|
05:42 | |
|
Generating Trace Scripts with Profiler
|
|
01:27 | |
|
Demo: Scripting Trace from Profiler
|
|
03:44 | |
|
Summary
|
|
01:04 | |
|
Tracing Scenarios |
|
00:54:53 | |
|
Introduction
|
|
01:13 | |
|
Default Trace in SQL Server
|
|
02:20 | |
|
Events Captured by the Default Trace
|
|
01:35 | |
|
Common Questions about the Default Trace
|
|
02:10 | |
|
Scenarios to use the Default Trace
|
|
01:44 | |
|
Demo: Default Trace
|
|
07:24 | |
|
Identifying Blocking Issues
|
|
01:55 | |
|
Demo: Identifying Blocking Issues
|
|
04:47 | |
|
Identifying Deadlock Issues
|
|
01:34 | |
|
Demo: Identifying Deadlock Issues
|
|
05:12 | |
|
Identifying Recompile Issues
|
|
01:20 | |
|
Demo: Identifying Recompile Issues
|
|
03:44 | |
|
Security and Compliance Auditing
|
|
01:18 | |
|
Demo: Security and Compliance Auditing
|
|
05:44 | |
|
Capturing a Tuning Workload
|
|
01:41 | |
|
Demo: Capturing a Tuning Workload
|
|
03:55 | |
|
Capturing a Replay Workload
|
|
01:10 | |
|
Demo: Capturing a Replay Workload
|
|
02:04 | |
|
Common Trace Mistakes
|
|
02:52 | |
|
Summary
|
|
01:11 | |
|
Analzying Trace Data |
|
00:48:08 | |
|
Introduction
|
|
01:52 | |
|
Analyzing Trace Data with Transact-SQL
|
|
02:03 | |
|
Demo: Analyzing Trace Data with Transact-SQL
|
|
02:42 | |
|
Analyzing Data in Profiler
|
|
02:10 | |
|
Demo: Analyzing Data in Profiler
|
|
05:05 | |
|
Demo: Merging PerfMon Data in Profiler
|
|
04:26 | |
|
Extracting Trace Events
|
|
02:00 | |
|
Demo: Extracting Trace Events
|
|
04:05 | |
|
Normalizing Trace Data
|
|
02:10 | |
|
Demo: sp_get_query_template
|
|
04:15 | |
|
Demo: ReadTrace from RML Utilities
|
|
05:11 | |
|
Demo: ClearTrace
|
|
03:16 | |
|
Database Tuning Advisor Workload Analysis
|
|
02:17 | |
|
Demo: DTA Workload Analysis
|
|
05:33 | |
|
Summary
|
|
01:03 | |
|
Replaying Trace Workloads |
|
00:41:48 | |
|
Introduction
|
|
01:40 | |
|
Profiler Replay
|
|
02:11 | |
|
Profiler Replay Requirements
|
|
02:11 | |
|
Demo: Profiler Replay
|
|
05:19 | |
|
RML Utilities OStress Replay
|
|
02:24 | |
|
RML OStress Replay Requirements
|
|
02:17 | |
|
Demo: RML Ostress
|
|
06:35 | |
|
SQL Server 2012 Distributed Replay
|
|
02:27 | |
|
Distributed Replay Requirements
|
|
01:49 | |
|
Demo: Distributed Replay
|
|
07:09 | |
|
Replay Tool Limitiations
|
|
02:23 | |
|
Replay Tool Benefits
|
|
02:41 | |
|
Which Tool to Use?
|
|
01:24 | |
|
Summary
|
|
01:18 |
