Microsoft® SQL Server® 2012 Bible

919

CHAPTER

38

Using Profi ler and SQL Trace

IN THIS CHAPTER

Viewing SQL Server Events

Establishing Traces

Merging Profi ler Data with SysMon

I

f you’ve been working with SQL Server for any length of time, you’ve probably used SQL Server

Profiler. If you haven’t had the opportunity to use it, no big deal. With the exception of Profiler

for Analysis Services (SSAS) it’s being replaced by Extended Events. However, Profiler is still

available in SQL Server 2012 and is still a vital tool to have available for any earlier versions of SQL

Server that you may work with. This chapter covers the high-level features and functionality of

Profiler. Your best bet is to be ready and able to capture performance data with either Profiler or

Extended Events.

Features of SQL Server Profi ler

■ (^) SQL Server Profiler is the optional front-end user interface for SQL Trace. The two are
different components and technologies. SQL Trace runs on the server, is lightweight, and
collects data points to be passed to Profiler or written to a file. Collectively, they have the
following features and benefits: SQL Trace is a lightweight, but powerful, technology that
can run on SQL Server; it collects performance data selected from hundreds of possible
performance data points ranging from locks, to connections, to SQL DML statements, to
recompiles.
■ SQL Server Profiler is a separate application that can configure, start, and stop SQL Trace as
well as capture and display data from SQL Trace.
■ Profiler data can scroll on the screen or be saved to a file or table for further analysis.
■ (^) Data can be filtered in numerous ways, including viewing only events related to a specific
database, excluding events from a specific application, or capturing only the queries that
exceed a specified duration.
■ Profile configurations can be saved and reused later.
■ (^) Profile event data can be merged with server counter data collected by Perfmon/SysMon for
a great visual representation of what’s happening on the server.
c38.indd 919c38.indd 919 7/31/2012 10:04:16 AM7/31/2012 10:04:16 AM
http://www.it-ebooks.info