Home Service Manager Portal Feature Requests



SQL Missing indexes

merlenette_jonesmerlenette_jones Member Advanced IT Monkey ✭✭✭
It would be nice to have indices added for workitemid's such as:

1. Affecteduserid
2. classid

Would make searching much more efficient
9 votes

Submitted · Last Updated


  • Konstantin_Slavin-BoKonstantin_Slavin-Bo Customer Ninja IT Monkey ✭✭✭✭
    That would actually be really nice to have, as I'm guessing it should improve search times quite a bit, when there's indices!

    Good suggestion, I'm upvoting!
  • Peter_SettlePeter_Settle Customer Advanced IT Monkey ✭✭✭

    Count me in , Great Idea.

    Up Voted.

  • Morten_MeislerMorten_Meisler Premier Partner Advanced IT Monkey ✭✭✭
    -- Missing Index Script
    -- Original Author: Pinal Dave
    dm_mid.database_id AS DatabaseID,
    dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
    dm_migs.last_user_seek AS Last_User_Seek,
    OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
    'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
    + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','')
    + CASE
    WHEN dm_mid.equality_columns IS NOT NULL
    AND dm_mid.inequality_columns IS NOT NULL THEN '_'
    ELSE ''
    + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
    + ']'
    + ' ON ' + dm_mid.statement
    + ' (' + ISNULL (dm_mid.equality_columns,'')
    + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns
    '' END
    + ISNULL (dm_mid.inequality_columns, '')
    + ')'
    + ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
    FROM sys.dm_db_missing_index_groups dm_mig
    INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
    ON dm_migs.group_handle = dm_mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details dm_mid
    ON dm_mig.index_handle = dm_mid.index_handle
    WHERE dm_mid.database_ID = DB_ID()
    ORDER BY Avg_Estimated_Impact DESC

    Have anyone tried making missing indexes from above query? Nr. 1 should be AffectedUser that has a pretty big impact.

    But I guess it's only for specific searching scenarios. Just wonder if anyone could share some experience.

    And yes this is ofc a tempory workaround and unsupported to create the index yourself as it could get wiped in the next update.

Sign In or Register to comment.