Poor performance using Set FMTONLY On

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: EBohn (EBohn_at_discussions.microsoft.com)
Date: 10/19/04 

Date: Tue, 19 Oct 2004 07:05:02 -0700

I have two tables for which the application is issuing a query such as this
to get an empty resultset of the correct structure:

Set FMTONLY On Select col1, col2 from table1 Set FMTONLY Off

Both queries specify the column names (no asterisks). Both tables have
about 1 million rows. One of these statements executes immediately and does
almost no I/O. The other takes 30 seconds and does 800,000 reads. The only
thing that I can find that is different is that the table for the fast one
has a clustered index and the table for the slow one has no clustered index.
Does this make sense? I'm reluctant to just add the clustered index for this
client because this is a production app and adding that index could be quite
time consuming. I am considering copying the table to another (temporary)
database and adding the clustered index there, but would like some opinions
on this first.

I have looked at article 839801 but I don't think that applies since one of
my queries is fast.

Thanks for you help

Relevant Pages

  • Re: Poor performance using Set FMTONLY On
    ... > I have two tables for which the application is issuing a query such as ... > Both queries specify the column names. ... > has a clustered index and the table for the slow one has no clustered ...
    (microsoft.public.sqlserver.programming)
  • RE: Poor performance using Set FMTONLY On
    ... The primary key of the table was not defined as a clustered index. ... > Both queries specify the column names (no asterisks). ...
    (microsoft.public.sqlserver.programming)