Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Boost Your Performance - Bad Index Detection Expand / Collapse
Author
Message
Posted Thursday, November 18, 2004 10:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 21, 2008 9:10 AM
Points: 11, Visits: 5
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/colu


Kindest Regards,

Andre Vigneau MCSE, MCDBA

Post #147048
Posted Wednesday, December 08, 2004 5:18 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 03, 2007 2:55 AM
Points: 928, Visits: 1

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!
Post #150015
Posted Wednesday, December 08, 2004 7:27 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: 2 days ago @ 3:54 PM
Points: 8,369, Visits: 733
Really nice article.


Post #150035
Posted Wednesday, December 08, 2004 9:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 22, 2008 2:03 PM
Points: 180, Visits: 35

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.




Post #150068
Posted Tuesday, June 28, 2005 4:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 21, 2006 11:36 PM
Points: 64, Visits: 1
Very nice article..although I saw this at a very later date


Regards,

Meghana

Post #194643
Posted Thursday, December 08, 2005 6:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, August 25, 2013 4:41 PM
Points: 93, Visits: 30
Thanks so much.  Very helpful info to have, and nice of you to provide the tool.
Post #242831
Posted Thursday, December 08, 2005 4:30 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315

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_%'

Post #243041
Posted Thursday, December 08, 2005 4:36 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315

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_%'

Post #243043
Posted Wednesday, October 15, 2008 10:07 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 5:49 PM
Points: 2,263, Visits: 3,758
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.


Mohammed Moinudheen
Post #586704
Posted Thursday, October 16, 2008 11:23 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 5:49 PM
Points: 2,263, Visits: 3,758
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.


Mohammed Moinudheen
Post #587179
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse