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
What you'll learn
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.
Table of contents
- Introduction 2m
- What is Profiler? 2m
- Creating New Traces 2m
- Demo: Creating a New Trace from Scratch 4m
- Demo: Creating a New Trace from a Template 3m
- Demo: Creating a New Trace from a Trace File 2m
- Filtering Trace Events 3m
- Trace Templates - Introduction 2m
- Trace Templates - SP_Counts and Standard 1m
- Trace Templates - TSQL and TSQL_Duration 1m
- Demo: TSQL_Duration Template 2m
- Trace Templates - TSQL_Grouped and TSQL_Locks 1m
- Trace Templates - TSQL_Replay and TSQL_SPs 2m
- Trace Templates - Tuning 1m
- Demo: Trace Templates 5m
- Saving Captured Data 1m
- Demo: Saving Captured Data 6m
- Tips for Tracing with Profiler 2m
- Summary 2m
- Introduction 2m
- Performance Comparison 2m
- The SQL Trace DMVs 1m
- Demo: Trace DMVs 3m
- Programmatically Creating Traces 2m
- sp_trace_create 2m
- sp_trace_setevent 1m
- sp_trace_setfilter 2m
- sp_trace_setstatus 1m
- Demo: Programatically Creating Traces 6m
- Generating Trace Scripts with Profiler 1m
- Demo: Scripting Trace from Profiler 4m
- Summary 1m
- Introduction 1m
- Default Trace in SQL Server 2m
- Events Captured by the Default Trace 2m
- Common Questions about the Default Trace 2m
- Scenarios to use the Default Trace 2m
- Demo: Default Trace 7m
- Identifying Blocking Issues 2m
- Demo: Identifying Blocking Issues 5m
- Identifying Deadlock Issues 2m
- Demo: Identifying Deadlock Issues 5m
- Identifying Recompile Issues 1m
- Demo: Identifying Recompile Issues 4m
- Security and Compliance Auditing 1m
- Demo: Security and Compliance Auditing 6m
- Capturing a Tuning Workload 2m
- Demo: Capturing a Tuning Workload 4m
- Capturing a Replay Workload 1m
- Demo: Capturing a Replay Workload 2m
- Common Trace Mistakes 3m
- Summary 1m
- Introduction 2m
- Analyzing Trace Data with Transact-SQL 2m
- Demo: Analyzing Trace Data with Transact-SQL 3m
- Analyzing Data in Profiler 2m
- Demo: Analyzing Data in Profiler 5m
- Demo: Merging PerfMon Data in Profiler 4m
- Extracting Trace Events 2m
- Demo: Extracting Trace Events 4m
- Normalizing Trace Data 2m
- Demo: sp_get_query_template 4m
- Demo: ReadTrace from RML Utilities 5m
- Demo: ClearTrace 3m
- Database Tuning Advisor Workload Analysis 2m
- Demo: DTA Workload Analysis 6m
- Summary 1m
- Introduction 2m
- Profiler Replay 2m
- Profiler Replay Requirements 2m
- Demo: Profiler Replay 5m
- RML Utilities OStress Replay 2m
- RML OStress Replay Requirements 2m
- Demo: RML Ostress 7m
- SQL Server 2012 Distributed Replay 2m
- Distributed Replay Requirements 2m
- Demo: Distributed Replay 7m
- Replay Tool Limitiations 2m
- Replay Tool Benefits 3m
- Which Tool to Use? 1m
- Summary 1m
About the author
Jonathan Kehayias is a Principal Consultant with SQLskills. He is the youngest person ever to achieve the Microsoft Certified Master - SQL Server 2008 certification and has been a SQL Server MVP for many years. Jonathan is heavily involved in the SQL community; including being the top answerer on the MSDN Database Engine forum, writing deep technical content on his blog (plus his old blog, and answering questions as @SQLPoolBoy on Twitter. Jonathan is a performance tuning expert, both SQL Se... more