| Author |
Topic |
|
Kali
Starting Member
14 Posts |
 Posted - 2004-10-14 : 14:40:31
|
| Hi,Is there any good hard threshold ( like 95 %) for Memory usage high (pages in memory) counter for the production server?Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kali
Starting Member
14 Posts |
Posted - 2004-10-14 : 15:20:33
|
| thanks, This is a good link.I'm a little confused though, reading the following:"Extended Memory SupportGenerally speaking, because SQL Server 2000 dynamically acquires and frees memory as needed, it is not usually necessary for an administrator to specify how much memory should be allocated to SQL Server. However, SQL Server 2000 Enterprise Edition and SQL Server 2000 Developer Edition introduce support for using Microsoft Windows 2000 Address Windowing Extensions (AWE). This enables SQL Server 2000 to address significantly more memory (approximate maximum of 8 GB for Windows 2000 Advanced Server and 64 GB for Windows 2000 Datacenter Server). When extended memory is configured, each instance accessing the extended memory must be configured to statically allocate the memory it will use.Note: This feature is available only if you are running Windows 2000 Advanced Server or Windows 2000 Datacenter Server."Does that mean that if I'm running W2K Server with SQL 2000 Enterprise, I am not takig advantage of up to 8 GB of RAM? Even if I have the max server memory configutes as up to 8GB?Thanks |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-14 : 15:46:08
|
| Yes, you are not able to take advantage of the the 8GB of RAM due to your OS version.Tara |
|
|
|
sify
Starting Member
18 Posts |
Posted - 2004-10-14 : 21:07:29
|
| Tara Correct me if I am wrong....SQL Server is a 32 bit software. So the memory SQL Server can access is 2^32 that will 4GB. But if your server hardware supports 8^GB of RAM, how will the applications take advantage of this additional memory.For that in Windows 2000 Advanced Server and datacenter a new feature called AWE(Address Windowing extension) is introduced(to compete with other os vedors???) where it allows the application to access more than 4GB of memory. Only SQL Server Enterprise version supports it and on Advanced and datacenter windows version, but by default it is turned off and needs to turned on manually modifying the max server memory.Advanced Server: 8GB of RAMData Center: 64 GB of RAM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-14 : 21:12:28
|
| There is a 64-bit version of SQL Server.Tara |
|
|
|
Kali
Starting Member
14 Posts |
Posted - 2004-10-15 : 11:23:54
|
| I am now confused, maybe someone can clarify:from BOL:Standard 32-bit addresses can map a maximum of 4 GB of memory. The standard address spaces of 32-bit Microsoft Windows NT® 4.0 and Windows 2000 processes are therefore limited to 4-GB. By default, 2 GB is reserved for the operating system, and 2 GB is made available to the application. If you specify a /3GB switch in the Boot.ini file of Windows NT Enterprise Edition or Windows 2000 Advanced Server, the operating system reserves only 1 GB of the address space, and the application can access up to 3 GB. For more information about the /3GB switch, see Windows NT Enterprise Edition or Windows 2000 Advanced Server Help.So that's kinda contradicting, telling me that sql is 4 gb capable, but can only access 3 gb if I specify the switch in Boot.ini. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-10-15 : 11:59:22
|
| the memory limitations in windows are definitely confusing. Here is a brief rundown:Standard applications on standard windows can only address a 2Gb memory space. The 4GB available to a 32bit environment is the entire memory space, windows reserves memory for the kernel that is why you cannot address all 4GB with SQL Server standard edition. Using the /3GB switch on the Win2k Advanced and Datacenter edition (combined with SQL Server Enterprise or Developer edition) lets the SQL Server process address up to 3GB of RAM.the /pae switch turns on AWE memory addressing at the OS level. THis only works on Win2k Advanced Server or Datacenter editions. THis allows a 36bit memory address space. This feature is enabled because of capabilities intel built into their CPUs starting with the Pentium Pro. To use AWE memory with SQL 2K, you need to be running the enterprise edition of SQL server and you need to enable the feature 'awe enabled' using sp_configure. CHeck BOL for details.The drawback with using AWE memory is performance. The system is noticeably slower using AWE memory. Also, not everything can be loaded into the AWE memory space, there are some limitations (I think only the buffer cache can go above the 4GB mark). Again, check BOL for details.The last solution for addressing more than 2GB RAM in SQL Server is to use the 64bit edition. The only problem is that it requires 64bit hardware. THe only edition available right now is SQL 2000 64bit for Intel Itanium hardware. THe hardware is about 1/3 more expensive than standard Intel Xeon based hardware. Next year when SQL 2005 is available, it will run on the XEON 64 and AMD Opteron 64bit platform. THese systems are less expensive than the Itanium, and they will run existing 32bit software without modification.btw, with the 64bit edition there are no memory limitations. And it is fast on the Itanium platform.-ec |
|
|
|
Kali
Starting Member
14 Posts |
Posted - 2004-10-15 : 13:31:10
|
| thank you for great explanation! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-16 : 03:07:37
|
| "64bit edition there are no memory limitations"That sounds awfully like "640k will be enough for anyone" and leads to discussion about the huge size of the disks ... and the immense size of the tape backup!Kristen |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-10-16 : 08:31:20
|
quote:
Originally posted by Kristen
"64bit edition there are no memory limitations"That sounds awfully like "640k will be enough for anyone" and leads to discussion about the huge size of the disks ... and the immense size of the tape backup!
To be more clear, the when you use 32bit SQL Server and the AWE extensions you can only load the buffer cache into the upper memory areas. This only benefits the relational database engine, analysis services sees no benefit at all.Also, the procedure cache, log cache, sort area etc. can only be loaded into the lower 4GB area.64bit SQL Server does not have any of these limitations. The large memory space is available to all processes, not just the buffer cache and the relational engine.-ec |
|
|
|
|
Memory usage high threshold