SQL Server Performance Forum – Threads Archive

SET FMTONLY ON/OFF

I have a stored procedure being called by a Visual Basic application. When this stored procedure is run from Visual Basic and I trace it using SQL Profiler, the commands SET FMTONLY ON and SET FMTONLY OFF are wrapped around it.
The problem is, when I run the stored procedure using the same parameters that VB sends to it in query analyzer, the stored procedure finishes almost instantaneously. When the stored procedure is run by VB with the FMTONLY options set, it suddenly takes over 30 seconds to execute and the number of reads goes through the roof.
The stored procedure is structured such that each command in it is executed only when a given ‘IF’ condition is true. The parameters that I am passing to it will not cause any of the ‘IF’ conditions to execute, yet when I trace the stored procedure in query analyzer with the FMTONLY option turned on it seems to execute all of the statements within the ‘IF’ statements anyway. Can someone explain to me why this is happening? I can’t seem to find anything online except Microsoft’s definition of FMTONLY stating that the option is used only to return Meta Data to the Client. Thank you,
John

The SET FMTONLY commands have something to do with returning metadata about the resultsets processed, I think I’ve read somewhere that even if you have IF statements in your TSQL it will parse all potential results to see what the potential metadata could be, eg returned column names and types. Could you post the vb code you’re using, this behaviour could be a result of not specifying the specific execute options when running a command in ADO. Cheers
Shaun World Domination Through Superior Software

Turns out that the code which was sending empty parameters was changed to send the correct parameters to the stored procedure and this seems to have corrected the problem.

]]>