sp_cursorfetch problem

[darronb]

Could anyone please throw some light on a problem I have.

I am not a programmer, I do not understand the code I am seeing, but I am in Tech support trying to figure out what is happening.

We have a bespoke product that stores information into a Microsoft SLQ 2000 Database.

We believe the problem is with our product but we need clarification on how the code is working.

We have been using SQL profiler to watch activity through the database and the question I has is:-

The code below represents, as I believe the way a cursor opens and closes.


CursorOpen 180150089
RPC:Completed declare @P1 int
set @P1=180150089
declare @P2 int
set @P2=2
declare @P3 int
set @P3=4
declare @P4 int
set @P4=-1
exec sp_cursoropen @P1 output, N'SELECT ActionedDate, ActionedTime FROM TempAdjust WHERE InternalID=''1933018566''AND ActionedDate = ''20050119'' AND USED = ''1'' AND NewTotalHours > ''0'' ORDER BY ActionedDate DESC', @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4
RPC:Starting exec sp_cursorfetch 180150089, 2, 0, 1
RPC:Completed exec sp_cursorfetch 180150089, 2, 0, 1
RPC:Starting exec sp_cursorclose 180150089
CursorClose 180150089
RPC:Completed exec sp_cursorclose 180150089

The code below we think is where the problem is, in the bottom line is says "exec sp_cursorfetch" after the "CursorClose", where the above code says "exec sp_cursorclose" after the "CursorClose"

CursorOpen 180150022
RPC:Completed declare @P1 int
set @P1=180150022
declare @P2 int
set @P2=8
declare @P3 int
set @P3=1
declare @P4 int
set @P4=1
exec sp_cursoropen @P1 output, N'SELECT @@IDENTITY', @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4
RPC:Starting exec sp_cursorfetch 180150022, 2, 0, 1
RPC:Completed exec sp_cursorfetch 180150022, 2, 0, 1
RPC:Starting exec sp_cursorfetch 180150022, 2, 0, 1
CursorClose 180150022
RPC:Completed exec sp_cursorfetch 180150022, 2, 0, 1

We believe that the above code is leaving a User Connection open to the SQL database, we can have up to 11000 user connections open at the same time, this uses up memory and the PC the application is running on.

can anyone confirm the above code to be correct or incorrect.

 

[dbomrrsm]

you would also usually deallocate a cursor otherwise it stays around in memory which will be resourse intensive after a while

DEALLOCATE NAME_OF_CURSOR

[blue]DBomrrsm[/blue]

 

[SQLSister]

Also what ever it is will probablly be much faster to run and take up many more resources if it is rewritten to eliminate the cursor.

Further, you need to get rid of that line where it gets the value of @@identity. @@identity it very dangerous to use and can cause data integrity problems if there is a trigger on the table whihc inserts to another table with an identity as it will return the wrong value. Go through your code and replace all of the @@identity instances with scope_identity().

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[vongrunt]

This is profiler output caused by client-side cursors (yup, dynamic SQL). RPC calls are typical for .NET apps (but not strictly limited to), SQL:Batch* for vanilla ADO apps (VB, ASP, blah). Standard recommendations include:

- check client-side code. It should destroy recordset/connection objects ASAP and let connection pooling do it's job.
- change cursor types/location at critical places
- using server-side code instead (stored procedures, eventually views).

Btw. how many sp_reset_connection statements you got in this trace?

 

[darronb]

Hi thanks for all your posts so far.

I have had a look at the trace, which was ran from 10:00 to 16:00 on one day, there are over 6 million rows.

There are no sp_reset_connection statments in the trace.

It is all in lower case ??

 

[vongrunt]

Yup, all in small caps.

1 million trace rows/hour... how many clients use this database?

 

[darronb]

one

 

[vongrunt]

One "client" as customer/installation or one client as a machine?

I'm asking that because my production server takes about a week to generate 6M entries (with default trace template).

 

[darronb]

I have one XP Pro PC running our Application, One connection. (Which accesses the database every 40 seconds), connecting to a Windows 2000 server, this has SQL 2000 loaded and nothing else.

Hope this helps.

 

[darronb]

OK, can anyone verify this.

We have now set up the same database on a different PC with 2 hard drives (Databases installed on the second drive.)

Although our test has been over 48 hours, SQL seems to be handling the avalable memory on the server better than when only 1 hard drive was being used.

Does having two physical hard drives affect memory usage ????