News

ArcUser Online


Search ArcUser

mapbackground  

E-mail to a Friend

Tracing ArcSDE Queries with SQL Profiler
by Shannon Shields and Joseph Buckles
Esri Education Services

Editor's note: SQL Profiler, one of the performance monitoring tools that is included with Microsoft SQL Server, can be used by the database administrator (DBA) to diagnose problems and optimize performance. This article addresses how SQL Profiler can be used with ArcSDE running SQL Server. A basic understanding of both ArcSDE and SQL Server is assumed. A step-by-step tutorial and a self-extracting archive containing sample data can be download.

By design, the communication that takes place between ArcSDE Application Server or Direct Connect clients, such as ArcMap and ArcCatalog, and SQL Server is largely transparent to the user. Users will see very little difference between accessing a shapefile, coverage, or personal geodatabase and an ArcSDE geodatabase. However, when using ArcSDE data, very little of it is cached by the client application so there is a constant stream of communication between the client, where data is used, and SQL Server, where data is stored. SQL Server contains many tools that can assist a DBA in viewing and understanding the types of queries sent by ArcSDE clients.

This article will examine SQL Profiler, a SQL Server tool, and its usefulness in evaluating database performance and diagnosing errors. The DBA can create traces in SQL Profiler that capture SQL statements. These statements can be used to evaluate query performance, monitor server usage, and diagnose errors or bottlenecks encountered by users.

What is SQL Profiler?

SQL Profiler allows system administrators to monitor and record database and server activities. SQL Profiler provides a graphical user interface to a series of stored procedures that monitor server events. Various events are collected by SQL Profiler and are returned to the administrator through the interface. Alternatively, these events can be saved to a file or a SQL Server table. The administrator can specify which server events to monitor and can further constrain the events collected through the use of filters.

SQL Profiler can execute several different traces simultaneously against local or remote SQL Servers. Saved traces can be reexecuted or stepped through, event by event, to see where problems occur or to test whether or not problems have been corrected. SQL Server's Index Tuning Wizard can also use a saved trace file as a workload.

SQL Profiler is a valuable tool for the ArcSDE administrator. Traces that capture events specific to ArcSDE or ArcSDE clients can be defined. Results returned from these traces can help an administrator understand characteristics of system usage such as which feature classes are accessed most frequently and what are the most common types of queries executed by users. Understanding how a system is used is the first step in establishing and realizing performance-tuning goals.

Dialog for choosing trace properties
Using SQL Profiler, an ArcSDE administrator can capture events specific to ArcSDE or ArcSDE clients and better understand system usage.

Using the Profiler

In SQL Server 2000, traces are based on trace definitions that can be stored in templates. A trace definition consists of the events the DBA wishes to monitor, the data that will be collected for each event, and any optional filters.

An event is an action generated within the SQL Server engine. Individual events are grouped into categories. An entire event category can be added to a trace definition, or just individual events. Events include login connections, disconnections, and failures; Transact-SQL (T-SQL) statements; the beginning and completion of stored procedures; errors written to the SQL Server error log; and locks acquired and released on database objects.

Data can be collected about each event. A list of data columns makes up part of a trace definition. The data that can be returned by an event varies between events and can include the duration of the event, the CPU time allocated to that event, the number of logical reads or writes generated by the event, the application or user generating the event, and whether or not the event executed successfully.

Even the most inactive instance of SQL Server can generate a large amount of trace information. When an event is added to a trace definition, every occurrence of that event is returned by the trace. Filters can be applied to the event data to limit the results of the trace to those events generated by a specific user or application. Filters can also be set to look for specific statements or specific database objects. Filters can also look for events of a specified minimum or maximum duration.

Administrators can use the predefined templates that come with SQL Profiler or create custom templates. Templates can be created independently or as part of a new trace. Each trace uses a template or begins with an empty list of events and data columns. Trace and trace template properties can be modified except when the trace is running.

Two Stored Procedures events, RPC:Completed and SP:StmtCompleted, return the contents of ArcSDE stored procedures. RPC:Completed occurs when a Remote Procedure Call (RPC) event has completed. SP:StmtCompleted occurs when a statement within a stored procedure has completed. The TextData data column for these events displays the full text of the T-SQL statement. These events are useful for observing and analyzing the queries passed by ArcSDE clients.

SQL Profiler Scenarios

The following three scenarios, involving problems commonly encountered by ArcSDE administrators, show how SQL Profiler can be used.

Continued on page 2

Contact Us | Privacy | Legal | Site Map