ArcUser Online

Tracing ArcSDE Queries with SQL Profiler
Continued...

SQL Profiler Scenarios

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

Scenario 1: The client application is returning unexpected results.

A SQL Server error message may or may not have been generated, but the user is unable to proceed and it is unclear from the client application what is causing the problem. In this scenario it is not known whether the problem is occurring on the client or the server side. Isolating the cause of this problem may be difficult, and the ArcSDE administrator may want to begin by eliminating those pieces that are functioning correctly. SQL Profiler will be able to identify if the problem is occurring within SQL Server.

Create a trace using the Execution Warnings and Errorlog events from the Errors and Warning event class. The Execution Warnings event will list any warning that occurs during the execution of a SQL statement or stored procedure. The Errorlog event lists error events logged in the SQL Server error log. SQL Server errors returned by the trace will appear in red. The TextData data column will display the full error or warning.

Scenario 2: Slow performance

If queries are taking an unusually long time to execute, these queries may contain inefficient SQL or the queried tables may need to be indexed differently. When performance is suffering, SQL Profiler can be used to trace CPU usage and the duration of executed queries.

To identify slow-performing queries, create a trace using the RPC:Completed and SP:StmtCompleted events from the Stored Procedures event class. Include TextData, CPU, and Duration in the list of data columns. Establish a benchmark for an acceptable query execution time. Create a filter on the Duration column so that only queries that exceed this value will be returned. The Duration column returns data in milliseconds so set the filter to a Duration length greater than or equal to the benchmark time in milliseconds.

This trace will return the queries generated by ArcSDE client applications that are slow to return results. Tools such as the SQL Query Analyzer's graphic execution plan and the Index Tuning Wizard can help the ArcSDE administrator further analyze these queries.

Scenario 3: What are the most commonly used tables?

Sometimes performance issues are caused by a change in server usage. The number of ArcSDE users may have increased, or users are accessing the server more frequently. This scenario illustrates one way to use SQL Profiler to analyze server usage.

In this scenario, the goal is to gauge how the system is being used. SQL Profiler should not just be reserved for unexpected or slow performance. A good database administrator will minimize these occurrences by evaluating the database performance over time and determining which tables are being accessed most frequently and by which users. This type of trace can be performed after the usage of an ArcSDE geodatabase has changed. For example, usage probably would be very different for feature classes, which were once used only for a read-only ArcIMS application but are now part of a larger, read-write, multiversioned geodatabase.

To create a trace that will identify which objects are used and how often, add the Audit Object Permission event from the Security Audit event class. Add ObjectName to the list of data columns. Save the trace to a table in SQL Server. Run the trace for a period of time that represents average usage. After collecting sufficient information in the trace table, use the SQL Query Analyzer to explore which objects are accessed most often.

The following query will generate a unique list of objects, such as tables, and the total number of times these objects were accessed during the trace period. This list is ordered from the most to the least accessed.

Conclusion

These scenarios illustrate just a few of the ways SQL Profiler can be used by ArcSDE administrators. Workload the accompanying tutorial which covers how to set up SQL Profiler to trace an attribute query generated by ArcMap and how the data generated by SQL Profiler can be used in the SQL Query Analyzer for further analysis.

For more information on this topic, Esri offers a five-day instructor-led course, ArcSDE Administration for SQL Server, that specifically addresses installation, configuration, data loading, and performance optimization issues associated with running ArcSDE on SQL Server. Extensive documentation on SQL Server is also available from the Microsoft Web site including the comprehensive and searchable SQL Server Books Online.