• SQLJay (2/26/2015)


    Hello all,

    We use an ERP system that is leveraging SQL2008R2 x64 database. Some of the ERP tables are massive holding close to 275 million rows of data.

    Our work environment is close to 24/7 so addressing index fragmentation has been impossible. As an accidental dba, I am doing my best to read and educate myself.

    I am reading about table partitioning to see if it can be leveraged but then again, there are dependencies and limitations posed by the ERP software which makes the situation even more difficult. I have also tried index maintenance with ONLINE option enabled, which ended up creating a concern. Towards the latter part of the completion of that task, the process takes a lock on the table which impacted business.

    Question: What if I create a brand new index, an exact duplicate of an existing index that I'm trying to run the maintenance on ? That way, the existing index will continue to be in use while the duplicate index is being built. When the new index finishes, I can issue the delete command on that old one. At that point, the newly created index will started getting used.

    Is my thinking wrong, will this work ? are there any downfalls for going this route ?

    regards,

    SQLJay

    Which edition of SQL Server do you have? Standard or Enterprise? Also, how much free space do you have where the MDF file lives and how much free space do you have where the LDF file lives? Can you post the CREATE TABLE statement for the table along with all indexes and constraints so that we might be able to do a deeper dive for you?

    Last but not least, how many GB does the table and it's indexes occupy? One way to find out is to run the following code

    EXEC sp_SpaceUsed 'puttablenamehere';

    Part of the reason I'm asking all the questions is, yes... there are many downfalls and "tricks of the trade" when it comes to index maintenance on large tables that a lot of the "canned" solutions that people have provided simply haven't included because they really require some human evaluation and deep concern for disk space.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)