[sqlalchemy] Re: How to specify NOLOCK queries in SA (mssql)
Hi Rick, This could involve quite a bit of investigation on our part, so it could take a while before we get to the bottom of it. We're running Windows 2003 as the OS, MS SQL Server 2005, via the SQL Server Native Driver, using PYODBC, Apache 2.2.6 mod_python 3.3.1. The app is based on Pylons, using SQLAlchemy-0.4.4dev_r3557-py2.5 Elixir-0.5.0dev_r2495-py2.5. Now for what it's worth, we made the change I mentioned above to our db. Before the change, we were getting 36 maximum concurrent locks in a day. after the change, that figure went down to 400. It also reduced the load on our CPUs by about 10%. So it helped. We will be doing more examination of what's happening with cursors next week...I'll keep you posted. :) On May 16, 2:20 am, Rick Morrison [EMAIL PROTECTED] wrote: Hi Bruce, I'm considering a switch from pymssql to pyodbc myself in the not-too-distance future, and this thread has me a bit curious about what's going on. This is a subject that may affect SQL more in the future when ODBC and JDBC drivers get more use. I think there's two distinct questions that need to be answered to get to the bottom of this. The first question is why are these queries being issued at all, and from where? Like Mike says, SQLA is playing no part in constructing or issuing these queries. From the bit of googling that I've done so far, it seems that the FMTONLY queries are issued behind the scenes by the data connector to fetch metadata regarding the query. While there's a lot of reasons a data connector might need to have metadata, there's two that seem especially likely when SQLA comes into play: a) There are un-typed bind parameters in the query, and the connector needs to know the data types for some reason. b) There is going to be a client-side cursor constructed, and result metadata is needed to allocate the cursor. From the description you give, I would bet that this is your main issue. If the cause is (a), a fix might be problematic, as SQLA issues all of its queries using bind parameters, and I'm not sure if type information is used for each. But if you're using explicit bind parameters, you may want to specify the type on those. As for the more likely cause (b) I would think this could be gotten around by making sure you specify firehose (read-only, forward-processing, non-scrollable) cursors for retrieval, but I'm not sure what the pyodbc settings for this might be. As a bonus, you'll probably see a bit of a performance boost using these types of cursors as well. The second question is more of a mystery to me: ok, so the data connector issues a FMTONLY queryif it's just fetching metadata, why would that cause database locks?. This one I can't figure out. Unless you're calling stored procedures or UDF's that have locking side effects, It's got to be a bug in the data connector. From what I read a FMTONLY query should be pretty fast (other than the round-trip network time), and should lock nothing. Are you running on Windows, or on Unix? What's your ODBC connector? Please post to the list as you work through this and let us know what you find... Rick --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to specify NOLOCK queries in SA (mssql)
Hi Bruce, I'm considering a switch from pymssql to pyodbc myself in the not-too-distance future, and this thread has me a bit curious about what's going on. This is a subject that may affect SQL more in the future when ODBC and JDBC drivers get more use. I think there's two distinct questions that need to be answered to get to the bottom of this. The first question is why are these queries being issued at all, and from where? Like Mike says, SQLA is playing no part in constructing or issuing these queries. From the bit of googling that I've done so far, it seems that the FMTONLY queries are issued behind the scenes by the data connector to fetch metadata regarding the query. While there's a lot of reasons a data connector might need to have metadata, there's two that seem especially likely when SQLA comes into play: a) There are un-typed bind parameters in the query, and the connector needs to know the data types for some reason. b) There is going to be a client-side cursor constructed, and result metadata is needed to allocate the cursor. From the description you give, I would bet that this is your main issue. If the cause is (a), a fix might be problematic, as SQLA issues all of its queries using bind parameters, and I'm not sure if type information is used for each. But if you're using explicit bind parameters, you may want to specify the type on those. As for the more likely cause (b) I would think this could be gotten around by making sure you specify firehose (read-only, forward-processing, non-scrollable) cursors for retrieval, but I'm not sure what the pyodbc settings for this might be. As a bonus, you'll probably see a bit of a performance boost using these types of cursors as well. The second question is more of a mystery to me: ok, so the data connector issues a FMTONLY queryif it's just fetching metadata, why would that cause database locks?. This one I can't figure out. Unless you're calling stored procedures or UDF's that have locking side effects, It's got to be a bug in the data connector. From what I read a FMTONLY query should be pretty fast (other than the round-trip network time), and should lock nothing. Are you running on Windows, or on Unix? What's your ODBC connector? Please post to the list as you work through this and let us know what you find... Rick --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to specify NOLOCK queries in SA (mssql)
Thanks for your speedy response Michael :) Yes, after more searching it does rather look like this is an issue that has to do with MSSQL, rather than SQLA. This post (http:// forums.microsoft.com/MSDN/ShowPost.aspx?PostID=570896SiteID=1) seems to point to a similar opinion, points the finger at db connector parameters. For anyone else who reads this looking for an answer to their own issue, so you know, we have decided to make changes to our database configuration options to remove the default locking of records during a query, as that looks like it may resolve our particular issue. We have the luxury of not having our data used in a way that requires locking. This approach may not be appropriate for other users, but it does suit our needs, so we'll try this out... On May 14, 11:53 am, Michael Bayer [EMAIL PROTECTED] wrote: On May 13, 2008, at 9:46 PM, BruceC wrote: Hi all. I'm running a Pylons App using SA, Elixir connecting to a SQL Server 2005 db via PYODBC. I'm getting a lot of database locks, the statements appear to be like this: SET FMTONLY ON select table1.id from table1 where 1=2 SET FMTONLY OFF I cant locate the string FMTONLY within the MSSQL dialect at all. So this is not something SQLA is issuing. I would like to explicitly make this a NOLOCK select, to prevent these queries from generating locks, as these locks appear to be impacting our application performance server stability. Can anyone point me in the direction of where such a change could be made? our MSSQL devs might be able to help here but also you might want to see what the source of the locking is (i.e. some (Py)ODBC thing); SQLA is not issuing any locking. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to specify NOLOCK queries in SA (mssql)
Just to be a bit more specific, these are the changes we will try in our mssql db: ALTER DATABASE MyDBName SET ALLOW_SNAPSHOT_ISOLATION ON ALTER DATABASE MyDBName SET READ_COMMITTED_SNAPSHOT ON :) On May 14, 4:36 pm, BruceC [EMAIL PROTECTED] wrote: Thanks for your speedy response Michael :) Yes, after more searching it does rather look like this is an issue that has to do with MSSQL, rather than SQLA. This post (http:// forums.microsoft.com/MSDN/ShowPost.aspx?PostID=570896SiteID=1) seems to point to a similar opinion, points the finger at db connector parameters. For anyone else who reads this looking for an answer to their own issue, so you know, we have decided to make changes to our database configuration options to remove the default locking of records during a query, as that looks like it may resolve our particular issue. We have the luxury of not having our data used in a way that requires locking. This approach may not be appropriate for other users, but it does suit our needs, so we'll try this out... On May 14, 11:53 am, Michael Bayer [EMAIL PROTECTED] wrote: On May 13, 2008, at 9:46 PM, BruceC wrote: Hi all. I'm running a Pylons App using SA, Elixir connecting to a SQL Server 2005 db via PYODBC. I'm getting a lot of database locks, the statements appear to be like this: SET FMTONLY ON select table1.id from table1 where 1=2 SET FMTONLY OFF I cant locate the string FMTONLY within the MSSQL dialect at all. So this is not something SQLA is issuing. I would like to explicitly make this a NOLOCK select, to prevent these queries from generating locks, as these locks appear to be impacting our application performance server stability. Can anyone point me in the direction of where such a change could be made? our MSSQL devs might be able to help here but also you might want to see what the source of the locking is (i.e. some (Py)ODBC thing); SQLA is not issuing any locking. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to specify NOLOCK queries in SA (mssql)
On May 13, 2008, at 9:46 PM, BruceC wrote: Hi all. I'm running a Pylons App using SA, Elixir connecting to a SQL Server 2005 db via PYODBC. I'm getting a lot of database locks, the statements appear to be like this: SET FMTONLY ON select table1.id from table1 where 1=2 SET FMTONLY OFF I cant locate the string FMTONLY within the MSSQL dialect at all. So this is not something SQLA is issuing. I would like to explicitly make this a NOLOCK select, to prevent these queries from generating locks, as these locks appear to be impacting our application performance server stability. Can anyone point me in the direction of where such a change could be made? our MSSQL devs might be able to help here but also you might want to see what the source of the locking is (i.e. some (Py)ODBC thing); SQLA is not issuing any locking. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
