Embarcadero DB Optimizer 1.5 SQL Profiler User Guide

embarcadero technologies > embarcadero® db optimizer™ 1.5 sql profiler user guide 1 using profiling … USING PROFILING > RUNNING A PROFILING SESSION EMBARCADERO TECHNOLOGIES > EMBARCADERO® DB OPTIMIZER™ 1.5 SQL PROFILER USER GUIDE 8 •The Events Tab displays information about wait events profiled by the execution process. •The Sessions Tab displays information about sessions profiled by the execution process. •The I/O Tab is Oracle-specific and will not appear in the Top Activity Section unless the data source being profiled is an Oracle platform. This tab displays information about the I/O profiled by the execution process. When you select any item from the Top Activity tabs, details are displayed in the Profiling Details view. Depending on the nature of the Top Activity item that you selected, the tabs that compose the Profiling Details view will change to reflect that item’s specific session information. Note: When right-clicking on a SQL statement in the Top Activity Section in Profiling, if the SQL statement is run by a different user than the user who is running DBO, than the User Mismatch dialog appears, with an example of the following message: “This query was executed by [SOE] and you are currently connected as [system]. We recommend you reconnect as [SOE] to tune the SQL. Would you like to continue anyway?” This message indicates that the statement is being tuned by a user other than the user who originally ran the query, and tables may be missing based on the different schemas. Click OK to run the query, or click Cancel and run tuning under the original user. SQL Tab The Profile editor’s SQL tab shows a representation of all SQL statements that are executing or waiting to execute over the length of the profiling session or within the currently selected graph bars. Statements can be grouped by type by right-clicking the view and selecting Organize > By Type . The following statement types are organized: INSERT , SELECT , DELETE , and UPDATE TIP: Statements are grouped when they differ only by their clause values. This enables the roll-up of SQL statements that only differ by a variable value. For example: select * from emp where empno=1; and select * from emp where empno=2. A ‘+’ symbol appears beside rollup statements. You can click the symbol to expand and view the different statement predicates. Additionally, the SQL tab displays two other groupings: Group Description OTHER Includes all recognized statements other than INSERT, SELECT, UPDATE, and DELETE statements.

Tutorial Working with SQL Profiler

This tutorial complements the article “Tracing ArcSDE Queries with SQL Profiler,” by Shannon Shields and Joseph Buckles which appeared in the January-March 2002 issue of ArcUser … magazine. It provides an introduction to tracing queries with SQL Profiler and using SQL Query Analyzer and will teach you how to create a trace template to capture events passed from ArcMap and ArcSDE to Microsoft SQL Server. You will also learn how toÜ Choose events and data columnsÜ Set filters in SQL ProfilerÜ Evaluate query statements …

SQL Profiler and SQL Query Analyzer are performance monitoring tools that are included with SQL Server. These tools can be used by a database administrator to diagnose problems and optimize performance. This tutorial introduces only a small portion of the functionality available with SQL Profiler and SQL Query Analyzer. More information on both tools can be found in the HTML Help file, SQL Server Books Online, available from the Microsoft Web site (www.microsoft.com/sql/techinfo/ productdoc/2000/). To learn more about administering SQL Server with ArcSDE, enroll in ArcSDE Administration for SQL Server, a five-day instructor-led course offered by ESRI. Visit the ESRI Web site (www.esri.com) for information about this course and other resources. Data for This Tutorial The data for this tutorial, based on a dataset containing land-use parcels for Wilson County, North Carolina, consists of a personal geodatabase feature class containing parcel data. Download the archived data file, Parcels.zip, from the ArcUser Online Web site. Important: After downloading and unzipping Parcels.zip, open the parcels personal geodatabase. Copy or import the parcels feature class into ArcSDE. If you do not have permission to load data into ArcSDE, get your ArcSDE administrator to copy the data and grant you SELECT privileges for the feature class. Tutorial Overview Before beginning this tutorial, verify that the ArcSDE server is running and that your Working with SQL Profiler HTML Help file, SQL Server Books Online, available from the Microsoft Web site provides, information on SQL Profiler and SQL Querry Analyzer login to SQL Server has appropriate permissions to view the data. In this tutorial you will Ü Create a SQL Profiler trace template Ü Connect to ArcSDE from ArcMap Ü Perform an attribute query Ü View the results of the query in SQL Profiler Ü Copy the traced query to SQL Query Analyzer Ü View query statistics Ü Create a column index in ArcCatalog Ü View query statistics using the new index Step 1: Create a SQL Profiler trace template This step defines a new trace definition in SQL Profiler by specifying which events to trace and what type of information to record for each event. A trace template must be created first to store the trace definition. 1. From the Start menu, choose Programs > Microsoft SQL Server > Profiler to start SQL Profiler. 2. From the SQL Profiler menu, choose File > New > Trace Template. 3. Click Save As to save the trace template. 4. Save the Template as tutorial.tdb in the default directory for SQL Server. 5. In the Trace Properties dialog box, click the Events tab. 6. In the Available Event Classes list, expand the Stored Procedures event category by clicking on the plus sign (+) next to it. 7. Select SP:StmtCompleted. 8. Click the Add button to add it to the Selected Event Classes list. 9. Click the Data Columns tab. 10. From the Unselected Data column, add the TextData, Duration, and Reads columns to the Selected data list. 11. Click Save to create the template but do not close SQL Profiler. Step 2: Connect to ArcSDE from ArcMap In this step you will query the Parcels feature class using ArcMap to find Railway right-of-way features. During this step a trace running in SQL Profiler will capture the…….