INF: SQL Server ODBC Driver Performance Analysis Tools

ID: Q157802

The information in this article applies to:

Microsoft SQL Server version 6.5
Microsoft Open Database Connectivity, version 2.5

SUMMARY

The Microsoft SQL Server ODBC driver version 2.65 that shipped with SQL Server 6.5 introduced features to assist in analyzing the performance of ODBC applications using the driver. This article outlines the use of these features.

MORE INFORMATION

The Microsoft SQL Server ODBC driver version 2.65 introduced several driver- specific connection options for logging performance-related information. These options are documented in the "Programming ODBC for SQL Server" manual that ships with SQL Server 6.5, and are also documented in the SQL Server 6.5 Books Online. This article adds to the manuals by outlining how to incorporate these options in an ODBC application. This article assumes that the application has included the Odbcss.h file that ships with SQL Workstation 6.5.

LOGGING LONG-RUNNING QUERIES

ODBC applications can request that the driver write to a log file all queries that take longer than a specified time interval to complete. Administrators and programmers can then analyze the queries in the log file to determine why they are taking such a long time to complete.

An application specifies the file to use for the log by calling the following lines:


   SQLSetConnectOption(hdbc,
            SQL_COPT_SS_PERF_QUERY_LOG,
            (ULONG)"c:\\odbcqry.log");

It then sets the interval by calling the following lines:


   SQLSetConnectOption(hdbc,
            SQL_COPT_SS_PERF_QUERY_INTERVAL,
            1);

The number specified is in seconds, so the call shown above will cause all queries that do not return within one second to be logged.

Once these options have been enabled, the application can turn the logging on and off by calling the following function:


   SQLSetConnectOption(hdbc,
            SQL_COPT_SS_PERF_QUERY,
            SQL_PERF_START);
   SQLSetConnectOption(hdbc,
            SQL_COPT_SS_PERF_QUERY,
            SQL_PERF_STOP);

Note that the option is global to the application, so once the option has been started for any of the ODBC SQL Server connections the application has open, long-running queries from all of the application's ODBC SQL Server connections will be logged.

GATHERING PERFORMANCE DATA

The Microsoft SQL Server ODBC driver offers a couple of options regarding logging performance data for the driver; these are discussed in the "Logging Performance Data" section of "Programming ODBC for SQL Server." Applications can either write the performance data to a log file, or they can read the data into the application using a structure defined in the Odbcss.h header file.

The following commands start and stop performance data gathering:


   SQLSetConnectOption(hdbc,
            SQL_COPT_SS_PERF_DATA,
            SQL_PERF_START);
   SQLSetConnectOption(hdbc,
            SQL_COPT_SS_PERF_DATA,
            SQL_PERF_STOP);

The performance statistics are recorded in a data structure. The statistics are global for all connections made through the driver by the application. For example, if the application starts the performance statistics and opens three connections, the statistics will be global for all three connections. The connections could have been opened concurrently, or the application could have opened them sequentially.

If an application wants to log the performance data to a file, the following command creates the log file:


   SQLSetConnectOption(hdbc,
            SQL_COPT_SS_PERF_DATA_LOG,
            (ULONG)"c:\\odbcperf.log");

The log file is a tab-delimited text file that can easily be viewed in Microsoft Excel using the Open command on the File menu, and selecting the tab-delimited defaults in the File Open Wizard. Most other spreadsheet products also support opening a tab-delimited text file.

The application would then use the following command any time it wanted to write a record to the performance log, with the current contents of the data structure recording the performance data:


   SQLSetConnectOption(hdbc,
            SQL_COPT_SS_PERF_DATA_LOG_NOW,
            (ULONG)NULL);

The application does not need to set up a performance log; it could instead pull the performance data into the application by using SQLGetData to get a pointer to the sqlperf structure. This structure is typedef'd in the Odbcss.h header file. The following statements provide an example of pulling the statistics into the application:


   SQLPERF *PerfPtr;

   // initialize PerfPtr with pointer to performance data.
   SQLGetConnectOption(hdbc,
            SQL_COPT_SS_PERF_DATA,
            &PerfPtr);
   printf("SQLSelects = %d, SQLSelectRows = %d\n",
            PerfPtr->SQLSelects, PerfPtr->SQLSelectRows);

CONTROLLING QUERY AND PERFORMANCE LOGGING WITH ODBC ADMINISTRATOR

In addition to controlling query and performance logging using SQLSetConnectOption(), you can request logging while managing an ODBC data source using ODBC Administrator. When you work with a SQL Server ODBC data source, the driver's dialog box contains an Options button in the lower right corner. When you click this button, the driver creates a Profiling button just above it. When you click this Profiling button, the driver presents another dialog box for managing the profiling.

You can use the Profiling dialog box to request logging of long-running queries, and can specify both the log file and the query interval. Note that this interval is specified in milliseconds, not seconds as with SQL_COPT_SS_PERF_QUERY_INTERVAL. When the application first connects to a data source that specifies query logging, the driver starts logging all long-running queries from all connections to the SQL Server driver from the application. It stops logging the queries when the last active connection to the driver is closed.

You can also use the Profiling dialog box to request logging of the performance statistics, and to specify the log file for the statistics. When the application first connects to a data source that specifies statistics logging, the driver writes the statistics header information to the log file and starts accumulating the statistics in its internal data structure. When the last connection to the SQL Server driver from the application is closed, the driver writes out the global accumulated performance statistics.

You should remember that the performance statistics and long-running query logging are global to the driver, and this governs the behavior of the log files. When you connect to a data source that specifies profiling, the driver starts a log file and begins logging information from all connections active from the application to the SQL Server driver from that point forward. Even connections to data sources that do not specify profiling will be recorded, because the profiling is done globally for the driver. If the application does a SQLFreeEnv(), the ODBC Driver Manager will unload the driver. At this point, both the long-running query log and the performance statistics logs will hold the information from the old connections. If the application then makes another connection to the data source that specifies profiling, the driver is reloaded, and it overwrites the old copy of the log file. If an application connects to a data source that specifies profiling, and then a second application connects to the same data source, the second application will not get control of the log file, and therefore will not be able to log any performance statistics or long-running queries. If the second application makes the connection after the first application disconnects, the driver overwrites the first application's log file with the one for the second application.

Note that if an application connects to a data source that has either the long-running query or performance statistics enabled, the driver will return SQL_ERROR. If the application calls SQLSetConnectOption() to enable logging, a call to SQLError() returns the following message:

SQLState: 01000, pfNative = 0
szErrorMsg: [Microsoft][ODBC SQL Server Driver]
An error has occurred during an attempt to access
the log file, logging disabled.

DEFINITIONS OF THE SQL SERVER ODBC PERFORMANCE STATISTICS

The meaning of the variables defined in the sqlperf structure are given in this section. These descriptions also apply to the statistics recorded in the performance log file.

APPLICATION PROFILE STATISTICS

TimerResolution
The minimum resolution of the server's clock time in milliseconds. This will usually be reported as 0 (zero). The only time this statistic should be considered is if the number reported is large. If the minimum resolution of the server clock is larger than the likely interval for some of the timer based statistics, those statistics may be inflated.

SQLidu The number of INSERT, DELETE, or UPDATE commands since SQL_PERF_START.

SQLiduRows The number of rows affected by INSERT, DELETE, or UPDATE commands since SQL_PERF_START.

SQLSelects
The number of SELECTs processed since SQL_PERF_START.

SQLSelectRows
The number of rows selected since SQL_PERF_START.

Transactions
The number of user transactions since SQL_PERF_START. For example, suppose an application had run the following statements:

SQLSetConnectOption(hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_ON);
does some work.
SQLTransact(henv, hdbc, SQL_COMMIT);
does some more work.
SQLTransact(henv, hdbc, SQL_ROLLBACK);

This constitutes two user transactions. Even though the second transaction was rolled back, it still is counted as a transaction. Also, when an ODBC application is running with SQL_AUTOCOMMIT_ON, each individual command is considered a transaction.

SQLPrepares
The number of SQLPrepares since SQL_PERF_START.

ExecDirects
The number of SQLExecDirects since SQL_PERF_START.

SQLExecutes
The number of SQLExecutes since SQL_PERF_START.

CursorOpens
The number of times the driver has opened a server cursor since SQL_PERF_START.

CursorSize
The number of rows in the result sets opened by cursors since SQL_PERF_START.

CursorUsed
The number of rows actually retrieved through the driver from cursors since SQL_PERF_START.

PercentCursorUsed
PercentCursorUsed = CursorUsed/CursorSize. For example, if an application causes the driver to open a server cursor to do a select count(*) from authors, 23 rows will be in the result set for the select. If the application then only fetches three of these rows, CursorUsed/CursorSize is 3/23, so PercentCursorUsed is 13.043478.

AvgFetchTime
AvgFetchTime = SQLFetchTime/SQLFetchCount.

AvgCursorSize
AvgCursorSize = CursorSize/CursorOpens.

AvgCursorUsed
AvgCursorUsed = CursorUsed/CursorOpens.

SQLFetchTime
The cumulative amount of time it took fetches against Server Cursors to complete.

SQLFetchCount
The number of fetches done against server cursors since SQL_PERF_START.

CurrentStmtCount
The number of statement handles currently open on all connections open in the driver.

MaxOpenStmt
The maximum number of concurrently opened statement handles since SQL_PERF_START.

SumOpenStmt
The number of statement handles that have been opened since SQL_PERF_START.

CONNECTION STATISTICS

CurrentConnectionCount
The current number of active connection handles the application has open to the server.

MaxConnectionsOpened
The maximum number of concurrent connection handles opened since SQL_PERF_START.

SumConnectionsOpened
The sum of the number of connection handles that have been opened since SQL_PERF_START.

SumConnectionTime
The sum of the amount of time for which all of the connections have been opened since SQL_PERF_START. For example, if an application opened 10 connections and maintained each connection for 5 seconds, then SumConnectionTime would be 50 seconds.

AvgTimeOpened
AvgTimeOpened = SumConnectionsOpened / SumConnectionTime.

NETWORK STATISTICS

SQL Server uses an application protocol called Tabular Data Stream (TDS) to communicate between clients and the server. The network packet statistics reported by the driver relate to the TDS packets. The size of a TDS packet is either the server's default setting specified in sp_configure 'network packet size,' or what the ODBC client might request through SQLSetConnectOption(hdbc, SQL_PACKET_SIZE, NNNN). These packets may be larger than the size of the network packets actually sent by the underlying protocol stack (such as TCP/IP or SPX/IPX). The SQL Server Network Library DLLs and the underlying protocol stack are the components that map the TDS packets onto the network packets, but this is hidden from both the SQL Server ODBC driver and the DB-Library DLL.

ServerRndTrips
The number of times the driver sent commands to the server and got a reply back.

BuffersSent
The number of TDS packets sent to SQL Server by the driver since SQL_PERF_START. Large commands may take multiple buffers, so if a large command is sent to the server that filled six packets, ServerRndTrips would be incremented by one, and BuffersSent incremented by six.

BuffersRec
The number of TDS packets received by the driver from SQL Server since the application started using the driver.

BytesSent
The number of bytes of data sent to SQL Server in TDS packets since the application started using the driver.

BytesRec
The number of bytes of data in TDS packets received by the driver from SQL Server since the application started using the driver.

TIME STATISTICS

MsExecutionTime
The cumulative amount of time the driver spent doing its processing since SQL_PERF_START, including the time it spent waiting for replies from the server.

MsNetworkServerTime
The cumulative amount of time the driver spent waiting for replies from the server.


Keywords          : kbinterop kbprg kbusage SSrvProg 
Version           : 2.5 6.5
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: April 2, 1999