MS SQL Server - Distributed query memory leak?

Author

Distributed query memory leak?

Mark Baldwin

2006-10-24, 6:28 pm

I have written an OLEDB data provider using the provider templates installed
with VS.NET 2005.

The provider appears to leak memory!

To find the cause of the leak I have created a simple provider using *only*
the templates that come with VS. So all I have done is create a new project

CREATE THE PROJECT
------------------
1. Click File/New project
2. Select "ATL Project" - name it "LeakTest"
3. Configuration dialog appears - click Finish
4. Right-click project in Solution Explorer, select Add Class
5. Add class dialog appears - select ATL OLEDB Provider
6. ATL OLEDB Provider wizard appears - in "shortname" enter "LeakTest",
click Finish

7. In LeakTestRS.h replace the lines...

PROVIDER_COLUMN_ENTR
Y_STR("FileName", 4, cFileName)
PROVIDER_COLUMN_ENTR
Y_STR("AltFileName", 5, cAlternateFileName)

with

PROVIDER_COLUMN_ENTR
Y_WSTR("FileName", 4, cFileName)
PROVIDER_COLUMN_ENTR
Y_WSTR("AltFileName", 5, cAlternateFileName)

(this corrects a bug)

8. Compile the project
9. Register the DLL (regsvr32 leaktest.dll)

INSTALL INTO SQL2000(SP4)
-------------------------

1. In Enterprise Manager - expand Security, right click Linked Servers,
select New Linked Server...
2. In Linked server enter a name of "LEAK"
3. In Provider name select "LeakTest Class"
4. Click Provider Options and check "AllInProcess" and "Disallow adhoc
accesses"
5. Click OK and then OK again to add the linked server

TEST THE PROVIDER
-----------------

1. Open up an SQL Query window
2. Run the following query...

SELECT *
FROM OPENROWSET('LeakTest
.LeakTest.1', '';'';'','c:\*.*')

--
Best regards
Mark Baldwin

Tracy McKibben

2006-10-24, 6:28 pm

Mark Baldwin wrote:
> I have written an OLEDB data provider using the provider templates installed
> with VS.NET 2005.
>
> The provider appears to leak memory!
>
> To find the cause of the leak I have created a simple provider using *only*
> the templates that come with VS. So all I have done is create a new project
>
> CREATE THE PROJECT
> ------------------
> 1. Click File/New project
> 2. Select "ATL Project" - name it "LeakTest"
> 3. Configuration dialog appears - click Finish
> 4. Right-click project in Solution Explorer, select Add Class
> 5. Add class dialog appears - select ATL OLEDB Provider
> 6. ATL OLEDB Provider wizard appears - in "shortname" enter "LeakTest",
> click Finish
>
> 7. In LeakTestRS.h replace the lines...
>
> PROVIDER_COLUMN_ENTR
Y_STR("FileName", 4, cFileName)
> PROVIDER_COLUMN_ENTR
Y_STR("AltFileName", 5, cAlternateFileName)
>
> with
>
> PROVIDER_COLUMN_ENTR
Y_WSTR("FileName", 4, cFileName)
> PROVIDER_COLUMN_ENTR
Y_WSTR("AltFileName", 5, cAlternateFileName)
>
> (this corrects a bug)
>
> 8. Compile the project
> 9. Register the DLL (regsvr32 leaktest.dll)
>
> INSTALL INTO SQL2000(SP4)
> -------------------------
>
> 1. In Enterprise Manager - expand Security, right click Linked Servers,
> select New Linked Server...
> 2. In Linked server enter a name of "LEAK"
> 3. In Provider name select "LeakTest Class"
> 4. Click Provider Options and check "AllInProcess" and "Disallow adhoc
> accesses"
> 5. Click OK and then OK again to add the linked server
>
> TEST THE PROVIDER
> -----------------
>
> 1. Open up an SQL Query window
> 2. Run the following query...
>
> SELECT *
> FROM OPENROWSET('LeakTest
.LeakTest.1', '';'';'','c:\*.*')
>
>

Possibly related to this?
http://support.microsoft.com/defaul...d=rss&spid=2852

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Mark Baldwin

2006-10-24, 6:28 pm

Tracey,

Nice find - unfortunately, this hotfix has no effect on the problem.

--
Best regards
Mark

"Tracy McKibben" <tracy@realsqlguy.com> wrote in message
news:45096B79.9020100@realsqlguy.com...
> Mark Baldwin wrote:
>
> Possibly related to this?
> http://support.microsoft.com/defaul...d=rss&spid=2852
>
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Peter Yang [MSFT]

2006-10-24, 6:28 pm

Hello Mark,

I understand you found each time when a query run via your custom olddb
provider, SQL 2000 will increase its memory usage. You found "private
bytes"counter does increase whiel "total page" counter does not change. You
use memroy leak flag to test and did not find any leaks reported. If I'm
off-base, please let me know.

Since the Total pages counter does not change, it is more like the issue is
cuased by the memory leak inside the OLEDB data provider since its memory
is not included in Total pages counter and it is inside the process of SQL
Server.

To isolate the issue, you may want to check if you could run the provider
"out of process" SQL Server when we configure the linked server options
when "Allow inProcess" to false (by default it is false)

You may want to check if the issue still occurs if it is out of process. If
the issue does not occur with SQL private bytes, it shall be caused by this
provider itself. Please refer to the following article for details: (it
also applies to 2000 as I know)

222123 How to debug OLE DB Providers that are called from SQL Server 7.0
http://support.microsoft.com/defaul...kb;EN-US;222123

Also, you may want to refer to the following article to use umdh tool to
find memory leak in the process by using debugging:

268343 Umdhtools.exe: How to Use Umdh.exe to Find Memory Leaks
http://support.microsoft.com/defaul...kb;EN-US;268343

To find out the root cause of this issue we may need to analyze memory
dumps, this work has to be done by contacting Microsoft Product Support
Services. Therefore, we probably will not be able to resolve the issue
through the newsgroups. If the issue is urgent, I recommend that you open a
Support incident with Microsoft Product Support Services so that a
dedicated Support Professional can assist with this case. If you need any
help in this regard, please let me know.

For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp

If you have any questions or concerns on this, please feel free to let's
know. Thank you!
Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
====================
====================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
====================
====================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.

Mark Baldwin

2006-10-24, 6:28 pm

Peter - thanks, this issue is now resolved. The problem turned out to be the
MSXML components that were being used from within the provider.

--
Best regards
Mark

"Peter Yang [MSFT]" <petery@online.microsoft.com> wrote in message
news:3DMtFMH2GHA.4548@TK2MSFTNGXA01.phx.gbl...
> Hello Mark,
>
> I understand you found each time when a query run via your custom olddb
> provider, SQL 2000 will increase its memory usage. You found "private
> bytes"counter does increase whiel "total page" counter does not change.
> You
> use memroy leak flag to test and did not find any leaks reported. If I'm
> off-base, please let me know.
>
> Since the Total pages counter does not change, it is more like the issue
> is
> cuased by the memory leak inside the OLEDB data provider since its memory
> is not included in Total pages counter and it is inside the process of SQL
> Server.
>
> To isolate the issue, you may want to check if you could run the provider
> "out of process" SQL Server when we configure the linked server options
> when "Allow inProcess" to false (by default it is false)
>
> You may want to check if the issue still occurs if it is out of process.
> If
> the issue does not occur with SQL private bytes, it shall be caused by
> this
> provider itself. Please refer to the following article for details: (it
> also applies to 2000 as I know)
>
> 222123 How to debug OLE DB Providers that are called from SQL Server 7.0
> http://support.microsoft.com/defaul...kb;EN-US;222123
>
> Also, you may want to refer to the following article to use umdh tool to
> find memory leak in the process by using debugging:
>
> 268343 Umdhtools.exe: How to Use Umdh.exe to Find Memory Leaks
> http://support.microsoft.com/defaul...kb;EN-US;268343
>
> To find out the root cause of this issue we may need to analyze memory
> dumps, this work has to be done by contacting Microsoft Product Support
> Services. Therefore, we probably will not be able to resolve the issue
> through the newsgroups. If the issue is urgent, I recommend that you open
> a
> Support incident with Microsoft Product Support Services so that a
> dedicated Support Professional can assist with this case. If you need any
> help in this regard, please let me know.
>
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
>
> If you have any questions or concerns on this, please feel free to let's
> know. Thank you!
> Best Regards,
>
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ====================
====================
==========
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscript...ault.aspx#notif
> ications
> <http://msdn.microsoft.com/subscript...ps/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscript...rt/default.aspx>.
> ====================
====================
==========
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>

Sponsored Links