Post reply

Clarification on Indexes needed.

  • October 1, 2014 at 8:29 am

    #311505

    I'm currently studying some SQL Server Index stuff and would like some clarification if possible. The training materials say:

    If you create a clustered index on a table that has existing nonclustered indexes, SQL Server rebuilds all the nonclustered indexes. This ensures that the new clustering key replaces the row identifier (RID) value of the nonclustered values.

    In a large database table, it’s advisable to start to build new index values based on existing clustered indexes, followed by any nonclustered indexes.

    To put this into context, we have sometimes are sent large quantities of data from a vendor to fill in gaps for a Database currently build and functioning well. We use DTSX packages to to shove the normal data files from a staging table to a landing table that usually has a clustered index on a PK/Identity column, along with a couple other nonclustered indexes on Date, or catagory-type columns. When we get large data pushes, we try to run them through the normal DTSX package however, things ALWAYS bog down. The source data is being created with query, which is used to populate the Destination table. During this process, based on what I've read, it would seem that SQL Server is rebuilding all the nonclustered indexes, as well as populating to a clustered index. Is this the performance hog? The landing tables can have 20 million records with 40+columns sometimes.

    It's been suggested in that past that when we're pumping to an indexed table like this, especially big ones, we should drop all the indexes, pump the data, then rebuild the indexes. Am I understanding things correct?

  • October 1, 2014 at 8:39 am

    #1749414

    I'm not sure what that second bullet point means. Perhaps it's advising you to create the clustered index first and all non-clustered indexes afterwards. As for whether to drop indexes, load, then recreate indexes, give it a try and see what works best. Be careful if any of the indexes enforce primary key constraints, for example, and make sure you recreate them in the order described above.

    John

  • October 1, 2014 at 9:39 am

    #1749430

    John,

    PK constraints are def. on my radar... I'm on a quest to streamline a few things so indexes are bubbleing to the top right now...

    Thanks for the reply.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply