FMTONLY, LONG Running ?

Hi There

I have been running a trace for long running queries.
I know what FMTONLY is, simply returns the metadata of the query.

I have found something odd.
SQLBatchComplete:SET FMTONLY ON SELECT SETID,FFIG_FIGURE_XX,(CONVERT(CHAR(10),EFFDT_XX,121)),EFF_STATUS_XX,FFIG_TYP_XX,PRCSR_CLASS_XX,SQL_SEL_TXT_XX,RUN_SEQ_AVAIL_XX,DESCR_XX,FFG_COLUMN_XX,FFG_VAL_COL_XX,LASTUPDOPRID_XX,LASTUPDDTTM_XX FROM PS_FFGDFN_XX_VW SET FMTONLY OFF.

This performs 0 reads with a 0 duration time.

A few commads later the same SPID does the following:
SQLBatchComplete:SET FMTONLY ON SELECT FFIG_VAL_XX FROM PS_FFG_FCRB_XX_VW SET FMTONLY OFF

This performs 44000 reads 449 writes and takes 47 seconds.

What is going on
The same view is involved , the first FMTONLY simply retrieves more columns.
Why is the second FMTONLY taking 47 seconds and the first 0
Does FMTONLY actually perform the select If so why does the first one not do any reads and not take anytime as opposed to the second one

Can anyone shed some light on this for me.

Thanx

Answer this question

FMTONLY, LONG Running ?

cobramatt
Since re-occurring i have also traced that no SP:recompiles, File shrinkage or auto growth are happening.
joeycalisay
Can you monitor recompile of TSQL statements/SPs You are most probably hitting the issue where stats are created on some of the underlying tables due to data change. The creation/update of auto-stats can take a while on large tables. Check out the articles below:

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
http://support.microsoft.com/default.aspx scid=kb;en-us;195565

Could you try the query against the view with OPTION(KEEPFIXED PLAN) hint This will confirm if the recompilation due to stats is an issue here or not.
DavidYA

Does it happen every single time or only the first time you execute the query Note that even though SET FMTONLY ON returns the metadata, the query plan still needs to be generated and that can cause delays if say auto-update stats is enabled & statistics needs to be created on a table. This however depends on the size of the tables, stats etc.
ssharan
Actually on second glance i did say that NO .... are happening.
So i did not mis type. All i was saying it that i have traced for more info and still nothing.
muaddib32
Check out the trace flag that I talked about in my previous post.
MuraliMD
Thanx

I cannot alter the select from the view to use (KEEPFIXED PLAN) as this is application controlled.
I have read the articles.
I am tracing the view and auto stats in profiler to see if it is related i will update this thread as soon as i can results.

Thanx
john blamey
My apologies.
It is not the same view.
But my question still stands.Most FMTONLY commands take 0 Duration and perform no or very few reads.
Why does this one take so long, is the view select actually performed If so how can i stop this behaviour If set FMT only returns metadata why does it need to execute the entire view select without a where clause

Thanx
ddwinter
There are couple of things to note here. If the query is in a SP then SP:Recompile will tell you if a recompile happened. Auto-stats event only tells if stats was updated/created but it is hard to tell which statement caused it. You need to use the trace flag 205 and check the errorlog. Trace flag 205 will give the reason for the recompile which can be due to auto-stats create/update, schema change or set option (quoted_identifier, ansi_nulls etc) change. Note that SQL Server 2005 has a new statement level recompile event that can tell you this information directly. In SQL Server 2000, the only way is to use the trace flag for ad-hoc statements. Lastly, when the blocking/slowness happens it will be good to check the locks and waittypes. That will also give some clue as to what is going on.
abruton
You are going to hate me, my last statement i mewnt to say they are NOT happening ! My apologies !
renzo_xp
My mistake sorry i have looked at more trace files , normally this statement :
SET FMTONLY ON SELECT FFIG_VAL_XX FROM PS_FFG_FCRB_XX_VW SET FMTONLY OFF
Takes no time with 4 reads, still have no clarification on why then sometimes it takes forever with hunderds of thousands of reads

Thanx
Larry30813217
It seems to happen everytime it does and FMTONLY for this view.
If it generates the execution plan why does it perform writes
Without the where clause on this view it can get ugly, anyway to alter this behaviour

Thanx
chiharu09
Hi

Ok i have run a trace on the SET FMTONLY for that view, 99% of the time it is almost instantaneous.
Then 1 in a hundred times the FMT statement takes 60 - 90 seconds.
AT the same time i was running a trace on auto update stats, no long running autostats happened in this same time period.

Please help, this causes a blocking issue for the 60 - 90 second period in our production database !!!

Thanx
FMTONLY, LONG Running ?

Answer this question