SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Boost Your Performance - Bad Index Detection


Boost Your Performance - Bad Index Detection

Author
Message
Andre Vigneau
Andre Vigneau
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 5
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/colu


Kindest Regards,

Andre Vigneau MCSE, MCDBA

Jonathan Stokes
Jonathan Stokes
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2337 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!
Antares686
Antares686
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: Moderators
Points: 26246 Visits: 785
Really nice article.



sunjiulu
sunjiulu
Old Hand
Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)

Group: General Forum Members
Points: 396 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.





Meghana
Meghana
SSC Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
Points: 218 Visits: 1
Very nice article..although I saw this at a very later date


Regards,

Meghana

Charles Wannall
Charles Wannall
SSC Veteran
SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)

Group: General Forum Members
Points: 234 Visits: 31
Thanks so much. Very helpful info to have, and nice of you to provide the tool.
Sergiy
Sergiy
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25053 Visits: 12464

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
Sergiy
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25053 Visits: 12464

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
M&M
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5951 Visits: 3913
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
M&M
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5951 Visits: 3913
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search