Post reply

Profiler and SET FMTONLY

  • January 13, 2005 at 1:33 pm

    #162263

    Hi,

    While running profiler to do some investigations for my asp pages, I have noticed a lot of

    set fmtonly On, the sql phrase and then set fmtonly Off

    example  :

    SET FMTONLY ON Select PROD_VAT_CODE From Product Where PROD_MANF_SKU =

    'Q2477A#401'

    SET FMTONLY OFF

    I know that turning it on means retuns columns info with no data back. So it

    is called when c alling A SP that has a simple T-SQL phrase as the example

    in my trace in profiler and how can I disable so it will not be included in

    the trace results

    I want to add that in my code I never do any manipulation using the set fmtonly. It is a straight forward asp ADO code (open conn, give it  astored procedure, populate parameters and get back the result using a dataset

  • January 15, 2005 at 1:53 pm

    #537580

    I've played around with this a bit in ADO and I've found a couple things:

    1. Use command objects to explicitly bind each parameter for your stored procedure (for EVERYTHING).  This has two perf. benefits:

    a. RPC instead of T-SQL Batch execution

    b. Increases reusability of your execution plans (Less likely to have multiple entries for different combinations of parameters)

    2. Declare connection object separately and bind to command, while setting the CommandType to adCmdStoredProc.  I believe this is the thing that actually removes the need for FMTONLY statements, because sometimes if you just simply pass in a connection object in the Recordset.Open method, it will not use FMTONLY.

    3. After calling the Command.Execute method, you must reassign the ActiveConnection property or it will use FMTONLY.

    Also a side note:

    There is supposedly a PSS hotfix for this but I have not installed it, because of the benefits of command objects outlined above.  But if you get it up and running, please let me know about it.

    http://support.microsoft.com/default.aspx?scid=kb;en-us;836830

    -Max

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply