Post reply

Huge increase in DB Size & slow retreivals after applying Indexes

  • March 27, 2009 at 5:17 am

    #198598

    Hi

    I had created a Non clustered Index on a table on 12 Fields and other 23 tables, as the existing retrieval was slow, anticipating the increase in speed.

    Before applying the Index the DB Size (MDF) was 22 GB, but after applying the Index the size has climbed to 32 GB.

    The database recovery model is set at Full as this is a production system.

    Now, any search done on this table, is so slow that only the table is displayed but blank values.

    Pls let me know in case more info is required.

    Thanks

  • March 27, 2009 at 5:58 am

    #967337

    What made you choose those 12 fields? Were you working from a query plan or missing index information or recommendations from the Tuning Advisor?

    You need to look at the execution plan to determine what's happening in the queries run against that table.

    If you need help interpreting the execution plan, you can save it as a .sqlplan file, zip it and post it here. You may need to post the query, the table, index (indexes) and some sample data as well if you want detailed assistance.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • March 27, 2009 at 7:17 am

    #967410

    Hari (3/27/2009)

    Hi

    I had created a Non clustered Index on a table on 12 Fields and other 23 tables, as the existing retrieval was slow, anticipating the increase in speed.

    Before applying the Index the DB Size (MDF) was 22 GB, but after applying the Index the size has climbed to 32 GB.

    That's expected when you create so many indexes.

    The database recovery model is set at Full as this is a production system.

    You don't have to just because it's your production system. What are your business requirements? How much data your business can afford to lose?

    Now, any search done on this table, is so slow that only the table is displayed but blank values.

    Now that's strange:unsure:

  • March 27, 2009 at 7:22 am

    #967411

    As Grant advised, chose non- clustered indexes only when needed.

    Have you got any clustered indexes on your table? First start of creating clustered index and then build any non clustered indexes. Remember few indexes complimentary to your query but more indexes performance overhead on your table

Viewing 4 posts - 1 through 4 (of 4 total)

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