locked
Adding Indexes to Tables that Already Contain Data RRS feed

  • Question

  • Good Morning,

    I am writing a .NET application that makes use of Entity Framework 4.0 and SQL Server 2008 and would appreciate insight into the following issue. 

    A particular query requires two joins and, as the number of records in the related tables increases, the amount of time it takes for this query to execute greatly increases.  For this reason, non-clustered indexes on the foreign key fields involved in the join have been added.  Interestingly, even after the indexes were created, performance has not improved.

    My question is this:  The involved tables contained a large number of records at the time the indexes were added.  Does SQL Server automatically create index pages for data that already exists at the time an index is created?  If not, is there a way to initiate the creation of index pages where necessary?

    Thanks,
    Chris


    Chris

    Monday, August 20, 2012 2:05 PM

Answers

  • The most likely scenario is that optimizer decides not to use your new index(es) because they also would require bookmark lookups.  A bookmark lookup is a random data read -- a rather costly read.  In addition, whenever a query by index also needs to include a bookmark lookup, the bookmark lookup is required for every row processed by the index.  This can quickly become expensive enough that the optimizer decides that it is just better to do a sequential read of the entire table rather than do the index seek / bookmark lookup.

    Realize that this is just a guess; however, this is also a common discovery that is made after a simple index is added to a table.  You need to supply more information including:

    the index definition

    The query being run

    Monday, August 20, 2012 2:35 PM
  • Thanks for the responses.

    I realized after submitting my question that the database the indexes were added to was not the version being used by the application.  After adding indexes to the correct database, the performance improvement was dramatic.  An application feature that once took 1 hour and 40 minutes to execute now takes about 40 seconds!  I now have a new appreciation for the importance of proper indexing.

    I should mention that, after adding the indexes, Management Studio was used to generate the index pages for existing data.  This was accomplished by expanding the table node, right clicking on the indexes folder, and selecting "rebuild all".

    Thanks again,

    Chris


    Chris

    Monday, August 20, 2012 3:42 PM

All replies

  • in your situation, you create an index on a column(s), and it covers data in that column and data subsequently added to that column.

    If you take a look at the execution plan, you should be able to determine why the query performance is poor.  Short of that, post your DDL, sample data and query and we can take a look. 

    Cheers,



    Thanks, Andrew

    Monday, August 20, 2012 2:31 PM
  • The most likely scenario is that optimizer decides not to use your new index(es) because they also would require bookmark lookups.  A bookmark lookup is a random data read -- a rather costly read.  In addition, whenever a query by index also needs to include a bookmark lookup, the bookmark lookup is required for every row processed by the index.  This can quickly become expensive enough that the optimizer decides that it is just better to do a sequential read of the entire table rather than do the index seek / bookmark lookup.

    Realize that this is just a guess; however, this is also a common discovery that is made after a simple index is added to a table.  You need to supply more information including:

    the index definition

    The query being run

    Monday, August 20, 2012 2:35 PM
  • Thanks for the responses.

    I realized after submitting my question that the database the indexes were added to was not the version being used by the application.  After adding indexes to the correct database, the performance improvement was dramatic.  An application feature that once took 1 hour and 40 minutes to execute now takes about 40 seconds!  I now have a new appreciation for the importance of proper indexing.

    I should mention that, after adding the indexes, Management Studio was used to generate the index pages for existing data.  This was accomplished by expanding the table node, right clicking on the indexes folder, and selecting "rebuild all".

    Thanks again,

    Chris


    Chris

    Monday, August 20, 2012 3:42 PM