May 29, 2008 at 10:50 am
We had our SQL server freeze up... Nobody could log in, not via SQA, EM, Isql, or osql.
So we did what we normally do, reboot the box... too many business critical apps run on it.
And I did what I should have done a while back and stuck a trace on it... and now I'm looking...
I noticed that in 10 minutes of tracing that I see about 100K calls to sp_cursoropen.
Since it's an undocumented procedure I had to look it up... and found this.
http://jtds.sourceforge.net/apiCursors.html
This leads me to believe that this only gets called when a cursor is declared and then opened to scroll through data (ugh).
Are there any other times when this thing can be called? I'd like to have words with the developers and talk to them about not using so many of the bloody things, but if it's happening because of them stepping through a record set, or something else that they may need to do, as opposed to them just being lazy and using a cursor where they could probably do better without one, then I'd like to have my cards ready for when it comes time to play the discussion game with our devs...
Any thoughts?
May 29, 2008 at 11:26 am
sp_cursoropen, sp_cursorfetch and sp_cursorclose are used when a SQL Statement includes local and a remote tables such as
select * from localtable join Remote.DB.owner.remotetable on
[\code]
Effectively, for each row in the local table, a call to sp_cursorfetch on the remote server is performed. This is usually very resource intensive and can affect the performance of the remote server.
For the spid, the trace should have the hostname and login used by the remote server.
SQL = Scarcely Qualifies as a Language
May 29, 2008 at 1:28 pm
That's just another possible cause right? Because every time I track down an sp_cursoropen call... it's coming from client machines and terminal servers that don't have SQL server installed.
May 29, 2008 at 5:46 pm
Those cursor procedures are used by ODBC drivers when you're addressing (updating, inserting) more than 1 row.
Because ODBC driver is a normal procedural program it cannot perform set-based data alterations. It has to open cursor and do it row by row.
BTW, linked servers also use ODBC drivers.
So, keep in mind, every time you run INSERT/UPDATE on remote server you implicitly open a cursor. With all "favourite features" of RBAR. Including deadlocks, frosen servers, etc.
_____________
Code for TallyGenerator
May 30, 2008 at 8:38 am
Sergiy (5/29/2008)
Those cursor procedures are used by ODBC drivers when you're addressing (updating, inserting) more than 1 row.Because ODBC driver is a normal procedural program it cannot perform set-based data alterations. It has to open cursor and do it row by row.
So something to communicate to my devs would be that they should execute their inserts/updates/deletes by throwing a sql command execute type of adodc call instead of opening a recordset and stepping through record by record to make changes correct?
I'm so rusty on coding... it's been at least 10 years since I actually stepped through a recordset and changed records one by one instead of building a sql command and telling the server to execute it.
Sergiy (5/29/2008)
BTW, linked servers also use ODBC drivers.So, keep in mind, every time you run INSERT/UPDATE on remote server you implicitly open a cursor. With all "favourite features" of RBAR. Including deadlocks, frosen servers, etc.
I was afraid of that... I know we have cross server queries... does it do the same thing if they are just across databases on the same server?
May 30, 2008 at 4:12 pm
So something to communicate to my devs would be that they should execute their inserts/updates/deletes by throwing a sql command execute type of adodc call instead of opening a recordset and stepping through record by record to make changes correct?
If you need to update more than one row from application you're probably ding something wrong.
It's really rare cases when you really need this. Almost every time it's a bad design and bad developers' thinking.
Learn from standard tools. They're proven to be right.
In EM a row goes to "Edit" state as soon as you have changed something. It remains in "Edit" while you continue editing within that row. And it tries to commit changes immediately after you switch to another row.
There is no chance you can edit more than 1 row in any time.
It's a good and free advice for you. Take it.
I was afraid of that... I know we have cross server queries... does it do the same thing if they are just across databases on the same server?
No, recordsets returned by queries within the same server allocate space from the same memory. There is no need for any driver to pass it from one database to another.
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply