Boost Your Performance - Bad Index Detection

Question

Boost Your Performance - Bad Index Detection

Andre Vigneau

Old Hand

Points: 361
More actions
November 18, 2004 at 10:47 am

#361619

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/colu
Kindest Regards,
Andre Vigneau MCSE, MCDBA

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

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

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 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!

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Antares686

SSC Guru

Points: 125444

December 8, 2004 at 7:27 am

#533031

Really nice article.

sunjiulu Ten Centuries Points: 1232 More actions · Answer 3

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: 840 More actions · Answer 4

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

Regards,

Meghana

Charles Wannall Ten Centuries Points: 1003 More actions · Answer 5

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

Sergiy SSC Guru Points: 110206 More actions · Answer 6

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

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110206 More actions · Answer 7