How To Use SQL Server Profiler
SQL Server Profiler is a powerful, yet underutilized tool for recording SQL Server events from the server. SQL Server Profiler comes bundled with all versions of SQL Server, including SQL Server 2008, SQL Server 2008r2 and SQL Server 2012. This article focuses on how to use the SQL Server Profiler version that is bundled with SQL Server 2012. SQL Server Profiler can help you efficiently collect critical server data needed to improve SQL Server performance.
SQL Server Profiler allows you to capture all actions that occur in your database and save them into a trace file. These trace files are helpful for SQL Server performance tuning and for troubleshooting specific database issues.
Trace files can record valuable data, including:
- All T-SQL scripts running on the server simultaneously
- Information about how queries are resolved within SQL Server
- Logins, errors and requests
- Stored procedure requests
- Ad hoc queries executed
While capturing all data can sometimes be useful, it is important to customize what information is collected to avoid the problem of the trace file becoming too large. Most server support multiple databases and a trace that captures all traffic may become unnecessarily bulky without adding corresponding value.
In this article, we will walk through how to use SQL Server Profiler to capture a trace for SQL Server 2012, step-by-step. These steps detail how to record and begin to analyze a trace file.
Analyze a SQL Server trace file in order to:
- Unlock the full value of a SQL Server trace
- Quickly diagnose and identify all performance bottlenecks
- Locate specific SQL statements responsible for each bottleneck
- Proactively and constantly monitor performance through scheduled traces
In this section, you will learn the fundamentals of creating a new trace from scratch and analyzing it. We recommend recording ~400K events during a single recording session. Multiple traces should be recorded if the system is known to have different usage patterns throughout the day. Recorded trace files (.TRC) will be analyzed using SQL Trace Analyzer.
Essentially, to create a new trace, do the following:
- Download SQL Trace Analyzer (you will install during a later step). Download and move to the next step. You will install it later.
- Locate and launch SQL Server Profiler (bundled with SQL Server). SQL Server Profiler comes bundled with SQL Server. You will find the program by following Start -> Program Files - > Microsoft SQL Server -> Performance Tools -> SQL Server Profiler.
- Begin to create a new trace. Create a new trace definition and assign the new trace definition a name. Choose File -> New Trace
- Connect to a target SQL Server instance.
- Select the specific events that you would like to trace. Click on the "Event Selection" tab, next to the "General" tab at the top.
Uncheck the following rows: Audit Login Audit Logout, ExistingConnection, SQL:BatchStarting. Be sure that the events RPC:Completed and SQL:BatchCompleted are also checked. IMPORTANT NOTE: All columns for these 2 events should be checked, including the first column, TextData.
- Start the trace. Click "Run".
- Collect data. Wait until ~300K Rows have accumulated. The number of rows accumulated will be located in the bottom right corner of the screen.
- End the trace. Click the red "Stop" button (It is the red square near the top of the window, located directly below the Tools Window Help row).
- Save the trace to a file. Go to File->Save As->Trace File. Save the file as a ".TRC" file.
- Install and launch SQL Trace Analyzer (previously downloaded).
- Open the saved trace file with SQL Trace Analyizer to analyze the trace.
Congratulations! You have used SQL Server Profiler to record a trace!
Need help? Have an urgent SQL Server performance problem? If there is anything you would like to know
about SQL server performance, or if you need help improving performance of your database applications,
we are here to help.
Contact our consulting services today to discuss your SQL Server performance problems.
Call us at (800) 283-4775 or (215) 701-3913 or email us at