--- title: Tuning Indexes slug: neurons-for-itsm/on-premises-help/enu/2025/tuning-indexes docTags: createdAt: 2026-07-29T16:36:07.225Z --- Perform these steps to re-index the Ivanti Service Manager database: - [Capturing a Database Trace](docId\:NeYAB6PqFSSkNYfLnrUhp) - [Getting Index Tuning Recommendations](docId\:NeYAB6PqFSSkNYfLnrUhp) -  [About Creating New Indexes](docId\:NeYAB6PqFSSkNYfLnrUhp) ### Capturing a Database Trace Use the Microsoft SQL Server Profiler in Microsoft SQL Server Management Studio to capture a database trace. You analyze the information from this trace file in [Getting Index Tuning Recommendations](docId\:NeYAB6PqFSSkNYfLnrUhp). 1. Open Microsoft SQL Server Management Studio by selecting **Start > All Programs > Microsoft SQL Server 2008 > SQL Server Management Studio**. 2. Highlight the server that hosts the Ivanti Service Manager database. 3. From the **Tools** menu, select **SQL Server Profiler**. 4. Start a new trace: - If you are prompted for login credentials, specify them for Ivanti Service Manager and click **Connect**. The system displays the **Trace Properties** window. - If you are not prompted for login credentials, select **File > New Trace** in the **SQL Server Profiler** window. The system prompts you for login credentials. Specify credentials for Ivanti Service Manager and click **Connect**. The system displays the **Trace Properties** window. 5. On the **General** tab, do the following: :::Paragraph{indent="1"} a. In the **Trace name** field, enter a name for the trace. ::: :::Paragraph{indent="1"} b. In the **Use the template** field, select **Standard (default)**. ::: :::Paragraph{indent="1"} c. Check **Save to file** and designate a file location for the trace. ::: *Running a Trace From the Trace Properties Window* ::Image[]{src="https://api.archbee.com/api/optimize/parib0MC3hVoUNdfQdnac/bLdAiPJVJCxSFunGi3p6C_traceproperties.png" position="flex-start" size="80" width="845" height="531" initialPath="PerfTune_Images/TraceProperties.png" githubPath="ITSM-On-Prem-Help/Content/PerformanceTuning/PerfTune_Images/TraceProperties.png" showCaption="false"} 6. Click **Run** to initiate the trace. The system displays trace information while the trace runs. *A Running Trace* ::Image[]{src="https://api.archbee.com/api/optimize/parib0MC3hVoUNdfQdnac/8vkKYxp8Gy8sP1SnQf_jC_tracewindow.png" position="flex-start" size="56" width="559" height="412" initialPath="PerfTune_Images/TraceWindow.png" githubPath="ITSM-On-Prem-Help/Content/PerformanceTuning/PerfTune_Images/TraceWindow.png" showCaption="false"} 7. When the trace has collected enough information to analyze as described in the next section, select **Pause Trace** or **Stop Trace** from the **File** menu. ### Getting Index Tuning Recommendations Use the Index Tuning Advisor in Microsoft SQL Server Management Studio to get index tuning recommendations. 1. Open Microsoft SQL Server Management Studio by selecting **Start > All Programs > Microsoft SQL Server 2008 > SQL Server Management Studio**. 2. Highlight the server that hosts the Ivanti Service Manager database. 3. From the **Tools** menu, select **Database Engine Tuning Advisor**. 4. Start a new tuning session: - If you are prompted for login credentials, specify the credentials for Ivanti Service Manager and click **Connect**. The system displays the **Database Engine Tuning Advisor** window containing a new open session. - If you are not prompted for login credentials, select **File > New Session** in the **Database Engine Tuning Advisor** window. The system prompts you for login credentials. Specify credentials for Ivanti Service Manager and click **Connect**. The system opens a new session in the **Database Engine Tuning Advisor** window. 5. On the **General** tab, in the **Workload** area, browse to the trace file that you created in [Capturing a Database Trace](docId\:NeYAB6PqFSSkNYfLnrUhp) and click **Open**. 6. In the **Select databases and tables to tune** area, highlight the Ivanti Service Manager database. *Selecting the Ivanti Service Manager Database* ::Image[]{src="https://api.archbee.com/api/optimize/parib0MC3hVoUNdfQdnac/DkCCATnJN9xpiWlh7eKsv_dbenginetuningadvisor1.png" position="flex-start" size="73" width="728" height="639" initialPath="PerfTune_Images/DBEngineTuningAdvisor_1.png" githubPath="ITSM-On-Prem-Help/Content/PerformanceTuning/PerfTune_Images/DBEngineTuningAdvisor_1.png" showCaption="false"} 7. From the drop-down menu next to the selected database in the **Selected Tables** column, choose which tables to analyze. *Selecting Tables* ::Image[]{src="https://api.archbee.com/api/optimize/parib0MC3hVoUNdfQdnac/DKYfmg889KhE5mbST8FPD_dbenginetuningadvisor2-2.png" position="flex-start" size="41" width="413" height="296" initialPath="PerfTune_Images/DBEngineTuningAdvisor_2 (2).png" githubPath="ITSM-On-Prem-Help/Content/PerformanceTuning/PerfTune_Images/DBEngineTuningAdvisor_2 (2).png" showCaption="false"} 8. Click the **Tuning Options** tab. 9. In the **Physical Design Structures (PDS) to use in database** section, select **Indexes**. 10. In the **Partitioning strategy to employ** section, select **No partitioning**. 11. In the **Physical Design Structures (PDS) to keep in database** section, select **Keep all existing PDS**. 12. Click **Start Analysis**. 13. To view progress, select the **Progress** tab. 14. To view recommended indexing actions, click the **Recommendations** tab and go to the **Index Recommendations** section. *Index Recommendations* ::Image[]{src="https://api.archbee.com/api/optimize/parib0MC3hVoUNdfQdnac/rHUaKumWLxF_G0BiDPU_M_sqlserver2.png" position="flex-start" size="80" width="829" height="419" initialPath="PerfTune_Images/sql.server.2.png" githubPath="ITSM-On-Prem-Help/Content/PerformanceTuning/PerfTune_Images/sql.server.2.png" showCaption="false"} 1. Verify that the indexes recommended for creation do not already exist in the Ivanti Service Manager database as described in [Checking for Duplicate Indexes](docId\:AUADKIY8ZWW5I_mcYjpWo). 2. To view the tuning summary, click the **Reports** tab. You can view reports in the **Tuning Reports** drop-down list beneath the tuning summary. ### About Creating New Indexes After you verify that the indexes recommended for creation do not already exist in the Ivanti Service Manager database, use the Configuration Console to create the indexes. - [Best Practices for Creating New Indexes](docId\:NeYAB6PqFSSkNYfLnrUhp) - [Creating New Indexes](docId\:NeYAB6PqFSSkNYfLnrUhp) ### Best Practices for Creating New Indexes Follow these best practices when you create new indexes: - Ivanti Service Manager does not create clustered indexes. Disregard any clustered indexes created outside of Ivanti Service Manager. - Ivanti Service Manager does not create or use include fields or included indexes. You must create and maintain any included indexes that you use outside of the Ivanti Service Manager database. We recommend that you evaluate on a case by case basis the trade off between performance gains and maintenance overhead. - Ivanti Service Manager maintains statistics only for indexes created in Ivanti Service Manager. Disregard any statistics returned by the create statistics operations in database performance tuning tools. ### Creating New Indexes Perform these steps to create new indexes using the Configuration Console: 1. From the Configuration Console, click **Build > Business Objects**. The system displays the **Business Objects** workspace. 2. Select a business object. The system displays the **Business Object** page. 3. Click the **Indexes** tab. The system displays the **Indexes** workspace with a list of indexes for the business object. 4. To add a new index, click **Add New\...**. The system displays index fields associated with this business object. 5. Select the field to index for this business object. You can add more fields later. The system displays the **System Properties** page. 6. Enter information into the fields. | Field | Description | | ----------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | Index Name | A unique name for this index. | | Description | A useful description for this index. | | Unique | Designates this value as unique, preventing duplicate entries in the index and its backing table. You can create uniqueness by using multiple fields. This option automatically creates an unique index of this field.
Unlike the primary key field:
* You can have multiple unique fields in the business object. * This field is allowed to contain NULL values (although the NULL values are not indexed). | | Clustered | Checks if the business object is part of a clustered index at the database level. Clustered indexes can greatly increase overall speed of retrieval, but usually only where data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items is selected. This is the same as a clustered index function for a Microsoft SQL database.
A clustered index cannot contain included fields (it cannot be a cover index).
This checkbox is disabled for index fields that have an increasing value, such as GUID. | | Key Fields | See [Viewing, Adding, and Deleting Key Fields](docId\:NeYAB6PqFSSkNYfLnrUhp) | | Included Fields | See [Viewing, Adding, and Deleting Included Fields ](docId\:NeYAB6PqFSSkNYfLnrUhp). | | Filter Expression | Optional. You can apply a filter to the index that does not include NULL values. Click the **add new** icon :inlineImage[]{src="https://api.archbee.com/api/optimize/parib0MC3hVoUNdfQdnac/Pf8fIo1s3wHYPQOIrQDw-_addgreen.png" alt caption size="10" width="17" height="15"}, then choose a field from the list to create a filter. Repeat as needed.
* Operation: Choose an operation from the drop-down list for this filter. * **NotNull**: The filter does not index NULL values. * Value: Enter a string value or expression for this filter. | 7. Click Add this Index. The system adds the index to the business object. ### Viewing, Adding, and Deleting Key Fields The system displays the running size, in bytes, of the index for the key fields. The limit is 900 bytes. 1. From the Configuration Console, click **Build > Business Objects**. The system displays the **Business Objects** workspace. 2. Select a business object. The system displays the **Business Object** page. 3. Click the **Indexes** tab. The system displays the **Indexes** workspace with a list of indexes for the business object. 4. Click an index name to open the record. 5. To add a key field, do the following: :::Paragraph{indent="1"} a. Click the **add** icon ::Image[]{src="https://api.archbee.com/api/optimize/parib0MC3hVoUNdfQdnac/Pf8fIo1s3wHYPQOIrQDw-_addgreen.png" size="10" isUploading="false" initialPath="../Resources/GLOBAL_Images/add_green.png" githubPath="ITSM-On-Prem-Help/Content/Resources/GLOBAL_Images/add_green.png" width="17" height="15" showCaption="false"} at the end of the row. ::: :::Paragraph{indent="1"} b. Click **not set** to select a field from the drop-down list. ::: :::Paragraph{indent="1"} c. Click **Ascending** to change the order. You can select either ascending or descending. ::: 6. To delete a key field, click the **delete** icon :inlineImage[]{src="https://api.archbee.com/api/optimize/parib0MC3hVoUNdfQdnac/LvClSW4BQv-NSBb3LHO1r_deleteredx.png" alt caption size="10" width="18" height="16"} at the end of the row. 7. Click **Save**. ### Viewing, Adding, and Deleting Included Fields 1. From the Configuration Console, click **Build > Business Objects**. The system displays the **Business Objects** workspace. 2. Select a business object. The **Business Object** page appears. 3. Click the **Indexes** tab. The system displays the **Indexes** workspace with a list of indexes for the business object. 4. Click an index name to open the record. 5. To add a key field, do the following: :::Paragraph{indent="1"} a. Click the **add** icon ::Image[]{src="https://api.archbee.com/api/optimize/parib0MC3hVoUNdfQdnac/Pf8fIo1s3wHYPQOIrQDw-_addgreen.png" size="10" isUploading="false" initialPath="../Resources/GLOBAL_Images/add_green.png" githubPath="ITSM-On-Prem-Help/Content/Resources/GLOBAL_Images/add_green.png" width="17" height="15" showCaption="false"} at the end of the row. ::: :::Paragraph{indent="1"} b. Click **not set** to select a field from the drop-down list. ::: :::Paragraph{indent="1"} c. Click **Ascending** to change the order. You can select either ascending or descending. ::: 6. To delete a key field, click the **delete** icon :inlineImage[]{src="https://api.archbee.com/api/optimize/parib0MC3hVoUNdfQdnac/LvClSW4BQv-NSBb3LHO1r_deleteredx.png" alt caption size="10" width="18" height="16"} at the end of the row. 7. Click Save. For additional information about adding indexes, see the Ivanti Service Manager online help. (For information about accessing the Ivanti Service Manager documentation.