Share via

Profiling ODBC Driver Performance

  • Article

The SQL Server Native Client ODBC driver can profile two types of performance data: 

  • Long-running queries.

    The driver can write to a log file any query that does not get a response from the server within a specified amount of time. Application programmers or database administrators can then research each logged SQL statement to determine how they can improve its performance.

  • Driver-performance data.

    The driver can record performance statistics and either write them to a file or make them available to an application through a driver-specific data structure named SQLPERF. The file containing the performance statistics is a tab-delimited file that can be easily analyzed with any spreadsheet that supports tab-delimited files, such as Microsoft Excel.

Either type of profiling can be turned on by:

  • Connecting to a data source that specifies logging.

  • Calling SQLSetConnectAttr to set driver-specific attributes that control profiling.

Each application process gets its own copy of the SQL Server Native Client ODBC driver, and profiling is global to the combination of a driver copy and an application process. When anything in the application turns on profiling, profiling records information for all connections active in the driver from that application. Even connections that did not specifically call for profiling are included.

After the driver has opened a profiling log (either the performance data or long-running query log), it does not close the log until the driver is unloaded by the ODBC Driver Manager, when an application frees all the environment handles it opened in the driver. If the application opens a new environment handle, a new copy of the driver is loaded. If the application then either connects to a data source that specifies the same log file or sets the driver-specific attributes to log to the same file, the driver overwrites the old log.

If an application starts profiling to a log file and a second application attempts to start profiling to the same log file, the second application is not able to log any profiling data. If the second application starts profiling after the first application has unloaded its driver, the second application overwrites the log file from the first application.

If an application connects to a data source that has profiling enabled, the driver returns SQL_ERROR if the application calls SQLSetConnectOption to start logging. A call to SQLGetDiagRec then returns the following:

SQLState: 01000, pfNative = 0
ErrorMsg: [Microsoft][SQL Server Native Client]
   An error has occurred during the attempt to access
   the log file, logging disabled.

The driver stops gathering performance data when an environment handle is closed. If an SQL Server Native Client application has multiple connections, each with its own environment handle, then the driver will stop gathering performance data when any of the associated environment handles are closed.

The driver's performance data can either be stored in the SQLPERF data structure or logged in a tab-delimited file. The data includes the following categories of statistics:

  • Application profile

  • Connection

  • Network

  • Time

In the following table, the descriptions of the fields in the SQLPERF data structure also apply to the statistics recorded in the performance log file.

Application Profile Statistics

SQLPERF field

Description

TimerResolution

Minimum resolution of the server's clock time in milliseconds. This is usually reported as 0 (zero) and should only be considered 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 could be inflated.

SQLidu

Number of INSERT, DELETE, or UPDATE statements after SQL_PERF_START.

SQLiduRows

Number of INSERT, DELETE, or UPDATE statements after SQL_PERF_START.

SQLSelects

Number of SELECT statements processed after SQL_PERF_START.

SQLSelectRows

Number of rows selected after SQL_PERF_START.

Transactions

Number of user transactions after SQL_PERF_START, including rollbacks. When an ODBC application is running with SQL_AUTOCOMMIT_ON, each command is considered a transaction.

SQLPrepares

Number of SQLPrepare Function calls after SQL_PERF_START.

ExecDirects

Number of SQLExecDirect calls after SQL_PERF_START.

SQLExecutes

Number of SQLExecute calls after SQL_PERF_START.

CursorOpens

Number of times the driver has opened a server cursor after SQL_PERF_START.

CursorSize

Number of rows in the result sets opened by cursors after SQL_PERF_START.

CursorUsed

Number of rows actually retrieved through the driver from cursors after SQL_PERF_START.

PercentCursorUsed

Equals CursorUsed/CursorSize. For example, if an application causes the driver to open a server cursor to do "SELECT COUNT(*) FROM Authors," 23 rows will be in the result set for the SELECT statement. If the application then fetches only three of these rows, CursorUsed/CursorSize is 3/23, so PercentCursorUsed is 13.043478.

AvgFetchTime

Equals SQLFetchTime/SQLFetchCount.

AvgCursorSize

Equals CursorSize/CursorOpens.

AvgCursorUsed

Equals CursorUsed/CursorOpens.

SQLFetchTime

Cumulative amount of time it took fetches against server cursors to complete.

SQLFetchCount

Number of fetches done against server cursors after SQL_PERF_START.

CurrentStmtCount

Number of statement handles currently open on all connections open in the driver.

MaxOpenStmt

Maximum number of concurrently opened statement handles after SQL_PERF_START.

SumOpenStmt

Number of statement handles that have been opened after SQL_PERF_START.

Connection Statistics:

 

CurrentConnectionCount

Current number of active connection handles the application has open to the server.

MaxConnectionsOpened

Maximum number of concurrent connection handles opened after SQL_PERF_START.

SumConnectionsOpened

Sum of the number of connection handles that have been opened after SQL_PERF_START.

SumConnectionTime

Sum of the amount of time that all of the connections have been opened after 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

Equals SumConnectionsOpened/ SumConnectionTime.

Network Statistics:

 

ServerRndTrips

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

BuffersSent

Number of Tabular Data Stream (TDS) packets sent to SQL Server by the driver after SQL_PERF_START. Large commands can take multiple buffers, so if a large command is sent to the server and it fills six packets, ServerRndTrips is incremented by one and BuffersSent is incremented by six.

BuffersRec

Number of TDS packets received by the driver from SQL Server after the application started using the driver.

BytesSent

Number of bytes of data sent to SQL Server in TDS packets after the application started using the driver.

BytesRec

Number of bytes of data in TDS packets received by the driver from SQL Server after the application started using the driver.

Time Statistics

SQLPERF Field

Description

msExecutionTime

Cumulative amount of time the driver spent processing after SQL_PERF_START, including the time spent waiting for replies from the server.

msNetworkServerTime

Cumulative amount of time the driver spent waiting for replies from the server.

See Also

Concepts

SQL Server Native Client (ODBC)

Other Resources

Profiling ODBC Driver Performance How-to Topics (ODBC)