Exact Globe Performance issue when displaying the journals

Are you getting complaints from users, that opening the General Journal (especially with date range) will take time to display to their PC, even if your server is not at peak time? Yes I do. During non-peak hour it will take around 15-20 seconds to display the content of the journals (see below) and during peak time it will consume almost a minute (Terrible.!!!!)

Every time I heard such performance problem and time is short, first to come into my mind is to add more memory, more CPU, bigger and better disk subsystem and so on. But doubling your horsepower is not a guarantee to double the performance of your application. You might end up throwing a lot of money.

But Alas.!!!!! from 15 seconds, it went down to 2 seconds (Terrible fast..!!!). Thanks to DTA (Database Engine Tuning Advisor of MS SQL). to know more about DTA, see books online.

Here is the process :

• Create workload for analysis ( how to )
• Open SQL Server Profiler and connect to your server
• On the Event Selection tab of Trace Properties, click Column Filters and go to 'NTUsername' and under 'Like' type your user name. This is optional, but I did this because I want to record only my session. Click OK and Run buttons.
• Now you are ready to create a workload
• Go back to Exact Globe, under finance tab, click General Journal (be sure you have a date range defined earlier) and wait until it will display all the journals.
• Once displayed, go back to SQL Server Profile session and you will see something like below

                 

• Stop the trace and Save the workload.
• Open DTA (Database Engine Tuning Advisor) and connect to the SQL server
• Locate the file (workload you created) under "Workload/File", select also the "Database for workload analysis" and under the "Select databases and tables to tune" I select the database level (it means it will tune all the affected tables)

• on the "Tuning Option" tab I only want to consider additional Indexes, under PDS to use in database. 
• I do not want the DTA to provide me any recommendation on partitioning, since I'm not using any partitioning.
• Clicking the keep all the existing PDS radio button tells the PDA to keep the existing structures.
• I also unchecked the "Limit Tuning Time" to remove time limit while tuning.

• If it is ready, click "Start Analysis" button on the upper menu and you will see the "Progress" tab.

• After couple of minutes you will see the "Recommendation Tab", take a look on the "Estimated Improvement". Mine is showing 95% increase in performance. Wow.!!! Superb. 

• I can't wait to implement the recommendation. Let's do it. You have two ways to implement the recommendation.

• if you click "Apply Recommendation", you will be asked to "Apply Now" means immediately DTA will run the SQL script or you can schedule the recommendation to run at your convenient time.
• if you click "Save Recommendation", you can save it and execute later in SQL Analyzer window.

This task is little burdensome but you will be an instant hero for improving the performance of your Globe application. Happy Tuning.!!!!

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