Home› Service Manager Portal Feature Requests
We appreciate you taking the time to vote and add your suggestions to make our products awesome! Your request will be submitted to the community for review and inclusion into the backlog.

We recommend reviewing what is submitted before posting, in case your idea has already been submitted by another community member. If it has been submitted, vote for that existing feature request (by clicking the up arrow) to increase its opportunity of being added to Cireson solutions.

For more information around feature requests in the Cireson Community click here.

SQL Missing indexes

merlenette_jonesmerlenette_jones Member Advanced IT Monkey ✭✭✭
in Service Manager Portal Feature Requests
It would be nice to have indices added for workitemid's such as:

1. Affecteduserid
2. classid

Would make searching much more efficient
9
Up Down
9 votes

Submitted · Last Updated

Comments

  • 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 
    SELECT TOP 25
    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 ''
    END
    + 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 
    IS NOT NULL THEN ',' ELSE
    '' 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
    GO

    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.