May 30, 2002 at 12:58 pm
SQL Server 7.0
NT 4.0 SP5
I had a 1.2 Gb of RAM on my production Server. I added more memory and now I have 4 Gb of RAM. No change in performance. What do I need to configure within SQL to get the advantage of this much memory?
Thank you for any recommendation.
May 30, 2002 at 1:07 pm
From SQL7 BOL
extended memory size Option
The extended memory size option is available only for Microsoft® SQL Server™, Enterprise Edition running under future versions of Microsoft Windows NT®, Enterprise Edition, on Intel® and Alpha platforms. SQL Server utilizes the Enterprise Memory Architecture (EMA) feature that will be available on these platforms in this future version of Windows NT. For more information about configuring your system to enable the EMA feature on a particular system configuration, see your Windows NT documentation.
In addition to the EMA feature support available on future versions of Windows NT, some system vendors may make available products for Windows NT, version 4.0, Enterprise Edition, that SQL Server, Enterprise Edition can use for the extended memory size option. On Intel platforms, SQL Server can use a feature known as PSE36, and on Alpha platforms, the feature is known as VlmCache. For more information about availability, installation, and configuration of these products, see your system vendor.
The option indicates the number of megabytes (MB) of memory to use as an extended cache in addition to the conventional buffer pool. The maximum value to which that extended memory size can be set depends upon the total physical memory available and the amount of physical memory used by SQL Server conventional memory. For example, on a computer with 8 gigabytes (GB) of memory and 2 GB of SQL Server conventional memory, a reasonable value for extended memory size might be in the range of 5000 through 6000. (Windows NT on the Alpha platform supports up to 2 GB of conventional memory usage for SQL Server.) If the same 8 GB system had 3 GB of conventional memory usage for SQL Server, then the option should be set in the 4000 to 5000 range (Windows NT, Enterprise Edition with the 4GT feature supports, on Intel platforms, up to 3 GB of conventional memory usage for SQL Server).
Vendor specific hardware features limit the amount of memory that the EMA feature can support, and therefore that can be used by SQL Server. For Intel platforms with appropriate hardware support, this limit is 64 GB. For Alpha platforms with appropriate hardware support, this limit is 32 GB.
extended memory size is an advanced option. If you use sp_configure system stored procedure to change the setting, you can change extended memory size only when show advanced options is set to 1. The setting takes effect after stopping and restarting the server
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 30, 2002 at 1:18 pm
I think adding /3GB to boot.ini is required.
Andy
July 12, 2002 at 12:06 pm
HELP!
We're runnning SQL Server 7.0 on an dedicated Windows 2000 Advanced Server. For some reason this server NEVER seems to release any memory. No matter what action is performed against the database (VB, Query Analyzer, Enterprise Manager), the Memory Usage for SQLSRVR.exe as shown in the Windows Task Manager is always approximately equal to the Peak Memory Usage.
If we ignore the problem, eventually the server runs out of memory and freezes, necessitating a reboot. For a temporary fix, we've instituted a nightly process which stops and restarts the SQLSRVR.exe to release the memory, but we'd really like to solve the basic problem.
I've read tons of stuff on SQL memory handling, but to be honest I'm not very experienced in database admin, and the amount of material is overwhelming. I'm not sure where I should be concentrating my attention to fix this thing.
Any suggestions?
Dana
Connecticut, USA
Dana
July 12, 2002 at 5:22 pm
What SQL Server service pack are you on?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 13, 2002 at 9:46 pm
SP1
July 13, 2002 at 10:48 pm
I apologize. I was asking the question of DanaH. There are some fixes in the various service packs for memory leaks.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 15, 2002 at 11:41 am
DanaH, what version and service pack is the SQL Server?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 16, 2002 at 4:07 pm
We also had the same problem as DanaH1976 with a sql server 7.0 sp3 running on advanced server 2000 sp2 not releasing memory and locking up the machine. It didn't start until after we loaded the sp2 hotfixes. We finally limited the max amount of memory allocated to SQL. Since then, it runs like a charm.
mbg
mbg
July 16, 2002 at 8:20 pm
Each service pack they fix more and more memory leaks. For instance:
Bulk insert abort memory leak (fixed in SP3):
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q276751
Bulk insert from stored procedure (fixed in SP2):
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q246824
SQL-DMO Parameter By Reference Memory Leak (fixed in SP4):
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q293636
SQL PutData Memory Leak (fixed in SP 4):
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q307540
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 18, 2002 at 3:48 am
Like Danah, Im also having problems with sqlsrvr.exe size. If I reboot, one day later it uses up the 1.3 gb memory that I have allocated it via the max memory slider in enterprise manager and then then goes past this until it uses up all memory. Then certain cluster services start going offline. How come sqlsvr.exe ignores the enterprise manager max memory setting?
July 19, 2002 at 9:36 am
We has similar problems using SQL7 SP3 and NT4 SP6.
We were doing a data load via a VB/ADO program. The SQL Server memory kept dropping until it froze the machine, even though the server max memory option was set.
It turned out to be a faulty RAID controller.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply