How to use SQL Profiler to create an SQL trace in Microsoft SQL Server

INTRODUCTION

This article describes how to use SQL Profiler to create an SQL trace in Microsoft SQL Server 2005 and higher versions.

(Note, you can use the same steps or selection of event classes for any version of SQL Profiler.)

RESOLUTION

To create an SQL trace, follow these steps: 

1. Click Start, point to Programs, click Microsoft SQL Server 20XX (your version), click Performance Tools, and then click SQL Server Profiler

2. On the File menu, click New Trace… to open the ‘Connect to Server’ dialog box. 

3. In the Server name box, type the name of the SQL Server.

4. In the Authentication list, click SQL Authentication. 

5. Type the system administrator user name and the password, and then click Connect to open the Trace Properties dialog box. 

6. Key a trace name. Click the Events Selection tab, and then click to select the Show all events check box in the lower-right corner. 

7. Select the server event classes to trace: 

a. Expand Cursors, and then click to select the following check boxes: 

 •CursorClose
 •CursorExecute 
 •CursorOpen 
 •CursorPrepare 

b. Expand Errors and Warnings, and then click to select the following check boxes: 

 •Attention 
 •Exception 
 •Missing Column Statistics 

c. Expand Locks, and then click to select the following check boxes 

 •Lock: Deadlock
 •Lock: Deadlock Chain 

d. Expand Performance, and then click to select the following check boxes: 

•ShowPlan All

e. Expand Sessions 

•ExistingConnection (may already be selected by default )

f. Expand Stored Procedures and click to select the following checkboxes:

•RPC: Starting 
•RPC:Completed (may already be selected by default )
•SP: Completed 
•SP: Recompile 
•SP: Starting 
•SP: StmtCompleted 
•SP: StmtStarting 

Note By default, the RPC: Completed check box should be selected. Do not click to clear the RPC: Completed check box.

g. Expand TSQL, and then click to select the following check boxes: 

•SQL:BatchCompleted (may already be selected by default )
•SQL:BatchStarting 
•SQL:StmtCompleted 
•SQL:StmtStarting 

Note By default, the SQL:BatchCompleted check box should be selected. Do not clear the SQL:BatchCompleted check box. 

8. Click Organize Columns, and then make sure that the following columns appear in the Organize Columns dialog box: 

•Start Time 
•End Time 
•SPID 
•Event Class 
•Text Data 
•Integer Data 
•Binary Data 
•Duration 
•CPU 
•Reads 
•Writes 
•Application Name 
•NT User Name 
•DB User Name 

9. Do not filter by user.  We can do that on our end, as we like to see what else is running a the same time. But indicate which user it was when you return the trace file to us, otherwise it is assumed that the 'sa' user was used. 

10.  Click Run to run the trace and to capture SQL Server processes. 

11. On the File menu, click Stop Trace to stop the trace. 

12. On the File menu, click Save As, and then click Trace File

3. Name the file, and then save the file.

MORE INFORMATION

Microsoft Knowledge Base articles are intended to provide reactive, troubleshooting help. Because this document contains informational or "how to"-related material, we moved the document to one of the following areas: 

• CustomerSource 

• PartnerSource 
מאפיינים:

מזהה פריט: 912281 - סקירה אחרונה: 14 בפבר׳ 2017 - תיקון: 1

Microsoft Dynamics GP 10.0, Microsoft Dynamics GP 2010, Microsoft Dynamics GP 2013, Microsoft Dynamics GP 2015, Microsoft Dynamics GP 2016, System Manager, System Manager

משוב