Boost Your Performance - Bad Index Detection

  • Andre Vigneau

    Old Hand

    Points: 361

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/colu


    Kindest Regards,

    Andre Vigneau MCSE, MCDBA

  • Jonathan Stokes

    SSCrazy Eights

    Points: 9861

    Thanks. The timing is perfect as I am looking at indexes at the moment with a view to leaving some out of the build as they appear not to be used.

     

    it's a datamart staging table that I am looking at peforming a lot of idex scans whilst doing big selects. might as well table scan than index scan for the tinme it takes to build them.


    ------------------------------
    The Users are always right - when I'm not wrong!

  • Antares686

    SSC Guru

    Points: 125444

    Really nice article.

  • sunjiulu

    Ten Centuries

    Points: 1212

    This is a bottomline index check, really cool, I used to do it manually on many sqlserver environment, it took time. now  the script will help me. thanks.

  • Meghana

    SSC Eights!

    Points: 830

    Very nice article..although I saw this at a very later date


    Regards,

    Meghana

  • Charles Wannall

    SSC Eights!

    Points: 992

    Thanks so much.  Very helpful info to have, and nice of you to provide the tool.

  • Sergiy

    SSC Guru

    Points: 109666

    Great stuff. Thanks.

    Only 2 notes:

    WHERE <...> AND i.indid < 255 and i.name NOT LIKE '_WA_Sys_%'

    eliminates NULLs and makes join to sysindexes actually INNER, not LEFT.

    I'm not sure if it's possible to have a table without indexes at all. It must be an index with indid either 0 or 1 for each table.

    And COALESCE or ISNULL works faster than case ... IS NULL then ... else ... end.

    So I have rewritten your code this way:

    SELECT o.id AS TableID ,u.name Owner,o.name TableName,

      i.Indid AS IndexID

      , CASE i.name

       WHEN o.name THEN '** NONE **'

      ELSE i.name END AS IndexName,

           CASE i.indid

       WHEN 1 THEN 'CLUSTERED'

       WHEN 0 THEN 'HEAP'

      ELSE 'NONCLUSTERED' END AS ClusterType,

      CASE

         WHEN (i.status & 2048) > 0 THEN 'PRIMARY KEY'

       WHEN (i.status & (2|4096)) > 0 THEN 'UNIQUE'

      ELSE '' END AS UniqueType,

        CASE      

       WHEN (i.status & (2048)) > 0

         OR ((i.status & (4096)) > 0 )

        THEN 'CONSTRAINT'

       WHEN i.indid = 0 THEN ' '

      ELSE 'INDEX' END AS IndexType,

    -- This following part is non essential

    -- It is a pre char aggregate I use in other scripts

    -- to generate create and drop scripts

     COALESCE (     INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 1), '') +

        COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 2), '') +

     COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 3), '') +

     COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 4), '') +

     COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 5), '') +

        COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 6), '') +

        COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 7), '') +

        COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 8), '') +

        COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 9), '') +

        COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 10), '') +

        COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 11), '') +

        COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 12), '') +

        COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 13), '') +

        COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 14), '') +

        COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 15), '') +

        COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 16), '') AS AllColName,

    --

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 1), '')  AS ColName1,

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 2), '') AS ColName2,

      COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 3), '') AS ColName3,

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 4), '') AS ColName4,

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 5), '') AS ColName5,

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 6), '') AS ColName6,

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 7), '') AS ColName7,

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 8), '') AS ColName8,

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 9), '') AS ColName9,

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 10), '') AS ColName10,

      COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 11), '') AS ColName11,

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 12), '') AS ColName12,

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 13), '') AS ColName13,

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 14), '') AS ColName14,

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 15), '') AS ColName15,

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 16), '') AS ColName16

     FROM sysobjects o (NOLOCK)

     INNER JOIN sysindexes i (NOLOCK) ON o.id = i.id

        JOIN sysusers u (NOLOCK) ON o.uid = u.uid

     WHERE o.type = 'U' AND i.indid < 255

     AND o.name NOT IN ('dtproperties')

     AND i.name NOT LIKE '_WA_Sys_%'

  • Sergiy

    SSC Guru

    Points: 109666

    Great stuff. Thanks.

    Only 2 notes:

    WHERE <...> AND i.indid < 255 and i.name NOT LIKE '_WA_Sys_%'

    eliminates NULLs and makes join to sysindexes actually INNER, not LEFT.

    I'm not sure if it's possible to have a table without indexes at all. It must be an index with indid either 0 or 1 for each table.

    And COALESCE or ISNULL works faster than case ... IS NULL then ... else ... end.

    So I have rewritten your code this way:

    SELECT o.id AS TableID ,u.name Owner,o.name TableName,

      i.Indid AS IndexID

      , CASE i.name

       WHEN o.name THEN '** NONE **'

      ELSE i.name END AS IndexName,

           CASE i.indid

       WHEN 1 THEN 'CLUSTERED'

       WHEN 0 THEN 'HEAP'

      ELSE 'NONCLUSTERED' END AS ClusterType,

      CASE

         WHEN (i.status & 2048) > 0 THEN 'PRIMARY KEY'

       WHEN (i.status & (2|4096)) > 0 THEN 'UNIQUE'

      ELSE '' END AS UniqueType,

        CASE      

       WHEN (i.status & (2048)) > 0

         OR ((i.status & (4096)) > 0 )

        THEN 'CONSTRAINT'

       WHEN i.indid = 0 THEN ' '

      ELSE 'INDEX' END AS IndexType,

    -- This following part is non essential

    -- It is a pre char aggregate I use in other scripts

    -- to generate create and drop scripts

     COALESCE (     INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 1), '') +

        COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 2), '') +

     COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 3), '') +

     COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 4), '') +

     COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 5), '') +

        COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 6), '') +

        COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 7), '') +

        COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 8), '') +

        COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 9), '') +

        COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 10), '') +

        COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 11), '') +

        COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 12), '') +

        COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 13), '') +

        COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 14), '') +

        COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 15), '') +

        COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 16), '') AS AllColName,

    --

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 1), '')  AS ColName1,

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 2), '') AS ColName2,

      COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 3), '') AS ColName3,

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 4), '') AS ColName4,

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 5), '') AS ColName5,

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 6), '') AS ColName6,

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 7), '') AS ColName7,

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 8), '') AS ColName8,

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 9), '') AS ColName9,

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 10), '') AS ColName10,

      COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 11), '') AS ColName11,

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 12), '') AS ColName12,

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 13), '') AS ColName13,

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 14), '') AS ColName14,

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 15), '') AS ColName15,

         COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 16), '') AS ColName16

     FROM sysobjects o (NOLOCK)

     INNER JOIN sysindexes i (NOLOCK) ON o.id = i.id

        JOIN sysusers u (NOLOCK) ON o.uid = u.uid

     WHERE o.type = 'U' AND i.indid < 255

     AND o.name NOT IN ('dtproperties')

     AND i.name NOT LIKE '_WA_Sys_%'

  • M&M

    SSC-Insane

    Points: 21679

    I found this script to be useful for sql server 2005 as well.

    We do have DMV's for this purpose in 2005. But still nice to have this script.

    Well, if we need to run at the instance level rather than database level,

    could we have that that too?

    Because we have so many databases on the instance.

    M&M

  • M&M

    SSC-Insane

    Points: 21679

    Just to make it clear, my query is this

    -> I want to run this query on a database instance. This should run for all the user databases on the instance all at once.

    -> By any chance, do any of you have this kind of script.

    Good thing is, this script is useful for sql server 2005 also.

    M&M

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply