Reindexing LANDESK Databases

Version 21

    Verified Product Versions

    Endpoint Manager 9.6Endpoint Manager 2016.xEndpoint Manager 2017.x

    Database Indexing

     

    Performing routine database maintenance such as reindexing is important for keeping the LANDESK database running at peak performance. When information is entered or modified in the database tables, indexes can become fragmented. Re-indexing is best done during a maintenance window.

    Index fragmentation can happen when the logical sequence of pages is disrupted or when an index page contains less than its maximum amount of data, creating a gap in the data page or index. As indexes become fragmented, data reads become inefficient when accessing tables and slow database performance.

    For specific recommendations for tuning LDMS and MSSQL for large enterprise environments please visit DOC-39315.

    Doc 39315 also references actions such as backing up and updating statistics.

     

    SQL Script

    For current versions of SQL (2005 and above) there is an attached script in SQL_Logic_Based_Indexing-2018.txt that allows for a logical re-organization / rebuild action. Users can use the SQL Server Maintenance Plan Wizard to create their indexing plan or can use this script can be used instead. Its common practice for DBAs to take T-SQL and create SQL Server Agent jobs using script contents and then referring to the agent job when building their maintenance plan. This script offers more scalability in reorganizing less fragmented indexes, ignoring slightly fragmented indexes and rebuilding highly fragmented indexes. This would be useful when downtime is limited and the above reindex isnt meeting those needs. The only modification required is database name on the first line. There are other variables that can be tuned based on the DBA's needs.

     

    For assistance creating maintenance plans or sql agent jobs please refer to Microsoft's documentation.

     

    Some DBAs prefer to set the database to SINGLE USER MODE before reindexing the DB.  This isnt required however note that SQL Standard Edition will not keep indexes online when rebuilding them; so indexes will not be used for actions performed on those tables at the time of rebuild. Also consider if there will be resource contention at the time of your database maintenance.

     

    To set a database to single-user mode in SQL 2005-2014

     

    1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
    2. Right-click the database to change, and then click Properties.
    3. In the Database Properties dialog box, click the Options page.
    4. From the Restrict Access option, select Single.
    5. If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, click Yes.

     

    SQL Logic Based Indexing 2018

    In the SQL Logic Based Indexing 2018.txt file attached to this article is a more robust script that provides some decision making and allows the DBA to make some choices about thresholds.

     

    In the script you must select your DB using the following statement, simply replace <databasename> with the name of your Ivanti Endpoint Manager Database:

    USE <databasename>

     

    Next, the basic threshold to reorganize or rebuild an index is 10% and can be changed in this line of the script, 10.0 being the percentage level, change it to fit your needs or leave it as is. Indices below 10% fragementation (Or whatever you choose) will not be affected:

    WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

     

    Next, Choose your threshold for deciding between reorganizing or rebuilding indices with the following line. Anything below the threshold but above the basic fragmentation threshold will be reorganized, anything above this threshold will be rebuilt in the example below 30% is the decision threshold:

    -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
    IF @frag < 30.0
    SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
    IF @frag >= 30.0
    SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

     

    And lastly, Set your DB back to multi user, edit the following line to include your Ivanti endpoint Manager DB name where <databasename>:

    ALTER DATABASE <databasename>

     

    If your database is not set to single user mode this section will just complete and move on.

     

    NOTE:

     

    Stop LANDESK Management Suite/Ivanti Endpoint Manager Specific processes so they will not connect to the database, refer to this document: https://community.ivanti.com/docs/DOC-2465