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

November 2009 Blog Posts

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...

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 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...

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...

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

Powered by:
Powered By Subtext Powered By ASP.NET