Creating a custom index.
search cancel

Creating a custom index.

book

Article ID: 181400

calendar_today

Updated On:

Products

IT Management Suite

Issue/Introduction

 Do you have a suggestion regarding creating our own indexes?

Environment

ITMS 7.x, 8.x

Resolution

Understanding and implementing a custom index on a heavily read Symantec_CMDB database table can greatly improve product performance. This performance gain has a tradeoff. SQL queries using the indexed field will resolve much quicker than a non-indexed field.

There are a few ways of determining potential new index. Here is one SQL statement method for determining the tables and fields that have taken the longest for a SQL server to return results:

 
SELECT top 10 reverse(substring(reverse([statement]), 2, charindex('[',reverse([statement]))-2)) [Table],
                ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) [Total User Cost],
                mid.equality_columns [Equality Columns],
                mid.inequality_columns [Inequality Columns],
                mid.included_columns [Included Columns]         
FROM sys.dm_db_missing_index_groups mig
                INNER JOIN sys.dm_db_missing_index_group_stats migs
                                ON migs.group_handle = mig.index_group_handle
                INNER JOIN sys.dm_db_missing_index_details mid
                                ON mid.index_handle = mig.index_handle
ORDER BY [Total User Cost] DESC


Example output:
 
 
To create the new indexes, please consult Microsoft SQL Server documentation or follow this link: 
https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver16&redirectedfrom=MSDN
 
*Broadcom support does not support, diagnos, or assist in the creation of custom indexes, but when implemented correctly, custom indexes have great benefits.