SQL Server Performance

SET FMTONLY ON/OFF

Discussion in 'T-SQL Performance Tuning for Developers' started by zylo47, Feb 3, 2004.

  1. zylo47 New Member

    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
  2. trifunk New Member

    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
  3. zylo47 New Member

    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.

Share This Page