syscomments

RocketScientist's Miscellaneous Ramblings
posts - 76, comments - 354, trackbacks - 3

Wednesday, November 11, 2009

Left-duplicate indexes

At Kim Tripp's PASS session on indexing and covered indexes, she more-or-less proved that wider is better for indexes, within reason and searchability.  It's better to have the index (Column1, Column2, Column3) than the index (Column1) and then a (Column1, Column2) and then a (Column1, Column2, Column3).  And the first two of that set are redundant and don't provide any significant performance improvement over a wider index in most applications.

Finding cases where you have multiple indexes like that isn't a trivial problem.  Here's the answer I came up with.  This particular query finds cases of a (Column1, Column2....ColumnN) with a (Column1) but it could be pretty easily modified to do a multiple left side key required.  It's all filtering, just add [3] and the appropriate group by/max and you're set.  And once again, yes, you could do this without a temp table, but the temp table's kind of interesting on its own.

 

select oname, index_id, [1], [2], [3], [4], [5], [6],
[7], [8], [9], [10], [11], [12], [13], [14],
[15], [16], [17], [18], [19]
      into #TableList
      from (
            select object_name(object_id) oname,
index_id, key_ordinal, Column_ID
from sys.index_columns) AS SourceTable
            pivot(max(column_id)
            for key_ordinal in([1], [2], [3], [4], [5],
[6], [7], [8], [9], [10], [11], [12],
[13], [14], [15], [16], [17], [18], [19])
            ) as PivotTable
           
select oname, [1], max([2]), count(*)
from #TableList
group by oname, [1]
having count(*) > 1
order by 1

 

 

posted @ Thursday, November 12, 2009 11:05 AM | Feedback (0) | Filed Under [ KCTechBlog Syndication ]

Finding tables with no unique indexes

Same vendor, different issue.  How many tables in the database aren't really tables, but are actually just 2 dimensional data stores of unknown quality?  Tables should have a unique index.  Of some kind.  If it's a formal PK, or a unique constraint or a unique index, it needs have *something* unique enforced, or it's not really a relational table, but is instead a pile of unknown quality data. 

 

select so.name

from sys.objects so

      left join sys.indexes si

            on si.object_id = so.object_id

            and si.is_unique = 1

where si.index_id is null

      and so.type_desc = 'user_table'

order by 1

 428 tables with no unique indexes.  This goes with the 36 completely duplicated indexes and 50+ partially duplicated indexes. 

And by the way, I checked.  is_primary_key implies is_unique, and is_unique_constraint implies is_unique.

 

 

posted @ Wednesday, November 11, 2009 2:30 PM | Feedback (0) |

Finding duplicated indexes

So I wanted to start being a bit more aggressive about index management.  Specifically, I want to be able to find duplicated indexes inside of one of my vendor databases.  So (1) I can make fun of them even more than I do already, and (2) so I can give them a script of all their issues that they might be able to resolve them.

Here’s what I came up with.  Only really works if you have 19 or fewer columns per index.  Why 19?  I got tired of typing there.  You can write this up as a code generator if you don’t like it.  There are better ways to do this, but the way I’m doing it you can look at the temp table and use it to find the more subtle problem of a table with two indexes which are subsets of each other, which are usually but not always redundant.  Anyway, here ya go.

 

drop table #tablelist

 

select oname, index_id, [1], [2], [3], [4], [5], [6],

[7], [8], [9], [10], [11], [12], [13], [14],

[15], [16], [17], [18], [19]

into #TableList

from (

      select object_name(object_id) oname, index_id,

key_ordinal, Column_ID from sys.index_columns) AS SourceTable

      pivot (max(key_ordinal)

            for column_id in ([1], [2], [3], [4], [5], [6],

[7], [8], [9], [10], [11], [12], [13], [14],

[15], [16], [17], [18], [19]

)

) as PivotTable

           

     

select oname, [1], [2], [3], [4], [5], [6],

[7], [8], [9], [10], [11], [12], [13],

[14], [15], [16], [17], [18], [19]

from #TableList

group by oname, [1], [2], [3], [4], [5], [6],

[7], [8], [9], [10], [11], [12], [13], [14],

[15], [16], [17], [18], [19]

having count(*) > 1

order by 1

posted @ Wednesday, November 11, 2009 9:45 AM | Feedback (0) |

Powered by:
Powered By Subtext Powered By ASP.NET