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).