How to create a custom index.

Article:HOWTO83874  |  Created: 2013-02-26  |  Updated: 2013-02-26  |  Article URL
Article Type
How To

Understanding and implementing a custom index on a heavily read CMDB 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
To create the new indexes, please consult Microsoft SQL Server documentation or follow this link: 
*Symantec support does not support, diagnos, or assist in the creation of custom indexes, but when implemented correctly, custom indexes have great benefits. 


Article URL

Terms of use for this information are found in Legal Notices