INF: SET STATISTICS and the Microsoft SQL Server ODBC Driver (145645)

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);
				

the statistics will be returned through SQLError(). The time at which the application should call SQLError(), however, is different for each option.

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."
				

The call to SQLError() must be made immediately after the SQLExecute() or SQLExecDirect() function call that generates the output.

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."
				

There will be one 3615 message for each table involved in the query.