Profiling ODBC Driver Performance

 

System_CAPS_ICON_warning.jpg Warning

SQL Server Native Client (SNAC) is not supported beyond SQL Server 2012. Avoid using SNAC in new development work, and plan to modify applications that currently use it. The Microsoft ODBC Driver for SQL Server provides native connectivity from Windows to Microsoft SQL Server and Microsoft Azure SQL Database.

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 fieldDescription
TimerResolutionMinimum 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.
SQLiduNumber of INSERT, DELETE, or UPDATE statements after SQL_PERF_START.
SQLiduRowsNumber of INSERT, DELETE, or UPDATE statements after SQL_PERF_START.
SQLSelectsNumber of SELECT statements processed after SQL_PERF_START.
SQLSelectRowsNumber of rows selected after SQL_PERF_START.
TransactionsNumber 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.
SQLPreparesNumber of SQLPrepare Function calls after SQL_PERF_START.
ExecDirectsNumber of SQLExecDirect calls after SQL_PERF_START.
SQLExecutesNumber of SQLExecute calls after SQL_PERF_START.
CursorOpensNumber of times the driver has opened a server cursor after SQL_PERF_START.
CursorSizeNumber of rows in the result sets opened by cursors after SQL_PERF_START.
CursorUsedNumber of rows actually retrieved through the driver from cursors after SQL_PERF_START.
PercentCursorUsedEquals 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.
AvgFetchTimeEquals SQLFetchTime/SQLFetchCount.
AvgCursorSizeEquals CursorSize/CursorOpens.
AvgCursorUsedEquals CursorUsed/CursorOpens.
SQLFetchTimeCumulative amount of time it took fetches against server cursors to complete.
SQLFetchCountNumber of fetches done against server cursors after SQL_PERF_START.
CurrentStmtCountNumber of statement handles currently open on all connections open in the driver.
MaxOpenStmtMaximum number of concurrently opened statement handles after SQL_PERF_START.
SumOpenStmtNumber of statement handles that have been opened after SQL_PERF_START.
Connection Statistics:
CurrentConnectionCountCurrent number of active connection handles the application has open to the server.
MaxConnectionsOpenedMaximum number of concurrent connection handles opened after SQL_PERF_START.
SumConnectionsOpenedSum of the number of connection handles that have been opened after SQL_PERF_START.
SumConnectionTimeSum 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.
AvgTimeOpenedEquals SumConnectionsOpened/ SumConnectionTime.
Network Statistics:
ServerRndTripsThe number of times the driver sent commands to the server and got a reply back.
BuffersSentNumber 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.
BuffersRecNumber of TDS packets received by the driver from SQL Server after the application started using the driver.
BytesSentNumber of bytes of data sent to SQL Server in TDS packets after the application started using the driver.
BytesRecNumber of bytes of data in TDS packets received by the driver from SQL Server after the application started using the driver.

Time Statistics

SQLPERF FieldDescription
msExecutionTimeCumulative amount of time the driver spent processing after SQL_PERF_START, including the time spent waiting for replies from the server.
msNetworkServerTimeCumulative amount of time the driver spent waiting for replies from the server.

SQL Server Native Client (ODBC)
Profiling ODBC Driver Performance How-to Topics (ODBC)

Community Additions

ADD
Show: