memory leak in ASP(ADO 2.7) with MSSQL2000

[johan]

hi all I have a db (which i imported completely from mssql 6.5 into
mssql 2000) and when i run a select statement in query analyzer it has
no memory loss yet when executing it from asp, the sql server process
does not release the memory used.

here is my code, and i cannot spot where i am not releasing the
resources explicitly.
any help would be greatly appreciated.
<%@ Language="VBScript" %>
<!--#include file="conStr.asp" -->
<?xml version="1.0" ?>
<%
response.expires = -1

dim conn,rs,dom,tableName,nodeArray,fieldList,filterLi st,rootNode,selectStr,hasFilterClause

set conn = server.CreateObject("adodb.connection")
conn.ConnectionString = conStr

hasFilterClause = false

set dom = server.CreateObject("microsoft.xmldom")
dom.loadXml(Request.Form)
'dom.load server.mappath("test.xml")
set rootNode = dom.documentElement
tableName = rootNode.getAttribute("tableName")

set nodeArray = dom.selectNodes("//Fields/*")
for i = 0 to nodeArray.length-1
if i>0 then fieldList = fieldList + ","
fieldList = fieldList + nodeArray(i).nodeName
next

set nodeArray = dom.selectNodes("//Filters/*")
for i = 0 to nodeArray.length-1

filterList = nodeArray(i).firstChild.xml

hasFilterClause = true
next

selectStr = "Select " + fieldList + " From " + tableName

if hasFilterClause then

selectStr = selectStr + " Where " + filterList

end If


set rs = server.createObject("Adodb.Recordset")
conn.open
rs.open selectStr,conn,0,1

dim reStr
reStr = "<return>"
do until rs.eof
reStr = reStr + "<row>"
for i = 0 to rs.fields.count-1
reStr = reStr + "<" + rs.fields.item(i).name + ">"
if not isNull(rs.fields(i).value) then
reStr = reStr + cstr(rs.fields(i).value)
end if
reStr = reStr + "</" + rs.fields.item(i).name+ ">"
next
reStr = reStr+ "</row>"
rs.moveNext
loop
reStr = reStr+ "</return>"

rs.close
conn.close
set rs = nothing
set conn = nothing
set dom = nothing

response.write reStr
%>

[Bob Barrows]

johan wrote:

> hi all I have a db (which i imported completely from mssql 6.5 into
> mssql 2000) and when i run a select statement in query analyzer it has
> no memory loss yet when executing it from asp, the sql server process
> does not release the memory used.

Just why do you think you have a memory leak? You do realize that SQL 2000
manages memory differently from 6.5 don't you? Starting with 7.0, SQL Server
started dynamically pre-empting memory. It takes as much memory as it needs
and does not "give back" that memory until another application needs it.
This is by design. There is no memory leak. Books Online (BOL) covers this
quite well. Look for the article entitled "Dynamically Managing Memory on
Windows NT and Windows 2000". Here's a quote:

----------------------
When running on Microsoft® Windows NT® or Windows® 2000, the default memory
management behavior of the SQL Server database engine is not to acquire a
specific amount of memory, but to acquire as much memory as it can without
generating excess paging I/O. The database engine does this by acquiring as
much memory as is available, while leaving enough memory free to prevent the
operating system from swapping memory.
When an instance of SQL Server starts, it typically acquires 8 to 12 MB of
memory to complete the initialization process. After the instance has
finished initializing, it acquires no more memory until users connect to it
and start generating a workload. The instance then keeps acquiring memory as
required to support the workload. As more users connect and run queries, SQL
Server acquires the additional memory required to support the demand. The
instance will keep acquiring memory until it reaches its memory allocation
target, it will not free any memory until it reaches the lower limit of the
target.
-------------------------

When you open QA, another connection is opened. Did you check the memory
before opening QA? If not, this would explain why the memory did not change
when you ran your query.

There are two factors involved with ADO:
1. It's a new connection - The increase in memory used by SQL was caused by
the opening of another connection, not by the running of the query
2. OLEDB connection pooling - To increase the efficiency of connection
usage, OLEDB does not really close connections when you close them in your
application. It keeps them open for a period of time (default is 60 sec.),
allowing them to be used by other threads. If another connection has not
been requested by an application before the timeout period, the connection
is released.

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[johannes nel]

the reason whjy i think it is a memory leak is because when executing
the query from query analyzer that memory cost is a once off, but when
the query is executed through asp that memory cost is incurred for each
time i run a simmiliar query. thus querying the database 4 times would
incur 4*cost.

i don't think it is a memory leak in the SQL, since this is the first
time i have had a problem like this, but also the firsttime that i
imported a db straight from 6.5 and this db is badly designed, not
indexed, and overall a piece of sheet, but the memory loss is
inixplicable and is killing my testing server. (iam talking about after
10 queries the sql agent would be using 500mb and the queries woul be a
minute-10 minutes apart)

that is why i stated that i cannot find the spot where i am not
releasing my memory (maybe a cursor option that i am misreading the help
docs on?)



thanks for the help though

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows]

johannes nel wrote:

> the reason whjy i think it is a memory leak is because when executing
> the query from query analyzer that memory cost is a once off, but when
> the query is executed through asp that memory cost is incurred for
> each time i run a simmiliar query. thus querying the database 4 times
> would incur 4*cost.
>
> i don't think it is a memory leak in the SQL, since this is the first
> time i have had a problem like this, but also the firsttime that i
> imported a db straight from 6.5 and this db is badly designed, not
> indexed, and overall a piece of sheet, but the memory loss is
> inixplicable and is killing my testing server. (iam talking about
> after 10 queries the sql agent would be using 500mb and the queries
> woul be a minute-10 minutes apart)
>
> that is why i stated that i cannot find the spot where i am not
> releasing my memory (maybe a cursor option that i am misreading the
> help docs on?)
>

Your asp/ado code is fine.
Are IIS and SQL Server on the same machine? If not, are we talking about
memory usage on the IIS box, or the SQL Server box?
How much memory are we talking about here? 500 mb? How can 500mb be
"killing" a server?
What exactly is being killed on your testing server?
Why is sql agent involved?

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[johannes nel]

well 500mb is killing this specific server, since it only has a gig of
mem. iis && sql run on the same machine. when i say sql server agent, i
am reffering to sqlservr.exe process which is consumming the memory.

The big factor for me is that each query (not even concurrent) is taking
more memory thus mem+=lastQuery, and there apears to be no limit on
this.
:D


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows]

johannes nel wrote:

> well 500mb is killing this specific server, since it only has a gig of
> mem. iis && sql run on the same machine. when i say sql server
> agent, i am reffering to sqlservr.exe process which is consumming the
> memory.
>

In what way is it getting "killed"? Are you being required to reboot?

You can limit the amount of memory that SQL Server uses by modifying the
Server propertied, either via Enterprise Manager, or by using sp_configure.
Both approaches are covered in BOL.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[johannes nel]

yeah it causes me to have 2 reboot. curently what i am doing is i am
letting that specific query run of the 6.5 machine (which doesn't have
this problem :S) and getting it working like that.

thank u for all the effort. I was wondering if maybe i did the ado based
code incorrectly since I work in other languages more often, and i
remembered that there is no garbage collection in vb(script) so i
thought i might have made the whole myself.

I think the problem is just a badly designed db, with no indexes.

cheers

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!