Exact Globe - Database Maintenance Plan (Rebuilding Indexes and Updating Statistics)

Database fragmentation is a hindrance to SQL Server performance because with excessive fragmentation more data pages need to be read in order to fulfill a query request.  Fortunately, fragmentation is a manageable problem that can be resolved by re-building the indexes to reduce the fragmentation at the index or table level.

Like in Exact Globe, when any data modification operations like (Insert, Update, or Delete statements) table or index fragmentation can occur. To address this fragmentation issue, you need to have a month / bi- monthly maintenance plan of your Database. In our e-Globe application, I have detected the four most used tables with million records, they are (GBKMUT, AMUTAK, AMUTAS, BANKTRANSACTIONS).

• To check if the indexes of the SQL tables are fragmented you can use the Query Analyzer      and the following query (for example on GBKMUT):

     Use [777]

     Go

     DBCC showcontig ('GBKMUT') with Fast, Tableresults, All_indexes, No_infomsgs

      OR

     SELECT CAST(DB_NAME(database_id) AS Varchar(15)) AS 'Databasename',

     CAST(OBJECT_NAME([OBJECT_ID]) AS Varchar(35)) AS 'Tablename',

     CAST(index_type_desc AS Varchar(30)) AS 'IndexType,

     avg_fragmentation_in_percent AS 'LogicalFragmentation'

     FROM 

     sys.dm_db_index_physical_stats (DB_ID('777'),object_id('GBKMUT'),null,null,null )

Note: the 2nd command is highly recommended, since DBCC commands will be deprecated in a future version of SQL server.  Check the "Logical Fragmentation", the percentage should be as close to 0 as possible, and any value over 10 percent indicates external fragmentation.

How to create Maintenance Plan:

• Right mouse click on the Maintenance Plans option and select New Maintenance Plan

                                                

• On New Maintenance Plan… you’ll be asked to enter a name
• With the use of the Toolbox you can drag Maintenance Plan Tasks to the Design tab.

                                   

• Drag the Rebuild Index Task to Design tab

                                   

• Double click on the Rebuild Index Task in the Design tab to adjust the settings

                                   

           * On Database - select your e-Globe DB

           * On Object - select Table

           * On Selection - put tick mark on tables (ex: amutak, amutas, gbkmut, etc)

           * Click OK


I'm taking the opportunity of this maintenance plan to update all the statistics of all the tables.

• Drag the Update Statistics Task to Design tab

           

                               

•  Double Click on the Update Statistics Task in the Design tab and select the appropriated database(s).  Select the option All existing statistics at the Update: option and Full scan at the Scan Type: option.

                                 

• Click on the OK button to confirm. The Optimize Maintenance Task is now finished

                                 

                     * Connect the arrows according to your plan

                                * Green - success

                                * Red - failure

• You can also configure the Maintenance Plan to run on schedule by clicking Subplan Schedule

                                

If your Globe SQL Server Database is under heavy loads, index fragmentation is an important factor to monitor. With the help of the maintenance plan to rebuild your indexes and update the statistics you are assured for an optimum performance of your Globe database(s).

Asset Module : Asset Depreciation Schedule Listing

Its budgeting time...!!! I was asked by our Dubai Finance Manager, if the e-Globe can produce asset depreciation schedule per month, group by cost center and asset group. My answer is No. Although in Asset Module, (through Asset maintain, then open one asset, then click 'forecast button') it can give you depreciation schedule by providing all the informations needed, but you need to open all your assets one-by-one then repeat the same process, which is very burdensome.

The Solution :

    Exact Globe Add-ins in Excel.

The Process :

** I created an SQL view to  format the columns needed for asset depreciation schedule

CREATE VIEW [dbo].[zUVw_Asset_DepreciationSchedule_2011_2012]

AS

SELECT TOP (100) PERCENT dbo.gbkmut.kstplcode AS CostCenter, dbo.gbkmut.bkjrcode AS Year, dbo.gbkmut.facode AS AssetCode, 

dbo.ItemNumbers.Description AS AssetDescription, dbo.ItemNumbers.AssetGroup AS AssetGroup, SUM(dbo.gbkmut.bdr_hfl) AS Total, 

SUM(CASE WHEN periode = 1 THEN bdr_hfl ELSE 0 END) AS January, SUM(CASE WHEN periode = 2 THEN bdr_hfl ELSE 0 END) AS February, 

SUM(CASE WHEN periode = 3 THEN bdr_hfl ELSE 0 END) AS March, SUM(CASE WHEN periode = 4 THEN bdr_hfl ELSE 0 END) AS April, 

SUM(CASE WHEN periode = 5 THEN bdr_hfl ELSE 0 END) AS May, SUM(CASE WHEN periode = 6 THEN bdr_hfl ELSE 0 END) AS June, 

SUM(CASE WHEN periode = 7 THEN bdr_hfl ELSE 0 END) AS July, SUM(CASE WHEN periode = 8 THEN bdr_hfl ELSE 0 END) AS August, 

SUM(CASE WHEN periode = 9 THEN bdr_hfl ELSE 0 END) AS September, SUM(CASE WHEN periode = 10 THEN bdr_hfl ELSE 0 END) AS October, 

SUM(CASE WHEN periode = 11 THEN bdr_hfl ELSE 0 END) AS November, SUM(CASE WHEN periode = 12 THEN bdr_hfl ELSE 0 END) AS December

FROM dbo.gbkmut INNER JOIN dbo.grtbk ON RTRIM(LTRIM(dbo.grtbk.reknr)) = RTRIM(LTRIM(dbo.gbkmut.reknr)) 

INNER JOIN dbo.ItemNumbers ON RTRIM(LTRIM(dbo.ItemNumbers.Number)) = RTRIM(LTRIM(dbo.gbkmut.facode))

WHERE(dbo.gbkmut.transtype = 'B') AND (dbo.gbkmut.transsubtype = 'V') AND (dbo.gbkmut.bkjrcode = 2011) AND (dbo.gbkmut.periode IN (10, 11, 12))

AND (dbo.grtbk.bal_vw = 'W') OR (dbo.gbkmut.transtype = 'B') AND (dbo.gbkmut.transsubtype = 'V') 

AND (dbo.gbkmut.bkjrcode = 2012) AND (dbo.gbkmut.periode BETWEEN 1 AND 9) AND (dbo.grtbk.bal_vw = 'W')

GROUP BY dbo.gbkmut.bkjrcode, dbo.gbkmut.facode, dbo.gbkmut.kstplcode, dbo.ItemNumbers.Description, dbo.ItemNumbers.AssetGroup

Note: the financial year is from Oct. (current year) To Sept. (next year)

** This second SQL script is for retrieving the data from SQL View via Exact Globe Excel Add-ins

SELECT  CostCenter, Year, AssetCode, AssetDescription, AssetGroup, Total, 

January, February,  March,  April, May, June, July, August, September, October, November, December

FROM dbo.zUVw_Asset_DepreciationSchedule_2011_2012

Where AssetCode = ?1

ORDER BY AssetCode, Year, CostCenter

Note: ?1 will be the Parameter for Exact Globe Excel Add-ins

 ** How to use Exact Globe Excel Add-ins

• Open Excel and go to Exact globe menu, select 'Reports', then select your company

• On Reports Screen, under Generate, scroll down and choice 'Select'

• Write the second script here

Note: if you want to use the Parameter field here or on the Excel row (see sample below)

That's all Folks..!!!!

Exact Globe : The following data doesn't exist: Financial Entry

Today July 14, we encountered an error in Exact Globe during passing of simple financial entry (see below)

I tried looking the error message from customer portal, but I could not find any documents related to this problem. So, trial and error starts.

Error Message :

          The following data doesn't exist: Financial Entry

Observation:

     Upon checking on our Journal settings ( \System \Finance \Journals) select and open 1 journal, under General Tab, I saw a field "variable exchange rate" with tick mark. So, I go then to my currency and put the tick mark on "variable exchange rate". Alas..!!! it works, error is gone. (note: I was puzzled why this tick mark is required, since during passing of entries, I did not change the rate or the xrate field is diabled)

Solution:

        * go to \System \General \Countries \Currencies

        * select and open the currency used during passing of Journal Entries (in my case, EUR)

        * on General Tab, put tick mark on "variable exchange rate" , then save

        * pass again the same entries from scratch

Sending email for new Item: Inventory Module

Senior Accountants from one of our company investments told me that they want to know or to be alerted if an item is added in Inventory Module by Logistics Department automatically. Sad to say, this cannot be addressed by the system itself.

The problem :

     Adding item in Inventory will automatically select the first item group in (Item Card Finance Tab);. in most cases, the “miscellaneous group”. Logistics Staff normally doesn't care about this “tab”, because they don’t know the GL accounts to use for the item, unless told by the Accounting Department.

     When they (Logistics Staff) add a new item, they just fill-in the necessary information on Basic, Purchase/Sales and Inventory Tabs, then click Save. In most cases, the Finance Tab is left out. Sometimes, they forgot to phone Accounting Dept to inform of the new item and when there are fulfillments of the item, the effect in GL Accounts is wrong. See example below

New Item : Lipton Tea

Correct Item group

Item Group : Beans and Teas

Revenue Account : 4130 – Beans and Teas Sales

COGS Account : 5210 – Cost of Beans and Teas

Stock Account : 1710 – Inventory Beverages

System default group

Item Group : Small Wares or Miscellaneous

Revenue Account : 4100 – Miscellaneous Sales

COGS Account : 5200 – Cost of Miscellaneous

Stock Account : 1700 – Inventory Miscellaneous

The Solutions:

* Create SQL Trigger

* Set-up SQL Database Mail

* The SQL Script for the Trigger

USE [111]

GO

/****** Object:  Trigger [dbo].[Trg_ItemInsert_SendMail]    Script Date: 06/15/2011 12:07:59 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE trigger [dbo].[Trg_ItemInsert_SendMail] on [dbo].[Items]

--Instead of Insert

After Insert

As

Declare @ItemCode nchar(30), @ItemDesc nchar(60), @DBname nvarchar(50)

Declare @SysCreated nchar(30), @SysCreator int, @CreatorName nchar(50)

Declare @SubjectMsg nvarchar(100), @BodyMsg nchar(3000)

--

select @ItemCode = ItemCode from Inserted

select @ItemDesc = Description_0 from Inserted

select @SysCreated = convert(nchar(30),Syscreated) from Inserted

select @SysCreator = Syscreator from Inserted

select @CreatorName= ltrim(rtrim(first_name)) + ' ' + ltrim(rtrim(sur_name))

 from Humres where res_id = @syscreator

--variable

set @DBname = (select db_name())

Set @SubjectMsg = 'New Item is Created by - ' + @CreatorName

Set @BodyMsg =          'Dear Accounting,

            Please check the GL inventory account used by this new Item. Thank you.

                        Item Code : ' + @Itemcode +

                        'Item Description : ' + @ItemDesc

If @dbname = '111'

            Begin

            EXEC msdb.dbo.sp_send_dbmail

                        @profile_name = 'e-Globe Administrator',

                        @recipients = 'AcctgStaff1@yahoo.com;AcctgStaff2@yahoo.com',           

                        @copy_recipients = 'LogisticsStaff@yahoo.com',

                        @blind_copy_recipients = 'anthony@yahoo.com',

                        @subject = @SubjectMsg,

                        @body = @BodyMsg

            End

else

            Return

* Configuring Database Mail 

   How to: http://www.mssqltips.com/tip.asp?tip=1438

Here, whenever a new item is added by the Logistics Staff, a trigger will fire and will send an email to the Accounting Staff informing them that a new item is being added. The Accounting Staff will then edit the Item Card Finance Tab. Please note that the profile name used above should exist in the Database Mail.

Boosting your Exact Globe / Synergy database server thru Windows Server Settings.

Configuring your Windows server is easy especially if it is a stand-alone (not a domain) or a dedicated server for application like Exact globe. Like SQL Server, Windows Server is mostly self-tuning, but again there are things we can do in order your SQL database server (as a whole) will perform better.

*Selecting the best performing editions of Operating System. While SQL Server can run on standard editions of Windows Server 32-bit, still consider using Enterprise edition or Data Center edition to take advantage of higher memory capability, multiple processors support etc. See chart comparison below

For Windows Server 2003 http://www.microsoft.com/hk/server/windowsserver2003/evaluation/features/default.mspx

For Windows Server 2008 http://www.directionsonmicrosoft.com/sample/DOMIS/update/2008/02feb/0208ws2plp_ch.htm

*Set the processor scheduling to “background services”. By default after Windows server installation it is set to “programs”. By choosing “Background services” it tells the Operating System to favor background applications, such as SQL Server to prioritize in using the server processor, over foreground applications.

To change under Window Server 2003. Open control panel, click system and under the “Advanced” tab, click on the “Setting” button under “Performance,” the click on the “Advanced” tab.

*Set the memory usage to “Programs”. Also on the same tab (Advanced), change the memory allocation to favor “Programs” over “System cache.” Selecting “Programs” will tell the Operating System to give more memory to applications, such as SQL Server, rather than to the system cache. SQL Server loves memoryJ, the more you have, SQL Server loves it.

                                      

*Paging File Size. Also on the same tab (Advanced) under virtual memory, click on the “change button”, set the initial size (MB) and the maximum size (MB) to an equal value. The minimal initial size needs to be equal or greater than the recommended size. But if you are running SQL Full-Text Search service, it is recommended that the PAGEFILE.SYS file is three times of the physical RAM.

                                     

*File and Printer Sharing for Microsoft Networks. If the Maximize data throughput for network applications network connection option is selected, the Operating System gives priority to applications like SQL Server to perform buffered I/O operations by caching their I/O pages in file system cache.

                              

*Network Card Link Speed. Make sure it is set to the correct value. Select 100Mbps/Full duplex if your HUB or SWITCH supports this Link Speed & Duplex.

                              

 

If you want better performance out of your SQL Server, don’t install any server components (with the exception of necessary utilities), such as Domain, Exchange, DNS, DHCP, etc on your SQL Server machine as  these will cause extra overhead. The goal is to dedicate all of the server’s power to SQL Server. Once you are done, don’t forget to restart your server.

Related Blog :
      * Boosting your Exact Globe / Synergy database server thru SQL Server Settings 
       http://anthonyexactglobe.blogspot.com/2011/06/boosting-your-exact-globe-synergy.html
     * 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 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

Boosting your Exact Globe performance without upgrading

In the recent Exact Globe product update, system performance is improved by optimizing the database indices. On Batch 397 update, a new tool has been implemented to improve performance by adding indices to your database, because a good index structure enables the SQL Server database engine to retrieve requested data by the Globe application in an efficient way.

Note: you need to execute the tool then, but starting from batch 400, these indices are added automatically to the current standard index structure when you update the database to run on Exact Globe product 400.

However, for some reasons you can not update to this new release immediately. Like for instance, your have a customized solution that needs to be changed inorder to be compatible with the new Exact Globe release or you are already accustomed with the current installed version (I know one company in this situation) or you are just afraid to update because the data might get lost. But still you can make use of these new indexes in your current installation.

First of all, this is only available if your Exact Globe database runs on SQL 2005, SQL 2008 or SQL 2008 R2, because the deployment depends on the SQL Dynamic Management Views (DMVs), which is only available from SQL 2005 onwards.

To create these indexes with the tool: (EPTIndex.exe)

• Download the latest version of the EPTIndex.exe tool
• Copy EPTIndex.exe to the BIN Folder of your Exact Globe installation folder.
• Download the latest version of the PerformanceTuningIndices.xml file. Put your mouse on the filename:  PerformanceTuningIndices.xml and right click, Use Save Target As. Save the file in the XML folder of your Exact Globe installation folder. (If you double click on the filename, the XML will be opened instead of downloaded)
• Browse to the BIN folder of your Globe installation and double click on the EPTIndex.exe to start to the EPTIndex tool
• At the Connection section enter the correct name of the Server and Database name. (Make sure that you connect as a user with the System Role SysAdmin within SQL)
• Press the Preview button to get a list of possible indices which can be implemented
• Press the Generate all button to implement all suggested indices.

Scheduling the EPTIndex tool:

You can schedule this tool to run once a month via Windows Scheduler Task or SQL job to check if there are indices which need to be implemented. When you start the tool with parameters shown below, the Generate all process is automatically started to implement the suggested indices.

• Parameters: -r[DatabaseServerName] -D[DatabaseName]
• Example: "C:\Program Files\Exact Software\Bin\EPTIndex.exe" -rGlobeSVR -D111

Run Exact Globe, do some cards retrieval.  By this time you should encounter noticeable speed then. If you are not satisfied yet, read my other blog.
      * Boosting your Exact Globe / Synergy database server thru SQL Server Settings  
       http://anthonyexactglobe.blogspot.com/2011/06/boosting-your-exact-globe-synergy.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