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