Microsoft KB Archive/145645

= INF: SET STATISTICS and the Microsoft SQL Server ODBC Driver =

Article ID: 145645

Article Last Modified on 10/29/2003

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q145645



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

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.

Additional query words: odbc sql6 debug

Keywords: kbhowto kbprogramming KB145645

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.