November 8, 2004 at 2:04 pm
I have the following Profiler Trace Results...
13 SET FMTONLY ON select LNUM,CBSOURCE,BOR_NUM,BOR_DBID,CNTR,DBID from CB_TRADE SET FMTONLY OFF
40 SET FMTONLY ON
41 SET FMTONLY ON
40 select LNUM,CBSOURCE,BOR_NUM,BOR_DBID,CNTR,DBID, from CB_TRADE
40 select LNUM,CBSOURCE,BOR_NUM,BOR_DBID,CNTR,DBID, from CB_TRADE
... (occurs 1522 times)
40 select LNUM,CBSOURCE,BOR_NUM,BOR_DBID,CNTR,DBID, from CB_TRADE
40 select LNUM,CBSOURCE,BOR_NUM,BOR_DBID,CNTR,DBID, from CB_TRADE
12 SET FMTONLY ON select LNUM,CBSOURCE,BOR_NUM,BOR_DBID,CNTR,DBID from CB_TRADE SET FMTONLY OFF
16 NULL
The query that is going through ODBC actually has a WHERE clause, however, for some reason,
ODBC is stripping it off and running the query with the SET FMTONLY statements. These statements
are timing out at 30 secs. As you can see from the trace, the eventclass 40 (SQL:StmtStarting) is
executed 1522 times.
We see the same or similar results running through Query Analyzer and osql.
The server is a SQL2K SP3. The table is large, having > 7 million rows. However, I can run the same
query on another box with the same number of rows and the query runs correctly, returning an empty
rowset in less than a second.
Any insight would be greatly appreciated.
Elizabeth
November 10, 2004 at 5:44 am
from bol;
*********************************************
Returns only meta data to the client.
SET FMTONLY { ON | OFF }
No rows are processed or sent to the client as a result of the request when SET FMTONLY is turned ON.
The setting of SET FMTONLY is set at execute or run time and not at parse time.
SET FMTONLY permissions default to all users.
This example changes the SET FMTONLY setting to ON and executes a SELECT statement. The setting causes the statement to return the column information only; no rows of data are returned.
SET FMTONLY ONGOUSE pubsGOSELECT * FROM pubs.dbo.authorsGO*********************************************A) after FMTONLY on you have to put it OFF again somewhere in you script.B) I think stripping the where clause is resulting in a full tablescan
( on 7 million rows !! ) which results in a long waiting period. But to
be exact on this I need to know the SQL script running.RegardsGKramer
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply