?
Solved

Cursor causing memory leaks?

Posted on 2006-06-05
8
Medium Priority
?
290 Views
Last Modified: 2011-09-20
Hello,

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''')'

exec(@execstr)

open rs

fetch next from rs
into
@blah,
@blah2

--usual cursor iteration stuff

close rs
deallocate rs
0
Comment
Question by:raterus
8 Comments
 
LVL 33

Assisted Solution

by:knightEknight
knightEknight earned 80 total points
ID: 16837193
Try eliminating the cursor altogether by using a table variable instead:  http://www.sqlservercentral.com/columnists/kAli/eliminatingcursors.asp
0
 
LVL 33

Author Comment

by:raterus
ID: 16837255
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.

0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 920 total points
ID: 16837422
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.

0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 7

Assisted Solution

by:ExpertAdmin
ExpertAdmin earned 920 total points
ID: 16837887
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.

M@
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16846662
Bad code reflects the knowledge of the developer.

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

Author Comment

by:raterus
ID: 16862206
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.
0
 
LVL 13

Assisted Solution

by:Atlanta_Mike
Atlanta_Mike earned 80 total points
ID: 16862683
Good Luck!! It's always hard to get someone to change their ways.
0
 
LVL 33

Author Comment

by:raterus
ID: 16875247
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!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

809 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