HomeMS SQL

Working with the SQL Profiler

Steven Warren
By Steven Warren

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.

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.

Steven Warren
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.
Previous article
IBM Integration Products Combine For Beta
Next article
Oracle MultiMaster Replication Monitoring

Related Articles

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles

DatabaseJournal.com publishes relevant, up-to-date and pragmatic articles on the use of database hardware and management tools and serves as a forum for professional knowledge about proprietary, open source and cloud-based databases--foundational technology for all IT systems. We publish insightful articles about new products, best practices and trends; readers help each other out on various database questions and problems. Database management systems (DBMS) and database security processes are also key areas of focus at DatabaseJournal.com.

Advertisers

Advertise with TechnologyAdvice on Database Journal and our other IT-focused platforms.

Advertise with Us

Menu

Our Brands

Property of TechnologyAdvice.
© 2024 TechnologyAdvice. All Rights Reserved

Advertiser Disclosure: Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.