Boosting your Exact Globe / Synergy database server thru SQL Server Settings

SQL Server is mostly self-tuning. But, there are things we can do to help optimize its performance. And every time we help boost the performance of your SQL Server, we are at the same time boosting the performance of your Exact Globe or Synergy then.

Below are the recommended settings for optimum performance of your SQL Server.

* Physical DB location. File access is an essential consideration to an OLTP system like Exact Globe. If your system is busy or you expect it will be in the future, you may consider separating the DB location to prevent waiting time at the disk sub-system.

•  Don’t mix the location of SQL server system DB (master, msdb, temp, model) from your Exact Globe / Synergy DB.
• For best overall performance, locate the database files (.mdf) and log files (.ldf) on separate arrays or disk. To store your Exact Globe / Synergy database files (.mdf), the best performance is storing them on RAID 10 arrays, and then RAID 5 is most likely the next best bet.
• If your database is very large and busy, consider using multiple database data files or file group or you may also consider using DB partitioning (but DB partitioning is tough for Exact Globe / Synergy since the tables and indexes are created during installation)

* Memory Settings. By default, the memory architecture of Windows operating system 32-bit editions is limited to 4GB total physical RAM. 2GB is reserved for the OS and 2GB is reserved for applications like SQL Server. However, by adding switches (/3GB, /PAE) to the Boot.ini, the Enterprise and Data Center editions of Windows 2003, can access more than 4GB of physical memory.  But for Windows 2003 Standard Edition, it is limited to 4GB of RAM and does not support Physical Address Extensions (/PAE).

     - How to configure more than 2 GB memory for SQL Server on X86 (32 bits) version:

• For servers with 4GB of physical memory, add the /3GB switch to the BOOT.INI in order to force the operating system to use only 1 GB, while applications like SQL server can use the remaining 3 GB. Windows Server 2003 Standard, Enterprise and Datacenter editions are supporting this switch.
• For servers with more than 4 GB of physical memory add the /3GB and /PAE switch to the BOOT.INI and enable AWE in SQL Server. Doing this will allows the operating system to access physical memory beyond 4 GB but up to 16GB only. Again Windows Server 2003 Enterprise and Datacenter editions are supporting this switch.
• And for servers with more than 16 GB of physical memory, only add the /PAE switch to the BOOT.INI and enable AWE in SQL Server. Don’t use the /3GB switch because the operating system need more than 1 GB of memory to handle and manage the allocation of 16 GB and above.

    Memory Size        Boot.INI          Enable AWE        Lock Pages in Memory

      < 2 GB                                               x                                       x

      3 to 4 GB               /3GB                     √                                      √

      5 to 16 GB           /3GB /PAE               √                                      √

      > 16 GB                 /PAE                     √                                      √

• Note: In Windows Server 2003 32-bit editions, PAE is automatically enabled only if the server is using hot-plug memory. In this case, you do not have to use the /PAE parameter on a system that is configured to use hot-plug memory. In all other cases, you must use the /PAE parameter in the Boot.ini file to take advantage of memory over 4 GB.

                Example of the BOOT.INI file:
                [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,
                Standard" /3GB /fastdetect /NoExecute=OptOut
                or
                multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 003,
                Enterprise" /3GB /PAE /fastdetect /NoExecute=OptOut

• Operating systems on 64-bit editions can use the available memory in the server without the need of /3GB, /PAE or AWE enabling. You only need to grant Lock Pages in Memory rights to the SQL Server account.
• The Windows policy Lock Pages in Memory option is disabled by default. This privilege must be enabled to configure Address Windowing Extensions (AWE).
• Go to Start \ Run, In the Open box, type gpedit.msc.  The Group Policy dialog box opens. 
• Under Computer configuration \ windows settings \security settings \ local policies
• Select user right assignment folder
• Double click “lock page in memory” – check if the account with privileges to run the sqlserv.exe services is there. If not, then add the account. By default it’s the system account.

                                 

• Note: to check the account used to run the services. Go to Administrative tools \Services \SQL server \Properties \ under tab log on.  

     - How to enable AWE on SQL Server

•  On Server properties of your SQL Server and in the section ‘Server memory options’ put tick mark to enable the option: ‘Use AWE to allocate memory’.

                       

• You can also use the following SQL Commands to enable AWE

          Use master
          Exec sp_configure 'awe enabled', '1'

          Reconfigure

• If your SQL Server is dedicated (means no other windows applications are running except SQL), there is no reason to change the “min server memory” and “max server memory” settings at all. But if you are running other applications on the same server, there is a benefit of changing this setting to a minimum or maximum figure.

* Transaction Isolation Level. By default level 2 will be used to retrieve data. The higher the level the more data is locked during read operation to guarantee consistency. Only level 1 will not block other users during read operations and also you will not be blocked by other users.

• Which levels of data retrieval is used by Exact Globe 2003 and Synergy?  Within Exact Globe and Synergy, the runtime always connects to the database with level 1. This means that read operations never will be blocked by others users who are also reading data with level 1.
• Which levels of data retrieval is used by external applications? External applications like Crystal Reports or Excel connects to the database with the default level 2. Therefore it is important to configure the level of retrieving data for external applications to level 1. So start altering your SQL Store Procedures and issue something like the following (in red).

Set Transaction Isolation Level READ UNCOMMITTED

GO

Begin Transaction

Select * From …..

Select * From …..

Commit Transaction

• Which levels of data retrieval is used by SQL query analyzer?  Within SQL server you can retrieve data via a query. The default transaction isolation level of SQL is read committed or level 2. This means that when you execute a query you need to wait until the transaction is completed before any data will be displayed. Setting the option: 'Set Transaction Isolation Level:' to 'Read Uncommitted' eliminates the waiting time and the query is directly executed.

·  On SSMS main menu

·   Go to Tools \ Options \ Query Executions \ SQL Server \ Advanced

·   Set Transaction Isolation Level to : Read Uncommitted

* Although heavy-duty hardware can help SQL Server's performance but it does not always fix SQL Server performance problems. Application and database design and tuning can play a greater part in overall performance than hardware. Once you are done, don’t forget to restart your server.

Related blog :
     * Boosting your Exact Globe performance without upgrading
        http://anthonyexactglobe.blogspot.com/2011/06/boosting-your-exact-globe-performance.html
     * Boosting your Exact Globe / Synergy database server thru Windows Server Settings
        http://anthonyexactglobe.blogspot.com/2011/06/boosting-your-exact-globe-synergy_28.html