SHARE

Working with the SQL Profiler

Written By

Jun 24, 2005

In SQL Server 2000, the SQL Profiler can help you diagnose and fix SQL Server
performance issues. This tool lets you trace events and play back the results,
which can help you determine application and/or query problems, such as
long-running queries or ineffective indexes. Let’s take a look at how you can
use the SQL Profiler to monitor, analyze, and tune your SQL server.

When you open SQL Profiler (Figure A), you can run traces on a set of
criteria you specify.

Click for larger image

Figure A.

These criteria or events can be one or more of the following:

Cursors
Database
Errors and Warnings
Locks
Objects
Performance
Scans
Security Audit
Server
Stored Procedures
Transactions
TSQL

SQL Profiler includes a set of templates you can begin using right away. After
becoming fluent at working with SQL Profiler, you can create your own templates
or modify existing ones. Profiler offers the following eight templates shown in
Table A.

Table A
Template name	Template filename	Definition
Stored Procedure Counts	SQLServerProfilerSP_Counts	This template provides a collection of events about the Stored Procedure name that has started. The results of this trace are grouped by Event Class, Server Name, Database ID, and Object ID. In addition, the Server Process ID (SPID) is trapped.
Standard	SQLServerProfilerStandard.tdf	This template provides detailed information about Security Audits (Audit Login/Audit Logout), Sessions (Existing Connection), Stored Procedures, and TSQL Statements that have been completed.
TSQL	SQLServerProfilerTSQL	This template traps TSQL Statements in the order that they occur. The results include the Event, SQL Statement, SPID, and Start Time.
TSQL Duration	SQLServerProfilerTSQL_Duration	This template traps the TSQL Statements, Event Class, SPID, and the time it takes in milliseconds for the TSQL Statement to execute.
TSQL Grouped	SQLServerProfilerTSQL_Grouped	This template groups your TSQL Statements by Application Name, NT User Name, Login Name, and the Client Process ID of the application being called by SQL Server.
TSQL Replay	SQLServerProfilerTSQL_Replay	This template traps detailed information of the TSQL Statements that have been issued and enables you to replay the trace.
TSQL Stored Procedures	SQLServerProfilerTSQL_SPs	This template traps the Stored Procedures and TSQL commands associated with that Stored Procedure. This trace also traps the SPID and start time of the Stored Procedure.
Tuning	SQLServerProfilerTuning	This template traps TSQL Statements and SQL Batches that have completed.

SW

Steven Warren

Steven S. Warren is a popular author residing in Winter Haven, Florida with his wife Danna and 2 children: Catie-Charlotte and Dain. As a columnist on such well-known IT web sites as Techrepublic.com, CNET, and ZDNET, Steven has published numerous articles. Additionally, Steven holds the following certifications: MCDBA, MCSE, MCSA, CCA, CIW-SA, CIW-MA, Network+, and I-Net+. As a Senior Technical Consultant for The Ultimate Software Group, Steven has become an expert at administering Microsoft networks including Microsoft SQL Server. He is also a computer hardware and troubleshooting expert, and is constantly seeking out new technologies and certifications. Additionally, Microsoft recently awarded him the Most Valuable Professional (MVP) award for his outstanding achievements. Steven resides in Winter Haven, Fl.

Working with the SQL Profiler

Steven Warren

Recommended for you...

Company

Categories