Skip to main content

Optimizing Microsoft SQL Server on the Galaxy Repository Node

LEGACY TECH NOTE #
826

SUMMARY

This This Tech Note Note describes configuration points for optimizing Microsoft SQL Server performance and management when it is installed on the Galaxy Repository (GR) Node.

SITUATION

Application Versions

  • Wonderware Application Server 3.1 SP3 P1 and later
  • Microsoft SQL Server 2005 and later

Anti-Virus Exclusions

Ensure the Exclusions list includes includes Windows Temp Temp files.

For example, you want to set up the following (this example uses McAfee):

  • In the VirusScan Console, click click Task, then then On-Access Scanner Properties
  • Click Click All Processes  and click the the Exclusion Exclusion tab.
  • Click the the Exclusions Exclusions button, then click click Add.
  • Add the the C:\windows\temp\  directory (Figure 1 below)
  • Program Files\ArchestrA\Framework\Bin\CheckPointer
  • Program Files\ArchestrA\Framework\Bin\GalaxyData
  • Program Files\ArchestrA\Framework\Bin\GlobalDataCache
  • Program Files\ArchestrA\Framework\Bin\Cache
  • Documents and Settings\All Users\ApplicationData\ArchestrA (default setting, specified on WinPlatform editor's General page, History Store Forward Directory option)


    Figure 1: Windows\Temp\ Location in the Exclusion Item List

General Settings

SQL Server Processors

  • Uncheck Processor Processor 0  – The IDE/aaGR will run best when SQL Server is not using this Processor.


    Figure 2: Uncheck CPU "0"

SQL Server Memory Settings

  • By default, the SQL Server installation will set the RAM utilization to a very large number (2 Terabytes).
  • SQL may give memory back to a process if requested.
  • It is highly recommended to clamp the SQL Server maximum memory to at least 70% of the maximum available RAM (Figure 3 below).RAM.


    Figure 3: Set Maximum Memory Usage

General SQL Server Database Settings

  • Change the Default index fill factor from from 0 0 to to 80.


Figure 4: Database Settings -> Default Index Fill Factor

System Database Settings

Master Database Settings

  • Verify the configuration of the database files.


Figure 5: Master and MastLog DB File Configuration Settings

  • Modify the database files to minimize fragmentation.
  • Change the initial file size to to 100MB 100MB for both database and transaction log
  • Increase the file growth to a fixed size such as as 200MB (Figure 6 below):.


Figure 6: Autogrowth Configuration

For example:

  • If you have a database that is 50 MB in original size, enabling file growth by 10% will grow the database file increments of 5MB each time.

    If your resulting database is 400MB, you then have the database file partitioned many times.
  • If instead you enable the database growth by size, you will have a resulting database file that is partitioned only 4 times in this case.

TempDB

  • Configure the same settings for the the tempdb  (IMPORTANT).


Figure 7: Tempdb Settings

  • Ensure the Recovery mode is set to to Simple.
  • (Optional) Move the tempDB path to a different physical hard disk 

    USE master
    
GO
    
ALTER DATABASE tempdb
    
MODIFY FILE (NAME = tempdev, FILENAME = ‘D:TempDBtempdb.mdf’)
    
GO
    
ALTER DATABASE tempdb
    
MODIFY FILE (NAME = templog, FILENAME = ‘D:TempDBtemplog.ldf’)
    
GO

    Galaxy Database Settings

    For your database (Galaxy database, example: PT_Master):

    • Configure the initial size to to 500MB 500MB for both MDF and LDF.
    • Configure the autogrowth to to 500MB  for both MDF and LDF. This helps reduce fragmentation.


      Figure 8: Autogrowth Setting for Galaxy DB


    Figure 9: Galaxy Database and Galaxy_log DB

    • Ensure recovery mode is set to to Simple Simple and stop/restart SQLServer.

      Proxy Polling Settings

      The following setting determines how often to refresh the IDE Galaxy Tree.

      We do not recommend changing unless a big operation is going to occur like importing thousands of objects or migrating. The setting should be reset when finished, otherwise when you perform operations the tree will not display the correct state, such as as Checked in.

      • Change the setting in the registry (as follows), to 10 seconds


      Windows Registry Editor Version 5.00
      


      [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ArchestrA\Framework]
      
"ProxyPollingRate"=dword:00002710 (10000)
      • Set it to normal:

        Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ArchestrA\Framework]
        
"ProxyPollingRate"=dword:000001f4 (500)

      Maintenance Plan

      Create a Maintenance Plan in the Object explorer under Management. Run this task weekly to keep the database running smoothly.

      The following graphics show recommended settings for the SQL Server Maintenance Plan.


      Figure 10: Create Maintenance Plan


      Figure 11: Maintenance Plan Wizard: Select Tasks


      Figure 12: Maintenance Task Order


      Figure 13: Select DB for Maintenance


      Figure 14: Configure Shrink Tasks


      Figure 15: Configure Rebuild Index Task


      Figure 16: Define Update Statistics Tasks


      Figure 17: Define History Cleanup Task


      Figure 18: Complete Task Definintion

      When you complete the Task Definintion, the Maintenance Plan appears in the Jobs folder of the SQL Server Agent (Figure 19 below).


      Figure 19: SQL Server Agent Job

      1. Right-click the Maintenance Plan and click Properties, then Schedules/New.


        Figure 20: Create a New Job Schedule
      2. Set up the schedule according to your needs.


      Figure 21: Job Schedule Configuration

      Back to top