CS Time/TNA Documents

username password

Defragmenting your SQL tables

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

     

    CONTENT

     

    Reorganizing indexes

    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:

    1. Browse the Tables in the CS Time database.
    2. Expand the table you want to reorganise the indexes for and right-click on Indexes.

      SQL_Reorganize.jpg
       
    3. Click on Reorganize All.  A window will open listing all the indexes and the level of fragmentation.
    4. 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 CS 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: CS Time.  Change this if your database is not called CS 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.

     

     

    Permalink:  http://tinyurl.com/n3jmfzx

    Page last modified 10:02, 13 Mar 2014 by cay-lynne CS Time/TNA Documents > Advanced > Defragmenting your SQL tables

    Comments0

    You must login to post a comment.
    Attach file

    Files2

    Powered by MindTouch Core