AWE allow 32-bit operating systems to access large amounts of memory (sql

[Anon Anon]

Hi Guys,  i found an interesting article about sql server 2005(Hope many of  you  have already know).This is to be tested and to be  shared  with their respective DBA's.It is nothing specific to clarity application.i want to share this for the users  who are having performance issues  with sql server 2005 or higher,they can have an look.  Using AWE-mapped Memory with Windows Server 2003Address Windowing Extensions (AWE) allow 32-bit operating systems to access large amounts of memory.SQL Server 2005 supports dynamic allocation of AWE memory on Windows Server 2003. During startup, SQL Server reserves only a small portion of
AWE-mapped memory. As additional AWE-mapped memory is required, the operating system dynamically allocates it to SQL Server. Similarly, if fewer
resources are required, SQL Server can return AWE-mapped memory to the operating system for use by other processes or applications.
 The amount of physical memory supported has increased with the introduction of the Windows Server 2003 family. The physical memory accessible by
AWE depends on which operating system you are using. The following list provides the maximum physical memory accessible by each Windows Server 2003
operating system at the time of writing.  Windows Server 2003, Standard Edition supports physical memory up to 4 GB.
Windows Server 2003, Enterprise Edition supports physical memory up to 32 GB.
Windows Server 2003, Datacenter Edition supports physical memory up to 64 GB.
 1. To make AWE available to an instance of SQL Server 2005, use sp_configure to set the awe enabled option to 1, and then restart SQL Server.
 
2. Set min server memory and max server memory3. Before enabling AWE, you must configure the Lock Pages in Memory policy.  To enable the lock pages in memory option
 1.             On the Start menu, click Run. In the Open box, type gpedit.msc. The Group Policy dialog box opens.2.             On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.3.             Expand Security Settings, and then expand Local Policies.4.             Select the User Rights Assignment folder. The policies will be displayed in the details pane.5.             In the pane, double-click Lock pages in memory.6.             In the Local Security Policy Setting dialog box, click Add.7.             In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.    
 
ExampleThe following example shows how to activate AWE and configure a limit of 1 GB for min server memory and 6 GB for max server memory.
First, configure the Lock Pages in Memory policy for the account used for SQL Server service.Next, configure AWE:  sp_configure 'show advanced options', 1RECONFIGUREGOsp_configure 'awe enabled', 1RECONFIGUREGOAfter SQL Server restarts, the following message is written to the SQL Server error log: "Address Windowing Extensions enabled."Next, configure memory:  sp_configure 'min server memory', 1024RECONFIGUREGOsp_configure 'max server memory', 6144RECONFIGUREGOIn this example, the memory settings direct the buffer pool to dynamically manage AWE-mapped memory between 1 GB and 6 GB. If other applications require
additional memory, SQL Server can release the allocated AWE-mapped memory if it is not needed. In this example, the AWE-mapped memory can only be released
up to 1 GB.Dynamic AWE memory also allows SQL Server to increase memory if additional memory is added to a computer that supports Hot Add Memory. Available in
Windows Server 2003, Enterprise and Datacenter editions, Hot Add Memory allows memory to be added while the computer is running. For example, suppose
SQL Server 2005, running under Windows Server 2003, Enterprise Edition is started on a computer with 16 GB of physical memory. The operating system
is configured to limit applications to 2 GB of virtual memory address space; AWE has been activated on SQL Server. Later, the system administrator
adds 16 GB of memory while the computer is running. SQL Server 2005 immediately recognizes the additional memory, and, if necessary, can
take advantage of it.cheers,sundar  

[Deactivated User]

That is interesting, thank you.Do you experience the db server performance problems?My experience is that the cpu usage % and memory usage are low most of the time on db server.On the other hand the OS on db server can be anything supported by the db and could also be 64 bit and then the memory issue could be worked around.  Too bad Clarity if cannot benefit from it and apparently no difference for the Win32 java vm.  Martti K.

[Anon Anon]

Martti,  This suggestion is provided by our Technical Excellnce Team.They also implemented in other projects(Not clarity as we are using oracle) for which they are using database as sql 2005 (windows 32 bit) found it fruitful.In the Clarity Installation Guide -->Performance-->Analyze the MS Sql server DB -- Maintain Sufficient Memory.CA had given an Note,hope you might have read it.So even though it is not related to app(Using AWE-mapped Memory with Windows Server 2003) it will come under DB improvement activities as this was mainly dealing with Memory.So giving an try(If you have an same os / db version) with the help of your DBA in test env is not an bad idea.  In the year start we had several performance issues with our database.(oracle 9i.),but now managed to overcome successfully.  Database Performance Activities:- (clarity 7.5.2 and oracle 9.0.2.)  1. Set the DB Parameter CURSOR_SHARING = SIMILAR from Exact(Recommended by CA).  2. Data Clean Up Activites:-  â€¢ Clean the temp tables.
• Removed the data from Notifications and calendar Events.
• Removed the unwanted procedures.
• Removed the unnecessary custom triggers.
• Deleted the Old audit data.
• Removed the unnecessary logs.  3.     As Adviced By our   DBA(After implementing now we can see an 20% Improvement in the Extracts Running time. It saved a lot of our Time).  â€¢ Increased log buffer from 160KB to 1MB.
• Increased shared pool from 192MB to 750MB.
• Increased the buffer from 1.5GB to 2.5GB.
• Resized the redo logfile from 50MB to 100MB with mirroring logfile.  4. Created Index on Tables for Performance Hit Pages. (Important Note:- This indexes needs to be handled in upgrade as CA will not support it and there is no need for that in v8.1 and v12 as CA had fixed the issues. )  â€¢ Created an Index for parent_skill_id in rsm_skills and for temp_rsm_rsf_skills need to put an index. (In Dec’2008 there is a Big Performance Issue on accessing and uploading skills,now it is ok.)• Created an Index for id in odf_ca_task as while importing a WBS in project tasks resulted in time out as Full Table scan is running.Now it is ok.
• Created an Index for id in odf_ca_team as while user clicks the project team tab it is slow, reason being it is going for a Full Table scan is running.Now it is ok.• Creates an Index for id in ODF_CA_REQUISITION as when a user enters the details in the Resource Requisitions it is stored in this table which has no Index .It is going for a Full Table scan.• Create an Index on PRASSIGNMENT table for PRSTART, PRFINISH.This will help the timeslice Job to run quickly.  5. CA has included Rule Based Hints in some of the important system views which are used very frequently by the application. As now every thing is cost based in Oracle this in turn has de-graded the performance. Now it is been removed, so the optimizer will choose the best execution plan which will inturn help the performance.  6.Removed all the Global and instance access rights for all Inactive users and the project level rights for In-Active projects via XML.This will reduce the records in the CMN_SEC_ASSGND_OBJ_PERM which is called every 15 seconds for checking permissions.  7.The timeslice records had nearly 7.5 Million records,after re-creating properly it reduced to 3 Million Records.  8. Scheduled Oracle Table Analyze Job -weekly (earlier Monthly),Clean User session Jobs daily (earlier quarterly as we need user info for audit log,that resulted in millions of records in user audit table)     and Remove Job and report Logs (Bi weekly).  9. Database Physical Configuration (CA Recommendation for Oracle)In general Oracle databases should have their Data Files, Redo Log files and Archive Log files on physically separate disk drives. Beyond that, Oracle recommend the use of RAID1+0 (RAID10) disk arrays for the robust storage of these database files.  10. Our database server had 16GB of memory and this isn’t being fully utilised. So the following parameters is checked and sized accordingly to allow the database to make full use of the memory available and allow caching to occur. (Our DBA recommendation)  LOG_BUFFER
SGA_MAX_SIZE
DB_CACHE_SIZE
SHARED_POOL_SIZE  These changes will require a database restart.  Note:-   Allways have an Good DBA and an Better relationship with him........................Cheers,sundar

[Anon Anon]

How did you do the following via XML?

6.Removed all the Global and instance access rights for all Inactive users and the project level rights for In-Active projects via XML.This will reduce the records in the CMN_SEC_ASSGND_OBJ_PERM which is called every 15 seconds for checking permissions.

I'm not aware of the ability to remove instance rights via XML. Do you have an example of how to do this?

[Anon Anon]

Hi,

Pls ise the below sample xml.

<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_user.xsd">

<Header action="write" externalSource="NIKU" objectType="user" version="8.1.3.4545"/>

<Users>

<User externalId=" " userLanguage="English" userLocale="en_GB"

userName="1172507" userStatus="LOCK"

userTimezone="Europe/Brussels" userType="INTERNAL">

<PersonalInformation emailAddress="[e-mail]"

firstName="sundar" lastName="Jayaraman"/>

<Resource resourceId="1172507"/>

<Company/>

<General addedBy="admin" addedDate="2009-02-03"/>

<OBSAssocs completed="true"/>

<Groups completed = "true"/>

<GlobalRights completed = "true"/>

<InstanceRights completed = "true"/>

<InstanceOBSRights completed = "true"/>

</User>

</Users>


cheers,
sundar