Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Index Creation Guidelines Expand / Collapse
Author
Message
Posted Monday, June 7, 2004 1:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 6:25 AM
Points: 138, Visits: 273
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lP


Post #119673
Posted Wednesday, June 16, 2004 1:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 25, 2004 5:50 AM
Points: 50, 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.

 

 




Post #121278
Posted Wednesday, June 16, 2004 5:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 10:58 AM
Points: 246, Visits: 65

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
Post #121314
Posted Wednesday, June 16, 2004 5:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 19, 2012 7:19 AM
Points: 23, 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

 

 

 




Post #121317
Posted Wednesday, June 16, 2004 6:33 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 1:56 PM
Points: 295, Visits: 281

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
Post #121325
Posted Wednesday, June 16, 2004 7:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 19, 2012 7:19 AM
Points: 23, 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

 

 




Post #121330
Posted Wednesday, June 16, 2004 7:27 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 1:56 PM
Points: 295, Visits: 281

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
Post #121332
Posted Wednesday, June 16, 2004 4:22 PM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Monday, October 20, 2014 1:24 PM
Points: 8,370, Visits: 743
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.


Post #121487
Posted Thursday, June 17, 2004 1:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:28 PM
Points: 2,908, Visits: 1,834

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
Newbie on www.simple-talk.com
Post #121516
Posted Thursday, June 17, 2004 3:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 25, 2004 5:50 AM
Points: 50, 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.




Post #121530
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse