patternsqlMinor
Are these good indexing guidelines?
Viewed 0 times
theseguidelinesareindexinggood
Problem
I use indexes regularly but it's still hard for me in certain conditions to know if they are helping or hurting. There are a few guidelines I follow, but I am not sure if they are good nor am I sure of their justification.
-
It is better to create an index on a narrow datatype than it is on a wide datatype (e.g.
-
It is better to create an index on multiple columns than it is on a single column.
-
It is better to create an index on a column that is never (or rarely) updated than it is to index frequently changing columns.
Are these good guidelines? Since I'm not entirely sure why I follow these guidelines, can you help explain what is the justification for each and when would they not apply?
-
It is better to create an index on a narrow datatype than it is on a wide datatype (e.g.
INT over DATETIME).-
It is better to create an index on multiple columns than it is on a single column.
-
It is better to create an index on a column that is never (or rarely) updated than it is to index frequently changing columns.
Are these good guidelines? Since I'm not entirely sure why I follow these guidelines, can you help explain what is the justification for each and when would they not apply?
Solution
Basically, SQL Server will automatically create an index for your primary key.
Other than that, I would recommend to create indices on your foreign key columns - if you have a foreign key relationship, that's most likely because you need to JOIN two tables together, and your JOIN operations will benefit significantly if an index is present on the foreign key column.
From there on - observe your system, check your system performance, gather server traces to see what queries cause the biggest CPU and/or I/O load on your system. Armed with that information, design additional indices - but sparingly! An "over-indexed" system is often worse than one without any indices!
If you see an index that would make sense and that would help one or multiple of your "heavy" queries, add that index, and observe again.
Repeat this "observe - gather information - analyse information" cycle - basically forever...
See Kimberly Tripp's excellent Indexes: just because you can, doesn't mean you should! blog post on the dangers of over-indexing
Other than that, I would recommend to create indices on your foreign key columns - if you have a foreign key relationship, that's most likely because you need to JOIN two tables together, and your JOIN operations will benefit significantly if an index is present on the foreign key column.
From there on - observe your system, check your system performance, gather server traces to see what queries cause the biggest CPU and/or I/O load on your system. Armed with that information, design additional indices - but sparingly! An "over-indexed" system is often worse than one without any indices!
If you see an index that would make sense and that would help one or multiple of your "heavy" queries, add that index, and observe again.
Repeat this "observe - gather information - analyse information" cycle - basically forever...
See Kimberly Tripp's excellent Indexes: just because you can, doesn't mean you should! blog post on the dangers of over-indexing
Context
StackExchange Database Administrators Q#8040, answer score: 4
Revisions (0)
No revisions yet.