INF: SET STATISTICS and the Microsoft SQL Server ODBC Driver

• Microsoft SQL Server, versions 6.0 & 6.5

SUMMARY

This article discusses how the Microsoft SQL Server ODBC Driver returns SET STATISTICS IO and SET STATISTICS TIME data to an application.

MORE INFORMATION

The Transact-SQL commands SET STATISTICS IO and SET STATISTICS TIME can be used to obtain information about the performance of SQL statements. When these options are turned on in an ODBC environment:

   SQLExecDirect(hstmt, "SET STATISTICS IO ON", SQL_NTS);
   or
   SQLExecDirect(hstmt, "SET STATISTICS TIME ON", SQL_NTS);

After SET STATISTICS TIME has been turned on, SQLExecute() or SQLExecDirect() will both return SQL_SUCCESS_WITH_INFO on successful calls.

The application can retrieve the time statistics by then calling SQLError() until SQLError() returns SQL_NO_DATA_FOUND. SQLError() will return the time statistics in the following format:

   szSqlState="01000", *pfNativeError= 3613,
   szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
              SQL Server Parse and Compile Time: cpu time = 0 ms."

After SET STATISTICS IO has been turned on, the IO statistics are returned when the end of the result set has been reached. For example, if a select is executed and the result set is retrieved using SQLFetch(), the last SQLFetch() will return SQL_NO_DATA_FOUND. If the application then loops through calls to SQLError() until SQLError() returns SQL_NO_DATA_FOUND, the IO statistics will be returned in the following format:

   szSqlState="01000", *pfNativeError= 3615,
   szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
              Table: testshow  scan count 1,  logical reads: 1,
              physical reads: 0."