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
Course info
Rating
(130)
Level
Intermediate
Updated
Jul 30, 2012
Duration
3h 52m
Table of contents
Introduction
SQL Trace Architecture
Using SQL Server Profiler
Server-Side Trace
Tracing Scenarios
Analzying Trace Data
Replaying Trace Workloads
Description
Course info
Rating
(130)
Level
Intermediate
Updated
Jul 30, 2012
Duration
3h 52m
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.

About the author
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.

More from the author
SQL Server: Change Data Capture
Intermediate
2h 10m
Mar 27, 2015
More courses by Jonathan Kehayias
Section Introduction Transcripts
Section Introduction Transcripts

Introduction
Hi, this is Jonathan Kehayias from SQLskills. com and I'm recording this course for Pluralsight. This course is about collecting and analyzing trace data from SQL Server and this is module 1 which provides an introduction to the rest of the course. So, this course applies to all versions of SQL Server from 2005 onwards including SQL Server 2012. Even though Trace has deprecated inside of SQL Server 2012 as a part of the RTM release, it will still remain inside of SQL Server for a couple of additional release life cycles of SQL Server. So, this information, as far as it pertains to tracing SQL Server, is still applicable. Now, the reason for this course is that understanding how an application is actually using SQL Server is very critical to doing query performance tuning or even as a part of the development life cycle so that you know how the application is actually going to interact with SQL Server. This makes this course very useful for DBAs as well as database developers because developers need to be able to see what actually is going on, on the backside of the database when the application starts to talk to the database and Trace is a very good mechanism of being able to do that. Now, you might think, "Well, I can do query performance tuning from the plan cache. " But, sometimes, query execution plans aren't cached and that makes it impossible to do full workload analysis without going to tracing with SQL Trace. And there's a lot of reasons that an execution plan may not be cached. Some of those reasons happen to be auto-parameterization. If you use concatenated literals in an ad hoc statement and it's auto-parameterized, the execution plan may not be cached by SQL Server. If you make use of the RECOMPILE query hints, that can prevent you from getting an execution planning cache. And if the execution plan has a zero cost to compile, it won't be inserted into the plan cache as an optimization inside of SQL Server.

SQL Trace Architecture
Hi, this Jonathan Kehayias from SQLskills. com and I'm recording this course for Pluralsight. This is Module 2: SQL Trace Architecture. So, SQL Server has many different components that actually work together tot allow trace information to be captured. Inside of the database engine, we the SQL Trace controller, we have our event providers, we have individual events, we I/O providers that allow our trace data that has been captured from our events to be sent out to our target destinations, and then outside SQL Server, we have our external Profiler application that actually works on top of one of our I/O providers and can create traces against SQL Server and then consume that trace information from the rowset I/O provider that is provided by the SQL Server implementation of trace. In this module, we'll cover the SQL Trace architecture and then we'll cover how and when to use trace to capture information. And the last thing we'll do to wrap up the module is talk about some of the security considerations for collecting trace data.

Tracing Scenarios
Hi, this is Jonathan Kehayias from SQLskills. com and I'm recording this course for Pluralsight. This is Module 5 Trace Scenarios. In production environments, tracing should only be used where it's actually going to provide a benefit over other methods of collecting information specifically about your query or application workload. Other methods could include using the dynamic management objects and querying the plan cache to see information about how frequently certain queries that are already cached had been executing and what their impact to the server has been. In this module, we'll take a look at some of the example scenarios where tracing can provide a benefit to a production environment including the default trace in SQL Server and when you might go about using it, identifying blocking issues using SQL Trace, identifying deadlocking issues and how you might use Profiler to view the deadlock graph information to go about troubleshooting deadlocks, identifying recompile issues, security and compliance auditing, capturing tuning workloads, and capturing replay workloads for performing trace replay operations.

Analzying Trace Data
Hi, this Jonathan Kehayias from SQLskills. com and I'm recording this course for Pluralsight. This Module 6: Analyzing Trace Data. So in the previous module, we talked about how you can go about collecting trace data. In this module, we're going to look at how you can analyze the trace data because once you've collected it, it's only useful if you can actually go through and find the events of importance for whatever problem you're looking at or for whatever kind of work you're trying to do. So, inside of SQL Server, there's multiple ways that you can go about analyzing trace data, including querying the data with Transact-SQL, viewing the data inside of SQL Profiler and there are some additional really neat things that you can do inside a Profiler with trace data that you can't do anywhere else that we'll take a look at in this module. Using additional Microsoft and third-party tools to do analysis of the information for you automatically, there are some really nice tools that are available by Microsoft but are not shipped as a part of SQL Server that can really help you with analyzing what's going on in your system. And then doing some automated analysis with the tools that do ship with SQL Server. For example, the Database Tuning Advisor, which can do some workload analysis based on the trace data you captured and evaluate your existing physical design structures including indexing and how the database engines actually using those so that you can get recommendations of ways you can tune your workload better. So in this module, we're going to take a look at analyzing the trace data with Transact SQL, analyzing it using Profiler, normalizing trace data for better analysis, using some of the third-party tools, and then using trace data with the Database Tuning Advisor to actually do a workload tuning exercise to check the physical design structures of the database that you're working with.

Replaying Trace Workloads
Hi this is Jonathan Kehayias from sqlskills. com and I'm recording this course for Pluralsight. This is Module 7: Replaying Trace Data. So replay allows us to capture trace data for a specific workload or problem on one server and then we can reuse that trace data to actually simulate our activity or replay our workload against the different server to allow us to do testing and performance benchmarking of changes that we might have going on inside of our database. For example, we want to add some indexes. We want to tune some stored procedure code and we know the outputs are the same but we want to able to test over a bunch of different parameters and variables, what the execution time is going to be for those changes. Doing a successful replay requires that you collect specific events from SQL Server and that you have a backup of the database 'cause you need to provide a base for your replay operation to start from and you want to maintain that backup because every time you do a new replay you're going to have to restore that same backup. In SQL Server, replay can be actually accomplished a number of different ways against SQL Server. For example, Profiler provides a replay mechanism as a part of its application. The RML Utilities offer the OStress tool for doing replay against SQL Server and in SQL Server 2012 we have Distributed Replay. So in this module, we'll cover the tools that are available for performing replay against SQL Server and the requirements that are necessary for being able to use each of the tools to do replay operations against your target environment.