November 2009 Blog Posts
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...
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...
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...