关于 ADO 内存泄露 内存增长 的一些个人观点及解决方案

        [本文最初发表于：http://blog.csdn.net/wwwwws/article/details/7417349，转载请保留出处及本声明]

        当我们开发一个ADO客户端程序时，经常会发现程序运行以后，其拥有（占用）的虚拟内存大小不断增加，在一般情况下我们并不希望这样的事情发生，因此我们需要进行一些额外的配置。

       首先，先来分析一下内存增长的原因。内存增长的可能的原因有两点：

       一是由客户端程序的内存使用或管理不正确导致的内存泄露，在编写客户端程序时，申请内存之后没有对应的释放语句。很多初学者没有认识到或弄清楚堆(stack)和栈(heap)的区别而将堆栈的概念混淆，不知道哪些变量存储在堆空间，哪些存储在在栈空间，哪些存储在在全局静态空间——局部变量存放在栈空间不用也无法释放内存，全局或静态变量存放在代码之中同样不可以释放内存，而堆空间常常是用户特意或非特意申请的，用后必须释放而且释放的时机要恰到好处并且与其相关的指针要清空——堆栈概念的模糊在编写非托管程序时可能会发展成为一个可怕的问题，比如说编写C程序尤其是C++程序时，我就曾经在调用CStatic::SetBitmap(HBITMAP)的时候没有接受返回的HBITMAP而导致了一个隐式的内存泄露，这类问题常常很难发觉。

       二是由服务提供者(数据库驱动程序)产生的内存增长，这通常情况下被误认为是内存泄露，实际上任何一个成熟的数据库驱动程序一定是经过缜密设计的，产生内存泄露的可能性很小，内存的增长常常是为了提高性能而由设计者故意设计的。比如说在使用Access数据库(.mdb)的时候就会用到Jet引擎，每一个用到Jet引擎的程序都会加载一个msjet40.dll的动态链接库，这个文件就是Jet引擎。通过对其汇编代码的初步跟踪，发现如果用户程序设计得当（没有内存泄露，且对于使用ADO DB的用户程序来说，Connection和RecordSet都只有一次或少量的CreatInstance()，RecordSet每Open一次都对应一次Close），那么在查询记录时，只要不进行GetChunk或AppendChunk，Jet引擎就会重复利用以往内存而不会申请新的内存；反之，在进行GetChunk或AppendChunk的操作时，由于数据量巨大Jet引擎就会在重复利用内存之后产生新的内存需求，接着就会申请新的所需大小的内存，在GetChunk以后即使调用RecordSet的Close这些内存也不会释放，以便再次GetChunk的时候会提高存取速度。

       其次，接着研究解决方法。在ADO的各个方法之中，我并没有找到任何可以释放这些内存页的函数（也许是不太了解）。我认为既然内存页是由服务提供者申请并管理的，而ADO继承于OLE DB需要透过OLE DB来访问Jet引擎，ADO或OLE DB也都算是一个客户端程序，只要服务提供者没有提供接口函数，那么它就无权干涉由服务提供者申请并管理的内存。因此，我们将重点放在Jet引擎的学习上。

       有一些书目值得我们学习。

Visual C++ 随带的文档应是关于 MFC 实现 DAO 的第一个信息来源。不仅应该查阅 MFC DAO 示例和 MFC 技术说明，还应查阅 Class Library Reference 和 Programming with MFC。

有关 DAO 工作方式的其它信息，请查阅 Microsoft Jet Database Engine Programmer's Guide（Microsoft Press 出版）。

另外还有两篇推荐文章，均位于 Microsoft Developer Network 光盘上，它们是：

"Jet Database Engine ODBC Connectivity"，作者：Black、Neil 和 Stephen Hecht。

这篇文章详细讲述了 Microsoft Jet 如何使用 ODBC 检索服务器数据。对于所有用 DAO 编写重要服务器应用程序的人，这都不失为一篇必读文章。

"ODBC：Architecture，Performance，and Tuning"，作者：Lambert、Colleen。

本白皮书提供对 ODBC 工作方式的良好概述，并以现实、有用的方式讲述了性能问题。

       最后，提供一个解决方案。终于找到一份微软的说明，还算与我们的主题相关（在最后有部分摘抄），意思就是说在注册表中HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Jet 3.5设置一个MaxBufferSize的DWORD为200h即512就搞定。

       可以参考如下代码：

	_ConnectionPtr m_pConn;//_ConnectionPtr对象
	if ( m_pConn == NULL ) {//建立连接
		try{
			m_pConn.CreateInstance("ADODB.Connection");//创建一次即可
			m_pConn->ConnectionTimeout=3;//连接尝试时间
			m_pConn->Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=*****.mdb;"
				"Jet OLEDB:Database Password=*****","","",adModeUnknown);//连接Access数据库
			m_pConn->GetProperties()->GetItem(_variant_t("Jet OLEDB:Max Buffer Size"))->PutValue(_variant_t((long)512,VT_I4));//jet4.0会将输入修正到0x100~0x10000之间
			m_pConn->GetProperties()->GetItem(_variant_t("Jet OLEDB:Recycle Long-Valued Pages"))->PutValue(_variant_t((short)-1,VT_BOOL));
		}catch(_com_error &e){//捕捉异常
			AfxMessageBox(e.Description());
			::ExitProcess(-1);
		}
	}

       另外可以搜索"Jet OLEDB:Max Buffer Size"，"DBPROP_JETOLEDB_MAXBUFFERSIZE"。

       Appendix B: Microsoft Jet 4.0 OLE DB Properties Reference

       总之，即使在做了这些工作之后，Jet的部分虚存（堆空间）仍然会随着每一次查询一点一点地缓慢增加（Connection的日志？），这时唯一的办法似乎就是每隔一段时间关闭一次数据库，几秒钟之后再重新连接。另外，利用一些类似Valgrind的软件进行检测，或者利用软件调试，在VirtualAllocEx处下断点，再查看调用堆栈，分析来源以试图解决。

        PS：Jet引擎微软好像要放弃了，也不知道又有什么新花样，无论出什么新东西，痛苦的都是程序员。最后附上工作记录，也不算白忙活。

        [本文最初发表于：http://blog.csdn.net/wwwwws/article/details/7417349，转载请保留出处及本声明]

      

———————————————————————切割机———————————————————————

http://support.microsoft.com/kb/154384

INF: Jet 3.0 Dynamic Memory Usage and Access ODBC Driver

Article ID: 154384 -   View products that this article applies to.

Retired KB Content Disclaimer

This article was previously published under Q154384

Expand all  |   Collapse all

SUMMARY

Many enhancements have been made to Microsoft Jet 3.0 over the Jet 2.x versions to improve performance. Dynamic Memory Usage is one of them and it is a configurable performance setting.  Microsoft Jet 2.x pre-allocated a default of 512 KB for its buffer size with an upper limit (MaxBufferSize) of 4 MB RAM. The behavior of MaxBufferSize has been changed in Jet 3.0, which allocates memory on an "as- needed" basis up to an internally calculated high water mark. The idea is to efficiently use the memory in large RAM systems without the need to adjust Registry Settings.  The high water mark (MaxBufferSize) for Jet 3.0 is calculated by the following formula:

( (((Total Ram in MB - 12)/4 ) * 1024) + 512 )  KB

For example, for a system with 32 MB RAM, Jet 3.0 uses a calculated MaxBufferSize of 5,632 KB. You can override this value by setting the MaxBufferSize value in the following HKEY_LOCAL_MACHINE subtree:

\SOFTWARE\Microsoft\Jet\3.0\Engines\Jet

For Jet 3.5, the registry location for MaxBufferSize is:

\SOFTWARE\Microsoft\Jet\3.5\Engines\Jet 3.5

In addition, Jet 3.5 settings may be modified at the ODBC data source level. For additional information, please see the following article in the Microsoft Knowledge Base:

168686  PRB: Performance Loss When Upgrading Jet ODBC Driver

For MaxBufferSize (DWORD), enter a value in KB. You may have to add the keys, if you do not have them already in the registry.  The minimum value that Microsoft Jet 3.0 can use by default is 512 KB. However,the minimum value the the user can set is 128 KB. Unlike Jet 2.x, Jet 3.0 can exceed the MaxBufferSize. When this occurs, the engine starts up a background thread to start flushing pages to bring the buffer pool down to the designated MaxBufferSize.  For more information on Jet 3.0, refer to the "Microsoft Jet Database Engine Programmer's Guide" published by Microsoft Press.  Question:   How does MaxBufferSize setting affect ODBC applications using the Access Driver?  Answer:   Microsoft Access ODBC Driver v3.x is based on Jet 3.0 Engine. If you do not set the MaxBufferSize in the Registry and try to insert records continuously into a table, you will notice a huge growth in the memory consumed by the application (process) using the driver. If your application, running on Windows NT, is inserting records without ever closing the database connection, the system may run out of virtual memory.  The memory growth will eventually reach a peak value that is equal to the high water mark computed by Jet 3.0 and can be easily mistaken for a memory leak in the driver. However, it is important to note that it is not a memory leak as the memory growth stabilizes after reaching the peak value. This behavior is by design.  If you don't want the driver/Jet to allocate dynamic memory based on the formula discussed above, it is recommended that you set the MaxBufferSize to a value like 512 KB. You can set it to a higher value to gain better performance.

Properties

Article ID: 154384 - Last Review: August 25, 1999 - Revision: 1.0

APPLIES TO

• Microsoft Open Database Connectivity 3.0
• Microsoft Open Database Connectivity 3.5

Keywords:

KB154384

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top  |   Give Feedback

 

http://support.microsoft.com/kb/168686

PRB: Performance Loss When Upgrading Jet ODBC Driver

Article ID: 168686 -   View products that this article applies to.

System Tip This article applies to a different version of Windows than the one you are using. Content in this article may not be relevant to you.  Visit the Windows XP Solution Center

This article was previously published under Q168686

Expand all  |   Collapse all

SYMPTOMS

A Microsoft Visual C++ database project that uses the Microsoft Access 97 (Jet 3.5) ODBC Driver has a noticeable drop in performance compared to similar projects that use the Microsoft Access 95 (Jet 3.0) ODBC Driver.

Back to the top  |   Give Feedback

CAUSE

The Microsoft Access 97 ODBC driver correctly reads and uses the MaxBufferSize performance setting in the ODBC data source.For applications that deal with large amounts of database activity, the default MaxBufferSize setting of 512K may be too small and result in a performance loss. The default MaxBufferSize setting of 512 was designed for optimal performance when using Jet databases under normal conditions but will restrict the amount of memory allotted to the driver and impact performance when using the driver more aggressively.

Back to the top  |   Give Feedback

RESOLUTION

Increase the MaxBufferSize setting to reduce the amount of swapping that is caused by the more restrictive high watermark of 512K. The MaxBufferSize setting can be pre-set in the ODBC datasource or set programmatically starting in Visual C++ version 5.0 using the SetOption method documented in the DAO SDK. Increasing the MaxBufferSize setting greatly improves performance when the database activity level is high by preventing frequent page swapping. Setting MaxBufferSize to 0 activates Jet's built-in mechanism to determine the high watermark. Please look at the MORE INFORMATION section below for details.

Back to the top  |   Give Feedback

STATUS

This behavior is by design.

Back to the top  |   Give Feedback

MORE INFORMATION

The Microsoft Access 95 ODBC driver distributed with Microsoft Visual C++ 4.x and Microsoft Office 95 (version 3.00.xxxx) ignores the MaxBufferSize setting in the ODBC data source. Instead, it uses the Jet engine's default setting, which is determined based on the amount of physical RAM on the computer using the following formula: 

   ((Total Physical RAM in MB - 12 MB) / 4) + 512 KB
				

For a computer with 32 MB of RAM: 

   ((32 MB - 12 MB / 4) + 512 KB = 5632 KB
				

So when you use this driver, the MaxBufferSize setting is actually well over the default 512 KB in the data source. 

The Microsoft Access ODBC driver distributed with Visual C++ 5.0 and Microsoft Office 97 (version 3.50.xxxx) correctly reads and uses the 512 KB default setting from the ODBC data source. This means that the 3.50.xxxx driver is going to use a considerably smaller buffer than the 3.00.xxxx driver when used with the default settings. The impact on performance shows up after the driver has used up the 512 KB of memory and starts a cleanup thread to flush out the oldest buffer pages. If you are submitting a large amount of database requests (constantly inserting records, running queries or leaving open a large number of recordsets), the cleanup thread will eventually not manage to keep up with the amount of page swapping that has to happen. As a result, Jet begins to react more slowly to additional requests. 

To change the MaxBufferSize setting manually, double-click your Access database DSN in the ODBC Administrator. In the data source setup dialog box, click the "Options>>>" button to display the "Buffer Size" edit box and type in the new desired MaxBufferSize value there.

Back to the top  |   Give Feedback

REFERENCES

For additional information on Jet performance settings such as MaxBufferSize, see Chapter 13 of the Microsoft Jet Database Engine Programmer's Guide (Microsoft Press). 

For information on setting the MaxBufferSize setting programmatically, search for the "SetOption Method" in the DAO SDK online help in Visual Studio 97.

Back to the top  |   Give Feedback