Microsoft SQL Server 2000 dynamically acquire and free memory as needed. When you run multiple instances of SQL Server on a computer, each instance dynamically acquires and frees memory to adjust for changes in the workload of the instance.
SQL Server 2000 Enterprise Edition introduces support for the use of Microsoft Windows 2000 Address Windowing Extensions (AWE) to address approximately 8 GB of memory for instances that run on Microsoft Windows 2000 Advanced Server, and approximately 32 GB for instances that run on Microsoft Windows 2000 Datacenter. With AWE, SQL Server can reserve memory that is not in use for other applications and the operating system. Each instance that uses this memory; however, must statically allocate the memory it needs. SQL Server can only use this AWE allocated memory for the data cache and not for executables, drivers, DLLs, and so forth.
Note: To use Address Windowing Extensions (AWE) memory, you must run the SQL Server 2000 database engine under a Windows account that has been assigned the Windows lock pages in memory administrative credentials.
Support for Windows 2000 Advanced Server or Windows 2000 Datacenter or Windows Server 2003
The maximum amount of memory that can be supported on Windows Server 2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32 GB of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB of physical RAM by using the Physical Address Extensions (PAE) feature. You can use the 3 GB switch that is in the Boot.ini file with Microsoft Windows Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with Microsoft Windows Server 2003 Datacenter Edition.
SQL Server 2000
Both SQL Server 2000 Enterprise and SQL Server 2000 Developer Editions can use the following options:
Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL Server allows SQL Server 2000 to utilize more than 4 GB memory. Without the /PAE switch SQL Server can only utilize up to 3 GB of memory.
When you allocate SQL Server AWE memory on a 32 GB system, Windows 2000 may require at least 1 GB memory to manage AWE.
Example:
The following example shows how to enable AWE and configure a limit of 6 GB for the max server memory option:
1.Take backup of all user and system database on a separate machine (take full backup of database machine )
2.Stop SQL server (all SQL server 2000 services)
3. Add /3GB / PAE switch in your system boot.ini
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /3GB /PAE
4.Shutdown SQL server machine
5.Add physical memory and boot the server
6.Open SQL query analyzer and select master database and execute following script
Use master
sp_configure 'show advanced options', 1
RECONFIGURE GO
sp_configure 'awe enabled', 1
RECONFIGURE GO
sp_configure 'max server memory', 6144
RECONFIGURE GO
After execution of script restart database server.
Note: make sure that /PAE /3GB switch must be in boot.ini file
And database machine have windows 2003 enterprise edition
Note: on some system window task manager will not show you correct memory usage for sql server, to find correct values you need to check Two Performance monitor counter values SQL server: memory manager -> target server memory and SQL server: memory manager -> total server memory
FOR SQL SERVER 2005:
just add /PAE /3GB switch in boot.ini
and apply following changes in sql server properties
No comments:
Post a Comment