SQL Server Performance Forum – Threads Archive

Cursors always results in physical disk reads?

Hi there, I’m currently trying to figure out what I can do about the rather poorish performance of cursors. The application can not be changed, so please don’t suggest that <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />. Now, here’s an output from a trace - RPC:Completed declare @P1 int set @P1=180163244 declare @P2 int set @P2=16 declare @P3 int set @P3=1 declare @P4 int set @P4=-1 exec sp_cursorexecute 39, @P1 output, @P2 output, @P3 output, @P4 output, ’DAT’, 1 select @P1, @P2, @P3, @P4 MicrosoftÂ® Business Solutions-C5Â® Windows (32 bit) for SQL sa 500 53650 384 580 1208 55 2005-11-27 02:48:01.783 It might be difficult to see what’s what here, so here’s a rundown of the interesting numbers - CPU = 500, Reads = 53650, Writes = 384, Duration = 580 As you can see it’s rather horrible, some of the cursors even manages to hit above 1 sec in CPU time, which I believe is the same as 4000 thousand centuries in the computer world. But what puzzles me is that it seems to be slightly busy on the disk as well. This number of reads doesn’t seem to change at all, when adjusting the amount of memory SQL Server can gobble up. So why does the sp_cursorexecute insist on going on the disk, and how come SQL Server doesn’t help out with some free memory? Lastly, is there anything I can do about that (other than throwing hardware at it)? Again, please DO NOT suggest me to rewrite the application.

If application can’t changed, take a look of Execution Plan to find out if any index are neccesary.
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.

But the question I’d really like to get an answer on, if you know the answer <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> is - "But what puzzles me is that it seems to be slightly busy on the disk as well. This number of reads doesn’t seem to change at all, when adjusting the amount of memory SQL Server can gobble up. So why does the sp_cursorexecute insist on going on the disk, and how come SQL Server doesn’t help out with some free memory?"

SQL will not realease memory to OS until unless it is requested and if your SQL Server is shared by other resources then you must opt memory settings on SQL to Min & Max values. If not leave the memory settings to dynamic. In theory sp_cursorexecute creates and populates a cursor from the execution plan created by sp_cursorprepare, this sp_cursorprepare compiles the Transact-SQL statement or batch associated with a cursor into an execution plan but does not create the cursor. MSDN refers
The complete functionality from these stored procedures is made available to SQL Server applications through the API functions they support. For example, the cursor functionality of the sp_cursor system stored procedures is made available to OLE DB applications through the OLE DB API cursor properties and methods, to ODBC applications through the ODBC cursor attributes and functions, and to DB-Library applications through the DB-Library Cursor Library.
To fetch the data SQL has to request the resource from disk to complete the procedure. Capture PERFMON counters for further assessment. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>