Memory Limits for Windows Releases

Saturday, December 10, 2011

When I need to reference the physical memory limits for Windows Operating Systems, I seem to waste time trying to locate my notes or searching the Internet.  I am posting here so I can remember.  Found the information on Microsoft’s site and will use it for future reference.

Below are the Operating Systems and Editions I reference most frequently.

Windows Server 2008 R2

The following table specifies the limits on physical memory for Windows Server 2008 R2. Windows Server 2008 R2 is available only in 64-bit editions.

Version

Limit on X64

Windows Server 2008 R2 Datacenter

2 TB

Windows Server 2008 R2 Enterprise

2 TB

Windows Server 2008 R2 Foundation

8 GB

Windows Server 2008 R2 Standard

32 GB

Windows Server 2003 R2

The following table specifies the limits on physical memory for Windows Server 2003 R2. Limits over 4 GB for 32-bit Windows assume that PAE is enabled.

Version

Limit on X86

Limit on X64

Windows Server 2003 R2 Datacenter Edition

64 GB

1 TB

Windows Server 2003 R2 Enterprise Edition

64 GB

1 TB

Windows Server 2003 R2 Standard Edition

4 GB

32 GB


SYSAUX tablespace growing rapidly

Saturday, December 3, 2011

I have an Oracle 11g R2 (11.2.0.1) database where I noticed the SYSAUX tablespace was growing larger every day.  Searched Oracle My Support and found Doc ID 1292724.1 and Doc ID 552880.1 which were helpful.

After running awrinfo.sql, I found the largest consumer to be SM/OPTSTAT at 2.8 GB which is larger and not typical when compared to my other Oracle 10g R2 and Oracle 11g R1 databases.

********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)  
********************************************************
|                                                       
| Occupant Name        Schema Name               Space Usage
| -------------------- -------------------- ----------------
| SM/OPTSTAT           SYS                        2,726.8 MB
| SM/AWR               SYS                           76.6 MB
| SM/ADVISOR           SYS                            8.2 MB
| LOGMNR               SYSTEM                         7.9 MB
| SM/OTHER             SYS                            6.8 MB
| JOB_SCHEDULER        SYS                            3.6 MB

**********************************
(4) Space usage by non-AWR components (> 500K)
**********************************

COMPONENT        MB SEGMENT_NAME                            SEGMENT_TYPE  
--------- --------- --------------------------------------- --------------
NON_AWR       800.0 SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST      INDEX         
NON_AWR       752.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY        TABLE         
NON_AWR       424.0 SYS.I_WRI$_OPTSTAT_H_ST                 INDEX         
NON_AWR       240.0 SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY       TABLE         
NON_AWR       192.0 SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST       INDEX         
NON_AWR       104.0 SYS.I_WRI$_OPTSTAT_HH_ST                INDEX

Searching Google, I came upon John Hallas’ “Purging statistics from the SYSAUX tablespace” blog entry quite informative and detailed on managing occupants in the SYSAUX tablespace.

As John mentions:

“Whenever statistics in the dictionary are modified, old versions of statistics are saved automatically for future restoring. The old statistics are purged automatically at regular intervals based on the statistics history retention setting and the time of recent statistics gathering performed in the system. Retention is configurable using the ALTER_STATS_HISTORY_RETENTION procedure. The default value is 31 days.”

To help reduce the amount of space caused by historical data, I set the retention of old statistics to 14 days.

exec dbms_stats.alter_stats_history_retention(14);

To verify the new setting, I executed the statement below.

select dbms_stats.get_stats_history_retention from dual;

I proceeded to incrementally purge statistics from 31 days to 14 days.  Started with 28 days, then moved to 21 days.

exec dbms_stats.purge_stats(sysdate-28);

Then I tried 14 days, however I encountered an UNDO space error requiring me to change my increments using 18, 16, and finally 14 to complete the purge task.

Now to reclaim the space used by the objects shown in section 4 of the AWRINFO report, I will eventually follow John Hallas’ steps using the ALTER TABLE MOVE command.


Microsoft SPLA

Thursday, December 1, 2011

As a SaaS provider, we utilize the Microsoft Services Provider License Agreement (SPLA).  Adding this electronic note for future reference.

SPLA “is for organizations that want to offer hosted software and services to end customers, such as Web hosting, hosted applications, messaging, collaboration, and platform infrastructure.

Hosting providers who want to offer software services to their customers and who will include software licenses as part of their service offering should use SPLA. Microsoft SPLA is the only Microsoft Volume Licensing program that allows Microsoft products to be used for commercial hosting.”