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


Index Creation Guidelines


Index Creation Guidelines

Author
Message
Leo Peysakhovich
Leo Peysakhovich
SSC Eights!
SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)

Group: General Forum Members
Points: 968 Visits: 367
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lP



Mark_Holst
Mark_Holst
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 1

A good article. I think one small oversight is the use of "Covering Indexes" - if I am using the correct term.

The concept is using a composite index to return columns directly [from the index] rather than requiring a separate operation to perform a "bookmark lookup". A bookmark lookup can be rather costly in certain scenarios.

On one occasion I have used this technique to avoid disc thrashing on a certain large table.





BuckWoody
BuckWoody
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 75

Great article - but I do take issue with the statement that "every table needs a clustered index". In large, non-contiguous tables such as import tables for an ODS or Data Warehouse, this can dramatically slow down inserts.

Buck




Buck Woody
MCDBA, MCSE, Novell and Sun Certified
avi_a
avi_a
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 36

On the same note:

I was once told that creating a forein-key constraint upon two tables causes an implicit creation of some indexes that can speed up joins on the FK relatoinship.

Does anyone know of this kind of index (it should be some kind of "cross-table" or "relational" index)?

If creating a FK does in fact cause creation of some kind of cross-table index, can the same kind of index be created without the FK constraints?

Many thanks,

- Avi





Tatsu
Tatsu
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1666 Visits: 307

Avi,

Some Entity-Relationship modeling tools create indexes on FK relationships for you behind the scenes but SQL Server does not do this on its own. Also, an index can only apply to a single table and is completely separate from the constraints on a table (Including FK's) so your last question really does not apply.

Example:
If you have an OrderItem table with a ProductID column that references a Product table via foreign key, the ProductID column on the OrderItem table would not automatically have an index on it but you would probably want to create one since it will likely be used frequently in join operations.



Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
avi_a
avi_a
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 36

Thanks Bryant.

Do you know what kind of index is created automatically when creating a FK? is this some sort of an implicit index or can it be scripted from EM like any other index?

The reason I'm so interested in this issue is this:

I have a data set with FKs which is replicated into a subscription without the FK constraints. (this is by design)

However, when testing my queries on the original publication vs the subscription DB, I noticed the execution plans on the publisher used many indexes that wern't used in the subscription database, thus producing degraded performance on the subscription DB.

So what I really want to do is create in the subscription DB indexes that will be equivilent to the automatic indexes created in the publisher via thit's FKs.

Any thoughts on this would be appreciated,

- Avi





Tatsu
Tatsu
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1666 Visits: 307

The indexes should be like any other index but some of those modelling tools use some strange naming conventions. You should be able to script these out like any other index regardless of the name though.

There is one thing to watch out for if the database was originally designed using a modeling tool that created the indexes for you. If you do not make all schema changes in the modeling tool you could end up with inconsistencies in how these indexes are built on new foreign keys. If the database was originally built with one of these tools but the model is no longer used, make sure you manually create the indexes on the FK's where needed.

I only have experience with ERWin and Visio. ERWin used to create the indexes for you (I think there was an option to turn it off) but it has been several years since I used it. Visio does not create the indexes for you automatically but its diagramming scheme shows you which columns have FK's and indexes so it is easy to see where you might have missed an important index on a FK column.



Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Antares686
Antares686
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: Moderators
Points: 27308 Visits: 802
I agree, especially in the way of datamarts as you don't want to have to wait for shifting of data to complete. Clustered indexes have a great impact but does not always lend itself to some systems.



Dave Poole
Dave Poole
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17004 Visits: 3403

Good article!

One thing I'm not sure of is where you would use CREATE STATISTICS.

I know indices maintain their own statistics but if you do a query using an ORDER BY, WHERE, GROUP BY, HAVING etc on a non-indexed column SQL Server creates a _wa_sys statistic.

Is there a performance overhead in maintaining these statistics as there is with indices?



LinkedIn Profile
www.simple-talk.com
Mark_Holst
Mark_Holst
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 1

I think rather than everyone chipping in there, somebody should make that the next article topic!

I remember in the MCP study guide for SQL database & design, there was a whole section on statistics that was both fascinating and helpful, and makes you appreciate how it's mostly all automated. One thing that you did have to come out remembering was the creation of automated statistics (like the _wa_ ones you mentioned), and how those columns could indeed make themselves index candidates.





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