Profiling ODBC Driver Performance

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.