Cursor causing memory leaks?

Posted on 2006-06-05
Last Modified: 2011-09-20

I have a co-worker that is anti-microsoft, and he's been sending out emails complaining that his stored procedures are causing memory leaks in SQL Server.

I looked at all the stored procedures, and they all have me a bit worried...

Look below, here is what is being done, notice the cursor is being declared/exec using an exec() statement, which worries me.  Is this a bad practice?  Is this bad generally.  Can someone point me to something that says this is an awful way of doing things?

--cursor below, likely syntactically incorrect because I removed company related items

SET @execstr = '
declare rs cursor for
SELECT blah blah blah
FROM OPENROWSET(''MSDASQL'',''DSN='+ @DSN +''',''select * from some_other_db''')'


open rs

fetch next from rs

--usual cursor iteration stuff

close rs
deallocate rs
Question by:raterus
    LVL 33

    Assisted Solution

    Try eliminating the cursor altogether by using a table variable instead:
    LVL 33

    Author Comment

    ahem..let me clarify

    This is another co-worker's work, and he's anti-microsoft all the way, he's fully content to blame Microsoft & SQL Server for memory leaks he's been experiencing, not his code.  I'm frankly getting annoyed as his constant Microsoft/SQL Server bashing, so I'd like to give him real proof he's the one at fault.  I'd looking for some good arguments to use to convince him of his error with the example I gave in my question.

    LVL 68

    Accepted Solution

    While you may not want to hear this, there do appear to be known issues with memory leaks and cursors on remote/linked servers.  So, unless you are on SP4, these problems would continue.  Odd, it seems almost as if he were trying to create memory leaks, so that he could send out nasty e-mails ... .

    Reply -- with very careful wording -- essentially that he needs to speak with the leadership of your company if he wants to quit using MS products.  Otherwise, he needs to use what is there to maximum *benefit*, not just search for detriments.

    LVL 7

    Assisted Solution

    Just for the record, that is a weird way to code. Cursors are bad enough (and almost always a sign of bad database design), but to add that layer on top of it is really poor design.

    I would code the query without using the exec and run them both with SQL Profiler to determine which one is hogging more resources. Ideally (as suggested), you will eliminate it althogether and use JOIN statements to replace it.

    I suppose he thinks Oracle is the realm of the gods.

    LVL 13

    Expert Comment

    Bad code reflects the knowledge of the developer.

    There are plenty of jobs developing in Java if he hates Microsoft so much.
    LVL 33

    Author Comment

    Thank you everyone for your input.

    I've drafted a nice email explaining why not to use sql cursors and sent it out.  We'll see how it goes.  I definitely think what he's done can be rewritten to use joins, now whether he'll a) admit there is a problem and b) rewrite, is up to him.
    LVL 13

    Assisted Solution

    Good Luck!! It's always hard to get someone to change their ways.
    LVL 33

    Author Comment

    Well he never wrote back, I figure he's either busy fixing the problem, or ignored me...Either way, I'm done with this question and I thank you all for your time and insight!

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now