SQL Server: Collecting and Analyzing Trace Data

Pluralsight

Course Summary

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

+
Course Description

One of the easiest ways to collect information about SQL Server workloads is to capture trace information. This course will introduce you to using SQL Trace and SQL Profiler for tracing SQL Server activity, including common pitfalls and problems to avoid. You'll learn how to analyze the collected information using a variety of methods that will help with performance troubleshooting, workload benchmarking, activity auditing, and much more. The course is perfect for developers and DBAs who need to collect information about workloads running on SQL Server for analytic purposes. The information in the course applies to all versions from SQL Server 2005 onwards.

Course Description

One of the easiest ways to collect information about SQL Server workloads is to capture trace information. This course will introduce you to using SQL Trace and SQL Profiler for tracing SQL Server activity, including common pitfalls and problems to avoid. You'll learn how to analyze the collected information using a variety of methods that will help with performance troubleshooting, workload benchmarking, activity auditing, and much more. The course is perfect for developers and DBAs who need to collect information about workloads running on SQL Server for analytic purposes. The information in the course applies to all versions from SQL Server 2005 onwards.

+
Course Syllabus

Introduction
- 5m 17s

—Introduction 2m 0s
—Collecting Trace Information 1m 12s
—Workload Analysis 0m 52s
—Course Structure 1m 13s

SQL Trace Architecture
- 9m 54s

—Introduction 1m 10s
—What is SQL Trace? 0m 56s
—What are Events? 1m 17s
—The Trace Controller 0m 58s
—Event Producers 0m 38s
—Traces 0m 29s
—I/O Providers 0m 51s
—When to use SQL Trace 1m 3s
—Security Considerations 1m 34s
—Summary 0m 58s

Using SQL Server Profiler
- 43m 47s

—Introduction 2m 8s
—What is Profiler? 1m 35s
—Creating New Traces 1m 42s
—Demo: Creating a New Trace from Scratch 3m 39s
—Demo: Creating a New Trace from a Template 3m 23s
—Demo: Creating a New Trace from a Trace File 2m 22s
—Filtering Trace Events 2m 37s
—Trace Templates - Introduction 1m 32s
—Trace Templates - SP_Counts and Standard 1m 4s
—Trace Templates - TSQL and TSQL_Duration 1m 1s
—Demo: TSQL_Duration Template 1m 47s
—Trace Templates - TSQL_Grouped and TSQL_Locks 1m 5s
—Trace Templates - TSQL_Replay and TSQL_SPs 2m 5s
—Trace Templates - Tuning 0m 57s
—Demo: Trace Templates 5m 16s
—Saving Captured Data 1m 23s
—Demo: Saving Captured Data 5m 59s
—Tips for Tracing with Profiler 2m 6s
—Summary 2m 6s

Server-Side Trace
- 28m 48s

—Introduction 2m 4s
—Performance Comparison 2m 19s
—The SQL Trace DMVs 1m 1s
—Demo: Trace DMVs 2m 49s
—Programmatically Creating Traces 1m 53s
—sp_trace_create 2m 29s
—sp_trace_setevent 1m 7s
—sp_trace_setfilter 1m 49s
—sp_trace_setstatus 1m 20s
—Demo: Programatically Creating Traces 5m 42s
—Generating Trace Scripts with Profiler 1m 27s
—Demo: Scripting Trace from Profiler 3m 44s
—Summary 1m 4s

Tracing Scenarios
- 54m 53s

—Introduction 1m 13s
—Default Trace in SQL Server 2m 20s
—Events Captured by the Default Trace 1m 35s
—Common Questions about the Default Trace 2m 10s
—Scenarios to use the Default Trace 1m 44s
—Demo: Default Trace 7m 24s
—Identifying Blocking Issues 1m 55s
—Demo: Identifying Blocking Issues 4m 47s
—Identifying Deadlock Issues 1m 34s
—Demo: Identifying Deadlock Issues 5m 12s
—Identifying Recompile Issues 1m 20s
—Demo: Identifying Recompile Issues 3m 44s
—Security and Compliance Auditing 1m 18s
—Demo: Security and Compliance Auditing 5m 44s
—Capturing a Tuning Workload 1m 41s
—Demo: Capturing a Tuning Workload 3m 55s
—Capturing a Replay Workload 1m 10s
—Demo: Capturing a Replay Workload 2m 4s
—Common Trace Mistakes 2m 52s
—Summary 1m 11s

Analzying Trace Data
- 48m 8s

—Introduction 1m 52s
—Analyzing Trace Data with Transact-SQL 2m 3s
—Demo: Analyzing Trace Data with Transact-SQL 2m 42s
—Analyzing Data in Profiler 2m 10s
—Demo: Analyzing Data in Profiler 5m 5s
—Demo: Merging PerfMon Data in Profiler 4m 26s
—Extracting Trace Events 2m 0s
—Demo: Extracting Trace Events 4m 5s
—Normalizing Trace Data 2m 10s
—Demo: sp_get_query_template 4m 15s
—Demo: ReadTrace from RML Utilities 5m 11s
—Demo: ClearTrace 3m 16s
—Database Tuning Advisor Workload Analysis 2m 17s
—Demo: DTA Workload Analysis 5m 33s
—Summary 1m 3s

Replaying Trace Workloads
- 41m 48s

—Introduction 1m 40s
—Profiler Replay 2m 11s
—Profiler Replay Requirements 2m 11s
—Demo: Profiler Replay 5m 19s
—RML Utilities OStress Replay 2m 24s
—RML OStress Replay Requirements 2m 17s
—Demo: RML Ostress 6m 35s
—SQL Server 2012 Distributed Replay 2m 27s
—Distributed Replay Requirements 1m 49s
—Demo: Distributed Replay 7m 9s
—Replay Tool Limitiations 2m 23s
—Replay Tool Benefits 2m 41s
—Which Tool to Use? 1m 24s
—Summary 1m 18s