SQL Server Profiler is a powerful tool that allows you to capture and analyze events, such as the execution of a stored procedure, occurring within SQL Server. This information can be used to identify and troubleshoot many SQL Server-related problems.
HOW PROFILER CAN HELP DBAS
• Monitor the activity of the:
o SQL Server Database Engine
o Analysis Services
o Integration Services
• Monitor and identify performance-related problems with front-end application, queries,
T-SQL, transactions, and much more
• Perform query analysis of execution plans
• Monitor and identify SQL Server errors and warnings
• Capture and replay traces of activity to replicate problems
• Audit user activity
• Analyze trace results by grouping or aggregating them
• Create your own custom traces, and save them, for use whenever you wish
• Correlate Profiler trace events with System Monitor performance counters to identify
what event(s) are hogging server resources
• Capture data for analysis in the Database Engine Tuning Wizard
• Save trace results to a file, a database table, or to XML for later analysis
• Trace Profiler events programmatically, instead of using only the Profiler GUI
HOW PROFILER CAN HELP DEVELOPERS
• View how SQL Server works when interacting with a client
• Debug T-SQL code and stored procedures
• Perform stress testing
• Perform quality assurance testing
PROFILER TERMINOLOGY
We will introduce four key terms now and expound on them later when appropriate.
These terms are:
• Events
• Data columns
• Filters
• Trace
EVENTS
The execution of a stored procedure is one
example of an event. SQL Server Profiler allows you to capture over 170 different SQL Server-related
events. An Event Category is a group of related events.
DATA COLUMNS
Think of an event as a row in a database and data columns as the
columns in a worksheet. Different events are associated with different data columns, and not every data
column is available for every event
Examples of the data columns for a stored procedure event include EventClass, TextData,
ApplicationName, Reads, Writes, and Duration. Profiler offers 64 different event columns
FILTERS
Filters allow you to tell Profiler not to collect the events (rows) that you don’t want to save or view.
For example, maybe you only want to capture events from a selected user, a specific application, or for a given database
TRACES
A trace includes the events and data columns you collect and is usually stored in a physical file for later
examination. A trace file can be saved in many ways.
It can be:
• Stored in memory of Profiler
• Exported to a proprietary Profiler file format
• Exported to a database table
• Exported to an XML file
