The Microsoft SQL Server ODBC driver version 2.65 that shipped with SQLServer 6.5 introduced features to assist in analyzing the performance ofODBC applications using the driver. This article outlines the use of thesefeatures.
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 SQLServer 6.5 Books Online. This article adds to the manuals by outlining howto incorporate these options in an ODBC application. This article assumesthat the application has included the Odbcss.h file that ships with SQLWorkstation 6.5.
LOGGING LONG-RUNNING QUERIES
ODBC applications can request that the driver write to a log file allqueries that take longer than a specified time interval to complete.Administrators and programmers can then analyze the queries in the log fileto determine why they are taking such a long time to complete.
An application specifies the file to use for the log by calling thefollowing 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 allqueries that do not return within one second to be logged.
Once these options have been enabled, the application can turn the loggingon 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 hasbeen started for any of the ODBC SQL Server connections the application hasopen, long-running queries from all of the application's ODBC SQL Serverconnections will be logged.
GATHERING PERFORMANCE DATA
The Microsoft SQL Server ODBC driver offers a couple of options regardinglogging 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 theycan read the data into the application using a structure defined in theOdbcss.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 statisticsare global for all connections made through the driver by the application.For example, if the application starts the performance statistics and opensthree connections, the statistics will be global for all three connections.The connections could have been opened concurrently, or the applicationcould have opened them sequentially.
If an application wants to log the performance data to a file, thefollowing 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 inMicrosoft Excel using the Open command on the File menu, and selecting thetab-delimited defaults in the File Open Wizard. Most other spreadsheetproducts also support opening a tab-delimited text file.
The application would then use the following command any time it wanted towrite a record to the performance log, with the current contents of thedata 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 insteadpull the performance data into the application by using SQLGetData to get apointer to the sqlperf structure. This structure is typedef'd in theOdbcss.h header file. The following statements provide an example ofpulling 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 usingSQLSetConnectOption(), you can request logging while managing an ODBC datasource using ODBC Administrator. When you work with a SQL Server ODBC datasource, the driver's dialog box contains an Options button in the lowerright corner. When you click this button, the driver creates a Profilingbutton just above it. When you click this Profiling button, the driverpresents another dialog box for managing the profiling.
You can use the Profiling dialog box to request logging of long-runningqueries, and can specify both the log file and the query interval. Notethat this interval is specified in milliseconds, not seconds as withSQL_COPT_SS_PERF_QUERY_INTERVAL. When the application first connects to adata source that specifies query logging, the driver starts logging alllong-running queries from all connections to the SQL Server driver from theapplication. It stops logging the queries when the last active connectionto the driver is closed.
You can also use the Profiling dialog box to request logging of theperformance statistics, and to specify the log file for the statistics.When the application first connects to a data source that specifiesstatistics logging, the driver writes the statistics header information tothe log file and starts accumulating the statistics in its internal datastructure. When the last connection to the SQL Server driver from theapplication is closed, the driver writes out the global accumulatedperformance statistics.
You should remember that the performance statistics and long-running querylogging are global to the driver, and this governs the behavior of the logfiles. When you connect to a data source that specifies profiling, thedriver starts a log file and begins logging information from allconnections active from the application to the SQL Server driver from thatpoint forward. Even connections to data sources that do not specifyprofiling will be recorded, because the profiling is done globally for thedriver. If the application does a SQLFreeEnv(), the ODBC Driver Managerwill unload the driver. At this point, both the long-running query log andthe performance statistics logs will hold the information from the oldconnections. If the application then makes another connection to the datasource that specifies profiling, the driver is reloaded, and it overwritesthe old copy of the log file. If an application connects to a data sourcethat specifies profiling, and then a second application connects to thesame data source, the second application will not get control of the logfile, and therefore will not be able to log any performance statistics orlong-running queries. If the second application makes the connection afterthe first application disconnects, the driver overwrites the firstapplication's log file with the one for the second application.
Note that if an application connects to a data source that has either thelong-running query or performance statistics enabled, the driver willreturn SQL_ERROR. If the application calls SQLSetConnectOption() to enablelogging, 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 inthis section. These descriptions also apply to the statistics recorded inthe performance log file.
APPLICATION PROFILE STATISTICS
The minimum resolution of the server's clock time in milliseconds. Thiswill usually be reported as 0 (zero). The only time this statistic shouldbe considered is if the number reported is large. If the minimum resolutionof the server clock is larger than the likely interval for some of thetimer based statistics, those statistics may be inflated.
SQLiduThe number of INSERT, DELETE, or UPDATE commands since SQL_PERF_START.
SQLiduRowsThe number of rows affected by INSERT, DELETE, or UPDATE commands sinceSQL_PERF_START.
The number of SELECTs processed since SQL_PERF_START.
The number of rows selected since SQL_PERF_START.
The number of user transactions since SQL_PERF_START. For example, supposean 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 transactionwas rolled back, it still is counted as a transaction. Also, when an ODBCapplication is running with SQL_AUTOCOMMIT_ON, each individual command isconsidered a transaction.
The number of SQLPrepares since SQL_PERF_START.
The number of SQLExecDirects since SQL_PERF_START.
The number of SQLExecutes since SQL_PERF_START.
The number of times the driver has opened a server cursor sinceSQL_PERF_START.
The number of rows in the result sets opened by cursors sinceSQL_PERF_START.
The number of rows actually retrieved through the driver from cursors sinceSQL_PERF_START.
PercentCursorUsed = CursorUsed/CursorSize. For example, if an applicationcauses the driver to open a server cursor to do a select count(*) fromauthors, 23 rows will be in the result set for the select. If theapplication then only fetches three of these rows, CursorUsed/CursorSize is3/23, so PercentCursorUsed is 13.043478.
AvgFetchTime = SQLFetchTime/SQLFetchCount.
AvgCursorSize = CursorSize/CursorOpens.
AvgCursorUsed = CursorUsed/CursorOpens.
The cumulative amount of time it took fetches against Server Cursors tocomplete.
The number of fetches done against server cursors since SQL_PERF_START.
The number of statement handles currently open on all connections open inthe driver.
The maximum number of concurrently opened statement handles sinceSQL_PERF_START.
The number of statement handles that have been opened since SQL_PERF_START.
The current number of active connection handles the application has open tothe server.
The maximum number of concurrent connection handles opened sinceSQL_PERF_START.
The sum of the number of connection handles that have been opened sinceSQL_PERF_START.
The sum of the amount of time for which all of the connections have beenopened since SQL_PERF_START. For example, if an application opened 10connections and maintained each connection for 5 seconds, thenSumConnectionTime would be 50 seconds.
AvgTimeOpened = SumConnectionsOpened / SumConnectionTime.
SQL Server uses an application protocol called Tabular Data Stream (TDS) tocommunicate between clients and the server. The network packet statisticsreported by the driver relate to the TDS packets. The size of a TDS packetis either the server's default setting specified in sp_configure 'networkpacket size,' or what the ODBC client might request throughSQLSetConnectOption(hdbc, SQL_PACKET_SIZE, NNNN). These packets may belarger than the size of the network packets actually sent by the underlyingprotocol stack (such as TCP/IP or SPX/IPX). The SQL Server Network LibraryDLLs and the underlying protocol stack are the components that map the TDSpackets onto the network packets, but this is hidden from both the SQLServer ODBC driver and the DB-Library DLL.
The number of times the driver sent commands to the server and got a replyback.
The number of TDS packets sent to SQL Server by the driver sinceSQL_PERF_START. Large commands may take multiple buffers, so if a largecommand is sent to the server that filled six packets, ServerRndTrips wouldbe incremented by one, and BuffersSent incremented by six.
The number of TDS packets received by the driver from SQL Server since theapplication started using the driver.
The number of bytes of data sent to SQL Server in TDS packets since theapplication started using the driver.
The number of bytes of data in TDS packets received by the driver from SQLServer since the application started using the driver.
The cumulative amount of time the driver spent doing its processing sinceSQL_PERF_START, including the time it spent waiting for replies from theserver.
The cumulative amount of time the driver spent waiting for replies from theserver.