Over time, the indexes in a SQL database become fragmented as information is changed, added and deleted from the database. Heavily fragmented indexes can degrade query performance and cause Eco Time to respond slowly. Fragmentation can be remedied by reorganizing or rebuilding the indexes. Please note that these operations can take several minutes to complete depending on the size of the database.
Microsoft recommends that you reorganize the indexes of a table if the fragmentation level is between 5 and 30 percent. This action can be taken while the database is online and have connections to it.
You can reorganize the indexes using SQL Managment Studio:
Browse the Tables in the Eco Time database.
Expand the table you want to reorganise the indexes for and right-click on Indexes.
Click on Reorganize All. A window will open listing all the indexes and the level of fragmentation.
Click on Ok to start the process.
Reorganizing indexes can also be done by running a query. To reorganize all the indexes in a table use the following query:
ALTER INDEX ALL ON Clockings
REORGANIZE;
Rebuilding indexes
If the fragmentation level of an index is above 30% it is recommended that the index is rebuilt. This operation must be done while there are no connections to the database i.e. close all open Eco Time modules before rebuilding indexes.
In SQL Management Studio you can follow the same course of action as with Reorganizing Indexes, but just select Rebuild All.
You can rebuild the index using the following query:
ALTER INDEX ALL ON Clockings
REBUILD WITH (FILLFACTOR = 100, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);
Before rebuilding indexes on a table please check that you have ample disk space - let's say as a rule of thumb at least three times that of the size of the table you are rebuilding.
Example script
You can download and open an example script (Defrag_TNA_Indexes.sql) in SQL Management Studio.
Please note that the database name is specified at the top of the script and that it is assumed that the database name is: Eco Time. Change this if your database is not called Eco Time.
The script contains two sections:
The first section is used to reorganise indexes on the main files and the second section is used to rebuild the indexes on the main files.
The second section is commented out by default. Please uncomment it if you wish the rebuild the indexes and also comment out the reorganise section.
Over time, the indexes in a SQL database become fragmented as information is changed, added and deleted from the database. Heavily fragmented indexes can degrade query performance and cause Eco Time to respond slowly. Fragmentation can be remedied by reorganizing or rebuilding the indexes. Please note that these operations can take several minutes to complete depending on the size of the database.
Microsoft recommends that you reorganize the indexes of a table if the fragmentation level is between 5 and 30 percent. This action can be taken while the database is online and have connections to it.
You can reorganize the indexes using SQL Managment Studio:
Reorganizing indexes can also be done by running a query. To reorganize all the indexes in a table use the following query:
ALTER INDEX ALL ON Clockings
REORGANIZE;
If the fragmentation level of an index is above 30% it is recommended that the index is rebuilt. This operation must be done while there are no connections to the database i.e. close all open Eco Time modules before rebuilding indexes.
In SQL Management Studio you can follow the same course of action as with Reorganizing Indexes, but just select Rebuild All.
You can rebuild the index using the following query:
ALTER INDEX ALL ON Clockings
REBUILD WITH (FILLFACTOR = 100, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);
Before rebuilding indexes on a table please check that you have ample disk space - let's say as a rule of thumb at least three times that of the size of the table you are rebuilding.
You can download and open an example script (Defrag_TNA_Indexes.sql) in SQL Management Studio.
Please note that the database name is specified at the top of the script and that it is assumed that the database name is: Eco Time. Change this if your database is not called Eco Time.
Eco Time Documentation (Copyright © CapeSoft 2022)