Setup Instructions - SQL Server Profiler
Overview
SQL Server Profiler provides detailed debugging and performance information by allowing DBAs and IT Pros to capture low-level details about the execution of queries and client requests. Through the use of templates and filtering, this information can then be 'sliced and diced' in order to help spot problematic execution strategies, performance problems, and other concerns.
The instructions below are for generating a standard profiler trace that can be used by SQL Server Audits to initially gauge performance. Unless otherwise instructed by SQL Server Audits, please follow the instructions listed below exactly when preparing profiler trace information.
NOTE: Unless otherwise instructed, it's important to perform the trace from/on the server being profiled (otherwise you can introduce additional network overhead).
Instructions
1. After you have logged into Windows on the server where the SQL Server that will be profiled resides, open SQL Server Profiler from the Start Menu. (It will be in the Microsoft SQL Server 2005 (or 2008) folder under the Performance Tools sub-folder.)
2. In SQL Server Profiler, create a new trace by selecting the File > New Trace menu option as indicated below:
3. From the Login screen, connect to the Local SQL Server (unless otherwise directed by SQL Server Audits) - which you can do by specifying a period (dot) as the location:
4. Configure Trace Properties on the General tab to (A) use the Standard Template - which should be the default in most environments, (B) Store the results to File (somewhere on the C:\Drive is usually the best option), (C) and to set a Maximum Size of 25MB per File. Unless otherwise directed, you'll also need to (D) specify a trace stop time (which should be about 1 hour or so from when you start the trace):
5. With General settings in place, switch to the Events Selection tab and Uncheck all Events except for Stored Procedures - RPC:Completed, and TSQL - SQL:BatchCompleted events as shown below.
6. Then check the Show all Columns checkbox near the bottom right of the dialog - and the display of events and event details will toggle.
7. Now go through the list of columns (or details that will be logged with each entry) and make sure that the following checkboxes are checked for both
rows (and uncheck anything not listed below):
TextData
Application Name
LoginName
CPU
Reads
Writes
Duration
SPID
StartTime
EndTime
Binary Data (RPC Only)
DatabaseID
Hostname
8. If you have been instructed by SQL Server Audits to put a column Filter in place, click on the Column Filters button near the bottom right of the dialog, and follow any instructions provide by SQL Server Audits.(For example, in the sample image below, the entire trace is being 'filtered' to only collect data for the database with an ID of 7.)
9. Click Run to start the profiler trace and make sure that you don't log off of the server. (If you're using remote desktop, you can close the Window to the server, but don't log off.)
10. Once the profiler trace completes, zip/rar the generated files and transmit them to SQL Server Audits. (If you haven't been given explicit instructions for sending the files, use these Upload Instructions.)