OLEDB, sql server, and unmanaged heap memory leak Options
pb
unmanaged memory.
Using the debug diagnostic tool 1.1, the dump analysis report shows
that:
msdatl3.dll (a known Windows memory manager) is responsible for 828.81
MBytes worth of outstanding allocations. These allocations
appear to have originated from the following module(s) and
function(s):
sqloledb!CDBConnection::GetColData+2c8
The code involved in this leak is performing a SELECT and then reads
the data. One db field of concern is typed nvarchar(MAX).
The code basically looks like this:
Dim x,y,x as String
Dim reader As OleDbDataReader
Dim sqlstr As String = "SELECT * FROM pages WHERE pID = some page id
Dim cmd As New OleDbCommand(sqlstr, db.getconnection())
reader = cmd.ExecuteReader
reader.Read()
If reader.HasRows() Then
x = reader("col1")
y = reader("col2")
z = reader("col3") ' where col3 is of type nvarchar(MAX)
End If
reader.Close()
The size of col3, nvarchar(MAX), varies between 2,000 and 80,000
bytes.
Question: what am I doing wrong? Am I failing to clean up in some
way (I thought all I had to do was close the reader). Is there a
different/better way to read the nvarchar(MAX) field?
thanks.
Erland Sommarskog
> sqloledb!CDBConnection::GetColData+2c8
>
>
> The code involved in this leak is performing a SELECT and then reads
> the data. One db field of concern is typed nvarchar(MAX).
>
>
> The code basically looks like this:
>
>
> Dim x,y,x as String
>
>
> Dim reader As OleDbDataReader
>
>
> Dim sqlstr As String = "SELECT * FROM pages WHERE pID = some page id
> Dim cmd As New OleDbCommand(sqlstr, db.getconnection())
> reader = cmd.ExecuteReader
> bytes.
As a first attempt, try using SQLNCLI as the OLE DB provider instead
of SQLOLEDB. SQLNCLI has full support for nvarchar(MAX), and in general
fewer bugs than SQLOLEDB.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
pb
you got me thinking... so I spent the evening making it work under the
SQLClient... first look observations:
1. leak appears to be gone
2. its FASTER!!!
i kept (SQL via) OLEDB in the equation for some minor reasons, including
database portability... but now I've constructed the code with a simple
runtime switch (triggers off of the connect str) to select with OLEDB or
Sqlclient.
I will run more tests in the morning to see if the assertions above hold.
Microsoft: you should look into the OLEDB case though with n/varchar(MAX)
reads... unless my code has a problem, it looks like a bug.
Walter Wang [MSFT]
Thanks for the feedback.
What's the MDAC and SQL Server version you're using? Previously we have
some known issue on the ODBC driver in some version, such as this one:
#FIX: Performance degradation and memory leak in the ODBC Driver for SQL
Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;814410
Regards,
Walter Wang (waw...@online.microsoft.com, remove 'online.')
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
pb
SQL Server 2005 SP2
Windows Server 2003 SP2
on the box accessing data MDAC version 2.82.3959.0
I don't think the KB article you referenced it the issue, I suspect that I
am not leaking the column meta data but rather the entire contents of the
nvarchar(MAX) field (though I am not certain).
pb
that did the trick. overnight tests confirm a beautiful memory footprint
(stable, not growing) and much faster access time all around (I am guessing
maybe 30% increase in performance).
thanks for making the suggestion to use the SqlClient.
-phil
Erland Sommarskog
> What's the MDAC and SQL Server version you're using? Previously we have
> some known issue on the ODBC driver in some version, such as this one:
>
> #FIX: Performance degradation and memory leak in the ODBC Driver for SQL
> Server
> http://support.microsoft.com/default.aspx?scid=kb;en-us;814410
provider. Note that OleDbClient does not permit you to use MSDASQL
(OLE DB over ODBC), so memory leaks in ODBC has very little to do with it.
Erland Sommarskog
> you got me thinking... so I spent the evening making it work under the
> SQLClient... first look observations:
> 1. leak appears to be gone
> 2. its FASTER!!!
It should be. There are fewer layers involved.
The only reason to use OleDb client with SQL Server is portability,
but I figured that if you use a proprietary type like nvarchar(MAX),
portability is not on your agenda.
> Microsoft: you should look into the OLEDB case though with n/varchar(MAX)
> reads... unless my code has a problem, it looks like a bug.
Did you try with SQLNCLI?
Walter Wang [MSFT]
Could you please send me some of your test code which could reproduce the
issue of the oledb provider? I'll verify the behavior and pass along to our
product team for further investigation. Thank you.
pb
after the switch I am now getting this error every so often:
System.Data.SqlClient.SqlException: A transport-level error has occurred
when sending the request to the server. (provider: TCP Provider, error: 0 -
An existing connection was forcibly closed by the remote host.) at
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean
breakConnection) at...
nothing in my configuration has changed in the least except the switch from
OLEDB to SQLClient.
any ideas?
Erland Sommarskog
> after the switch I am now getting this error every so often:
>
> System.Data.SqlClient.SqlException: A transport-level error has occurred
> when sending the request to the server. (provider: TCP Provider, error:
> 0 - An existing connection was forcibly closed by the remote host.) at
> System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> Boolean breakConnection) at...
>
> nothing in my configuration has changed in the least except the switch
> from OLEDB to SQLClient.
properly, so each time you connect, you get a new connection from the pool,
rather than reusing an old one. Eventually SQL Server gets thinks you are
in for a DoS attacks and locks you out. This could also explain the memory
leak you had before.
To verify that this may be the problem, run Profiler to watch your
application. If you see a lot of Audit:Login, that does not go together
with sp_reset_connection, you have this problem.
pb
I turned off Shared Memory access in client settings and everything has
cleared up.
see post "sqlclient: existing connection was forcibly closed" in
microsoft.public.sqlserver.clients
and thanks for your help.
