SQL Cursors

[-TheGame-]

Hi guys, I'm working on improving performance in a billing database and am trying to find out if all cursors that are open eventually get closed and de-allocated.

The one of the processes that i'm working to improve loops on 90000-ish records.. which then loops on data usually using cursors from other tables for each of the 90000 records.

My question is how can i detect if my cursors are being closed and de-allocated? this database has about 1300 stored procs (somewhere around 200000 lines of SQL code) so its not like is easy to read through the code to make sure they all get closed

Thanks for any help in advance.

[Edit] - we are running Microsoft SOL Server 2005 FYI.

[nitelife]

I'm not a dba, but have dabbled in sql a few times. I found an article here which uses cursors in an example to create custom looping procedures in sql.

If you look at the code more closely a cursor is declared like this:

            declare @local_cursor cursor
                        set @local_cursor = hCForEachObject
           
            open @local_cursor
            fetch @local_cursor into @name

Later on it's closed like this:

close @local_cursor

This implies that you need to close cursors explicitly. This is the first example I found, so I suggest doing some research on cursors and seeing whether there are automatically deallocating versions.

[-TheGame-]

thanks for the reply, i'm leaning towards most of the cursors are probably being closed because i think we would see locking issues, i have found some that are not being deallocated though and need to dig into that more.

But on the bright side i found the major part of bottle neck for the process a briefly described above and with a few modifications to an index and a couple stored procedures, i was able to get a 5+ hour process time to about 25mins!

[nitelife]

great improvement! It's amazing how much difference optimising some code can be sometimes.

[TwoPlAnKs]

not really sure of an easy way to do it with windows software, short of just installing windows clones of the unix stuff or cygwin

easy though:
cat all.sql your.sql code.sql | grep [[CODE TO OPEN A CURSOR]] | wc -l
cat all.sql your.sql code.sql | grep [[CODE TO CLOSE A CURSOR]] | wc -l

That returns two numbers. If they don't match, you have a cursor not being closed.

To find which one, any scripting language would be able to read through the code, pick out the name of every cursor getting opened and find its close statement, then report any that don't have the close statement. Would be pretty easy in perl (which works well in windows)

[big man]

I'm not that familiar with it, but i would have thought you could use SQL Profiler to look for this

If it's not got a built in function that will do it, it shouldn't be too hard to filter out