Discussion:
[freetds] "SET FMTONLY ON select .." locks table
Yavuz Gökırmak
2014-10-03 14:21:06 UTC
Permalink
Hi all,

I am using freetds to do bulk load from linux to mssql,
it is interesting that we get some problem during "SET FMTONLY ON.." query,
it is just getting table columns but somehow it gets the table lock and
blocks other bulk load operations..

do you have any idea,
is it possible to disable this "set fmtonly" query and give table column
names manually instead..
regards.

dblib.c:1369:dbsqlexec(0xaba400)
dblib.c:6862:dbsqlsend(0xaba400)
mem.c:615:tds_free_all_results()
util.c:156:Changed query state from IDLE to QUERYING
write.c:139:tds_put_string converting 73 bytes of "SET FMTONLY ON select *
from clickstream.dbo.clicks_t SET FMTONLY OFF"
write.c:167:tds_put_string wrote 146 bytes
util.c:156:Changed query state from QUERYING to PENDING
net.c:743:Sending packet
0000 01 01 00 9a 00 00 01 00-53 00 45 00 54 00 20 00 |........ S.E.T. .|
0010 46 00 4d 00 54 00 4f 00-4e 00 4c 00 59 00 20 00 |F.M.T.O. N.L.Y. .|
0020 4f 00 4e 00 20 00 73 00-65 00 6c 00 65 00 63 00 |O.N. .s. e.l.e.c.|
0030 74 00 20 00 2a 00 20 00-66 00 72 00 6f 00 6d 00 |t. .*. . f.r.o.m.|
0040 20 00 65 00 76 00 61 00-6d 00 63 00 6c 00 69 00 | .......... ..c.l.i.|
0050 63 00 6b 00 73 00 74 00-72 00 65 00 61 00 6d 00 |c.k.s.t. r.e.a.m.|
0060 2e 00 64 00 62 00 6f 00-2e 00 63 00 6c 00 69 00 |..d.b.o. ..c.l.i.|
0070 63 00 6b 00 73 00 5f 00-74 00 20 00 53 00 45 00 |c.k.s._. t. .S.E.|
0080 54 00 20 00 46 00 4d 00-54 00 4f 00 4e 00 4c 00 |T. .F.M. T.O.N.L.|
0090 59 00 20 00 4f 00 46 00-46 00 |Y. .O.F. F.|

dblib.c:4639:dbsqlok(0xaba400)
dblib.c:4669:dbsqlok() not done, calling tds_process_tokens()
token.c:540:tds_process_tokens(0xabaee0, 0x7ffff1375b30, 0x7ffff1375b34,
0x6914)
util.c:156:Changed query state from PENDING to READING
--
Yavuz G?k?rmak
Co-Founder & Chief Data Architect
Infoowl Software Solutions & Consultancy

yavuz.gokirmak at infoowl.net
+90 530 420 00 59
www.infoowl.net
Frediano Ziglio
2014-10-03 14:28:27 UTC
Permalink
Post by Yavuz Gökırmak
Hi all,
I am using freetds to do bulk load from linux to mssql,
it is interesting that we get some problem during "SET FMTONLY ON.." query,
it is just getting table columns but somehow it gets the table lock and
blocks other bulk load operations..
do you have any idea,
is it possible to disable this "set fmtonly" query and give table column
names manually instead..
regards.
Hi,
probably adding a (nolock) to the query like

SET FMTONLY ON select * from clickstream.dbo.clicks_t(nolock) SET FMTONLY OFF

can help.
Post by Yavuz Gökırmak
dblib.c:1369:dbsqlexec(0xaba400)
dblib.c:6862:dbsqlsend(0xaba400)
mem.c:615:tds_free_all_results()
util.c:156:Changed query state from IDLE to QUERYING
write.c:139:tds_put_string converting 73 bytes of "SET FMTONLY ON select *
from clickstream.dbo.clicks_t SET FMTONLY OFF"
write.c:167:tds_put_string wrote 146 bytes
util.c:156:Changed query state from QUERYING to PENDING
net.c:743:Sending packet
0000 01 01 00 9a 00 00 01 00-53 00 45 00 54 00 20 00 |........ S.E.T. .|
0010 46 00 4d 00 54 00 4f 00-4e 00 4c 00 59 00 20 00 |F.M.T.O. N.L.Y. .|
0020 4f 00 4e 00 20 00 73 00-65 00 6c 00 65 00 63 00 |O.N. .s. e.l.e.c.|
0030 74 00 20 00 2a 00 20 00-66 00 72 00 6f 00 6d 00 |t. .*. . f.r.o.m.|
0040 20 00 65 00 76 00 61 00-6d 00 63 00 6c 00 69 00 | .......... ..c.l.i.|
0050 63 00 6b 00 73 00 74 00-72 00 65 00 61 00 6d 00 |c.k.s.t. r.e.a.m.|
0060 2e 00 64 00 62 00 6f 00-2e 00 63 00 6c 00 69 00 |..d.b.o. ..c.l.i.|
0070 63 00 6b 00 73 00 5f 00-74 00 20 00 53 00 45 00 |c.k.s._. t. .S.E.|
0080 54 00 20 00 46 00 4d 00-54 00 4f 00 4e 00 4c 00 |T. .F.M. T.O.N.L.|
0090 59 00 20 00 4f 00 46 00-46 00 |Y. .O.F. F.|
dblib.c:4639:dbsqlok(0xaba400)
dblib.c:4669:dbsqlok() not done, calling tds_process_tokens()
token.c:540:tds_process_tokens(0xabaee0, 0x7ffff1375b30, 0x7ffff1375b34,
0x6914)
util.c:156:Changed query state from PENDING to READING
Regards,
Frediano
Yavuz Gökırmak
2014-10-03 14:30:27 UTC
Permalink
Thanks Frediano,

nolock may solve but this query ( or logic )
is hardcode implemented in freetds,
is it configurable?
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
Hi all,
I am using freetds to do bulk load from linux to mssql,
it is interesting that we get some problem during "SET FMTONLY ON.."
query,
Post by Yavuz Gökırmak
it is just getting table columns but somehow it gets the table lock and
blocks other bulk load operations..
do you have any idea,
is it possible to disable this "set fmtonly" query and give table column
names manually instead..
regards.
Hi,
probably adding a (nolock) to the query like
SET FMTONLY ON select * from clickstream.dbo.clicks_t(nolock) SET FMTONLY OFF
can help.
Post by Yavuz Gökırmak
dblib.c:1369:dbsqlexec(0xaba400)
dblib.c:6862:dbsqlsend(0xaba400)
mem.c:615:tds_free_all_results()
util.c:156:Changed query state from IDLE to QUERYING
write.c:139:tds_put_string converting 73 bytes of "SET FMTONLY ON select
*
Post by Yavuz Gökırmak
from clickstream.dbo.clicks_t SET FMTONLY OFF"
write.c:167:tds_put_string wrote 146 bytes
util.c:156:Changed query state from QUERYING to PENDING
net.c:743:Sending packet
0000 01 01 00 9a 00 00 01 00-53 00 45 00 54 00 20 00 |........ S.E.T. .|
0010 46 00 4d 00 54 00 4f 00-4e 00 4c 00 59 00 20 00 |F.M.T.O. N.L.Y. .|
0020 4f 00 4e 00 20 00 73 00-65 00 6c 00 65 00 63 00 |O.N. .s. e.l.e.c.|
0030 74 00 20 00 2a 00 20 00-66 00 72 00 6f 00 6d 00 |t. .*. . f.r.o.m.|
0040 20 00 65 00 76 00 61 00-6d 00 63 00 6c 00 69 00 | ..........
..c.l.i.|
Post by Yavuz Gökırmak
0050 63 00 6b 00 73 00 74 00-72 00 65 00 61 00 6d 00 |c.k.s.t. r.e.a.m.|
0060 2e 00 64 00 62 00 6f 00-2e 00 63 00 6c 00 69 00 |..d.b.o. ..c.l.i.|
0070 63 00 6b 00 73 00 5f 00-74 00 20 00 53 00 45 00 |c.k.s._. t. .S.E.|
0080 54 00 20 00 46 00 4d 00-54 00 4f 00 4e 00 4c 00 |T. .F.M. T.O.N.L.|
0090 59 00 20 00 4f 00 46 00-46 00 |Y. .O.F. F.|
dblib.c:4639:dbsqlok(0xaba400)
dblib.c:4669:dbsqlok() not done, calling tds_process_tokens()
token.c:540:tds_process_tokens(0xabaee0, 0x7ffff1375b30, 0x7ffff1375b34,
0x6914)
util.c:156:Changed query state from PENDING to READING
Regards,
Frediano
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
--
Yavuz G?k?rmak
Co-Founder & Chief Data Architect
Infoowl Software Solutions & Consultancy

yavuz.gokirmak at infoowl.net
+90 530 420 00 59
www.infoowl.net
Frediano Ziglio
2014-10-03 14:41:20 UTC
Permalink
Actually not but as this query is just used to get metadata and as
lock last only the time to get this information I don't see the point
of locking here.

Frediano
Post by Yavuz Gökırmak
Thanks Frediano,
nolock may solve but this query ( or logic )
is hardcode implemented in freetds,
is it configurable?
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
Hi all,
I am using freetds to do bulk load from linux to mssql,
it is interesting that we get some problem during "SET FMTONLY ON.."
query,
Post by Yavuz Gökırmak
it is just getting table columns but somehow it gets the table lock and
blocks other bulk load operations..
do you have any idea,
is it possible to disable this "set fmtonly" query and give table column
names manually instead..
regards.
Hi,
probably adding a (nolock) to the query like
SET FMTONLY ON select * from clickstream.dbo.clicks_t(nolock) SET FMTONLY OFF
can help.
Post by Yavuz Gökırmak
dblib.c:1369:dbsqlexec(0xaba400)
dblib.c:6862:dbsqlsend(0xaba400)
mem.c:615:tds_free_all_results()
util.c:156:Changed query state from IDLE to QUERYING
write.c:139:tds_put_string converting 73 bytes of "SET FMTONLY ON select
*
Post by Yavuz Gökırmak
from clickstream.dbo.clicks_t SET FMTONLY OFF"
write.c:167:tds_put_string wrote 146 bytes
util.c:156:Changed query state from QUERYING to PENDING
net.c:743:Sending packet
0000 01 01 00 9a 00 00 01 00-53 00 45 00 54 00 20 00 |........ S.E.T. .|
0010 46 00 4d 00 54 00 4f 00-4e 00 4c 00 59 00 20 00 |F.M.T.O. N.L.Y. .|
0020 4f 00 4e 00 20 00 73 00-65 00 6c 00 65 00 63 00 |O.N. .s. e.l.e.c.|
0030 74 00 20 00 2a 00 20 00-66 00 72 00 6f 00 6d 00 |t. .*. . f.r.o.m.|
0040 20 00 65 00 76 00 61 00-6d 00 63 00 6c 00 69 00 | ..........
..c.l.i.|
Post by Yavuz Gökırmak
0050 63 00 6b 00 73 00 74 00-72 00 65 00 61 00 6d 00 |c.k.s.t. r.e.a.m.|
0060 2e 00 64 00 62 00 6f 00-2e 00 63 00 6c 00 69 00 |..d.b.o. ..c.l.i.|
0070 63 00 6b 00 73 00 5f 00-74 00 20 00 53 00 45 00 |c.k.s._. t. .S.E.|
0080 54 00 20 00 46 00 4d 00-54 00 4f 00 4e 00 4c 00 |T. .F.M. T.O.N.L.|
0090 59 00 20 00 4f 00 46 00-46 00 |Y. .O.F. F.|
dblib.c:4639:dbsqlok(0xaba400)
dblib.c:4669:dbsqlok() not done, calling tds_process_tokens()
token.c:540:tds_process_tokens(0xabaee0, 0x7ffff1375b30, 0x7ffff1375b34,
0x6914)
util.c:156:Changed query state from PENDING to READING
Regards,
Frediano
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
--
Yavuz G?k?rmak
Co-Founder & Chief Data Architect
Infoowl Software Solutions & Consultancy
yavuz.gokirmak at infoowl.net
+90 530 420 00 59
www.infoowl.net
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
Yavuz Gökırmak
2014-10-03 14:46:11 UTC
Permalink
I think this query may have some unexpected behaivours..

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/395f333e-78f7-49a3-8220-f952368153f2/fmtonly-long-running-?forum=transactsql

But actually I don't understand why it locks the table too..
Post by Frediano Ziglio
Actually not but as this query is just used to get metadata and as
lock last only the time to get this information I don't see the point
of locking here.
Frediano
Post by Yavuz Gökırmak
Thanks Frediano,
nolock may solve but this query ( or logic )
is hardcode implemented in freetds,
is it configurable?
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
Hi all,
I am using freetds to do bulk load from linux to mssql,
it is interesting that we get some problem during "SET FMTONLY ON.."
query,
Post by Yavuz Gökırmak
it is just getting table columns but somehow it gets the table lock
and
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
blocks other bulk load operations..
do you have any idea,
is it possible to disable this "set fmtonly" query and give table
column
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
names manually instead..
regards.
Hi,
probably adding a (nolock) to the query like
SET FMTONLY ON select * from clickstream.dbo.clicks_t(nolock) SET
FMTONLY
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
OFF
can help.
Post by Yavuz Gökırmak
dblib.c:1369:dbsqlexec(0xaba400)
dblib.c:6862:dbsqlsend(0xaba400)
mem.c:615:tds_free_all_results()
util.c:156:Changed query state from IDLE to QUERYING
write.c:139:tds_put_string converting 73 bytes of "SET FMTONLY ON
select
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
*
Post by Yavuz Gökırmak
from clickstream.dbo.clicks_t SET FMTONLY OFF"
write.c:167:tds_put_string wrote 146 bytes
util.c:156:Changed query state from QUERYING to PENDING
net.c:743:Sending packet
0000 01 01 00 9a 00 00 01 00-53 00 45 00 54 00 20 00 |........ S.E.T.
.|
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
0010 46 00 4d 00 54 00 4f 00-4e 00 4c 00 59 00 20 00 |F.M.T.O. N.L.Y.
.|
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
0020 4f 00 4e 00 20 00 73 00-65 00 6c 00 65 00 63 00 |O.N. .s.
e.l.e.c.|
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
0030 74 00 20 00 2a 00 20 00-66 00 72 00 6f 00 6d 00 |t. .*. .
f.r.o.m.|
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
0040 20 00 65 00 76 00 61 00-6d 00 63 00 6c 00 69 00 | ..........
..c.l.i.|
Post by Yavuz Gökırmak
0050 63 00 6b 00 73 00 74 00-72 00 65 00 61 00 6d 00 |c.k.s.t.
r.e.a.m.|
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
0060 2e 00 64 00 62 00 6f 00-2e 00 63 00 6c 00 69 00 |..d.b.o.
..c.l.i.|
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
0070 63 00 6b 00 73 00 5f 00-74 00 20 00 53 00 45 00 |c.k.s._. t.
.S.E.|
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
0080 54 00 20 00 46 00 4d 00-54 00 4f 00 4e 00 4c 00 |T. .F.M.
T.O.N.L.|
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
0090 59 00 20 00 4f 00 46 00-46 00 |Y. .O.F. F.|
dblib.c:4639:dbsqlok(0xaba400)
dblib.c:4669:dbsqlok() not done, calling tds_process_tokens()
token.c:540:tds_process_tokens(0xabaee0, 0x7ffff1375b30,
0x7ffff1375b34,
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
0x6914)
util.c:156:Changed query state from PENDING to READING
Regards,
Frediano
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
--
Yavuz G?k?rmak
Co-Founder & Chief Data Architect
Infoowl Software Solutions & Consultancy
yavuz.gokirmak at infoowl.net
+90 530 420 00 59
www.infoowl.net
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
--
Yavuz G?k?rmak
Co-Founder & Chief Data Architect
Infoowl Software Solutions & Consultancy

yavuz.gokirmak at infoowl.net
+90 530 420 00 59
www.infoowl.net
Frediano Ziglio
2014-10-04 07:55:56 UTC
Permalink
This can be true for views or store procedure but when you do bulk
inserts you use tables.

Frediano
Post by Yavuz Gökırmak
I think this query may have some unexpected behaivours..
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/395f333e-78f7-49a3-8220-f952368153f2/fmtonly-long-running-?forum=transactsql
But actually I don't understand why it locks the table too..
Post by Frediano Ziglio
Actually not but as this query is just used to get metadata and as
lock last only the time to get this information I don't see the point
of locking here.
Frediano
Post by Yavuz Gökırmak
Thanks Frediano,
nolock may solve but this query ( or logic )
is hardcode implemented in freetds,
is it configurable?
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
Hi all,
I am using freetds to do bulk load from linux to mssql,
it is interesting that we get some problem during "SET FMTONLY ON.."
query,
Post by Yavuz Gökırmak
it is just getting table columns but somehow it gets the table lock
and
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
blocks other bulk load operations..
do you have any idea,
is it possible to disable this "set fmtonly" query and give table
column
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
names manually instead..
regards.
Hi,
probably adding a (nolock) to the query like
SET FMTONLY ON select * from clickstream.dbo.clicks_t(nolock) SET
FMTONLY
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
OFF
can help.
Post by Yavuz Gökırmak
dblib.c:1369:dbsqlexec(0xaba400)
dblib.c:6862:dbsqlsend(0xaba400)
mem.c:615:tds_free_all_results()
util.c:156:Changed query state from IDLE to QUERYING
write.c:139:tds_put_string converting 73 bytes of "SET FMTONLY ON
select
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
*
Post by Yavuz Gökırmak
from clickstream.dbo.clicks_t SET FMTONLY OFF"
write.c:167:tds_put_string wrote 146 bytes
util.c:156:Changed query state from QUERYING to PENDING
net.c:743:Sending packet
0000 01 01 00 9a 00 00 01 00-53 00 45 00 54 00 20 00 |........ S.E.T.
.|
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
0010 46 00 4d 00 54 00 4f 00-4e 00 4c 00 59 00 20 00 |F.M.T.O. N.L.Y.
.|
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
0020 4f 00 4e 00 20 00 73 00-65 00 6c 00 65 00 63 00 |O.N. .s.
e.l.e.c.|
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
0030 74 00 20 00 2a 00 20 00-66 00 72 00 6f 00 6d 00 |t. .*. .
f.r.o.m.|
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
0040 20 00 65 00 76 00 61 00-6d 00 63 00 6c 00 69 00 | ..........
..c.l.i.|
Post by Yavuz Gökırmak
0050 63 00 6b 00 73 00 74 00-72 00 65 00 61 00 6d 00 |c.k.s.t.
r.e.a.m.|
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
0060 2e 00 64 00 62 00 6f 00-2e 00 63 00 6c 00 69 00 |..d.b.o.
..c.l.i.|
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
0070 63 00 6b 00 73 00 5f 00-74 00 20 00 53 00 45 00 |c.k.s._. t.
.S.E.|
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
0080 54 00 20 00 46 00 4d 00-54 00 4f 00 4e 00 4c 00 |T. .F.M.
T.O.N.L.|
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
0090 59 00 20 00 4f 00 46 00-46 00 |Y. .O.F. F.|
dblib.c:4639:dbsqlok(0xaba400)
dblib.c:4669:dbsqlok() not done, calling tds_process_tokens()
token.c:540:tds_process_tokens(0xabaee0, 0x7ffff1375b30,
0x7ffff1375b34,
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
Post by Yavuz Gökırmak
0x6914)
util.c:156:Changed query state from PENDING to READING
Regards,
Frediano
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
--
Yavuz G?k?rmak
Co-Founder & Chief Data Architect
Infoowl Software Solutions & Consultancy
yavuz.gokirmak at infoowl.net
+90 530 420 00 59
www.infoowl.net
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
--
Yavuz G?k?rmak
Co-Founder & Chief Data Architect
Infoowl Software Solutions & Consultancy
yavuz.gokirmak at infoowl.net
+90 530 420 00 59
www.infoowl.net
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
Loading...