SQL Server Performance Forum – Threads Archive

the FMTONLY Mystery ???

Hi There I really hope someone here can help me.
I have posted this question on other forums with not any real luck.
Sorry it is so long but i just want to make the problem very clear. I know exactly what FMTONLY does so please dont reply with links to general FMTONLY information.
FMTONLY returns metadata only, either for the ODBC connection or requested via the application through the data provider. The following statement will occur a few hundred times a day on our database server it is one of many FMTONLY statements that have similar issues: SET FMTONLY ON SELECT FFIG_VAL_XX FROM PS_FFG_FCRB_XX_VW SET FMTONLY OFF Now this statement 99.9% of the time takes 0 duration and does maybe less than 10 reads.
A few times a day this same statement will take 60-90 seconds do hundreds of thousands of reads and use ALOT of CPU. Can anyone please tell me why ?
I have run parrallel traces looking for recompiles or auto update stats that may be happening. I also believe that these FMTONLY statements should have a where clause, as the very next statement by the SPID is the actual SELECT * FROM PS_FFG_FCRB_XX_VW WITH A WHERE CLAUSE.
But the FMTONLY has no where clause ?
I currently have logged a case with the application company as to why they do not use where clauses on the FMTONLY statements. But that is not my main concern as 99% of the time this takes 0 duration without the where clause anyway.
I just am trying to find out why a few times a day the same FMTONLY statement suddenly goes insane, this results in severe blocking in the database not to mention CPU utilization. If anyone can shed some light on this i really appreciate it. Thanx

Did you update statistics as part of maintenance plan? Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Hi Luis Yes auto update statistics is on.
I ran a parrallel trace for auto update statistics with the FMT problem, although no auto update statistics are generated when this problem happens.
But statistics are regularly updated but they run very quickly and have minimal impact. Thanx

If you run sp_who, is there any process blocking the select statement when it’s taking a long time? Regards, Robert.

Yes the only blocking in the database is caused by this long running FMTONLY statement. Thanx

If I were you I’ll try tow things: 1) Full upadate statistics (no auto statistics on).
2) Leave only one processor for paralelims (if you have more than one). Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Hi Luis Thanx, but i dont think turning off auto stats will help as i have traced auto stats and they occur infrequently and when they do it is very fast so i cannot see it related to this problem.
Parrallelism maybe, but may that perhaps not have other repercusions on performance ?
How do you think it can influence the problem i am having ? Thanx

Cuold be, try using MAXDOP 1 for that specific query and see what’s goin on. About auto statistics on, I don’t said to set to off. What I try to said is to run Update Statistics with full scan.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>