on-line indexing in MS SQL 2005 Problems

  • Hi experts,

    I am running this following statement on my production . which is critical production box

    I want know before running this statement on my box in the evening (non peak hours )what steps should I take ?

    ALTER INDEX ALL ON Production.Product

    REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,

    STATISTICS_NORECOMPUTE = ON);

    Appreciate your quick response

    Thanks

    ichbinraj

  • Sounds like a good plan - after hours and such. How many indexes/rows are associated with the table? Have you done this in a dev environment to gauge an estimate as to how long the process will take so that you're certain it fits within your maintenance window?

    -- You can't be late until you show up.

  • I have tested this statements on test-server already.. found no errors

    Yesterday I tried again to run this statement .. found users are disconnecting from the session ..

    any information why its being kicking of users

    As this statement on-line indexing means it should not disturb the users right ?

    what is the best practice to do this operation ?

    thanks

    Ichbinraj

  • I would check first fragmentation on indexes of the table. If it is low on some indexes I would rebuild only necessary ones and not all of them, or you might find that fill factor of 90% is too high, or you do not need rebuild – just reorganize, or you can rebuild only clustered index that will automatically rebuild non-clustered. Then I would check available space on TempDB drive because on-line index rebuild needs to keep original index while rebuilding new one – it means if table is big you need enough free space. Also, you can create maintenance plan in management studio, where you can drug-and-drop tasks with index rebuild/reorganize. It will help you:

    - Schedule task at necessary time;

    - View T-SQL statement and make sure you don’t forget anything important;

    - Create a routine maintenance task;

    - Mark check-box "index online" so no one would be disconnected.

    You always can test that task on your local machine if you recreate table structure and load it with a part of data from original table.

    Alex Prusakov

  • ichbinraj (5/5/2009)


    Hi experts,

    I am running this following statement on my production . which is critical production box

    I want know before running this statement on my box in the evening (non peak hours )what steps should I take ?

    ALTER INDEX ALL ON Production.Product

    REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,

    STATISTICS_NORECOMPUTE = ON);

    Appreciate your quick response

    Thanks

    ichbinraj

    Your statement should included in the WITH statement (ONLINE = ON) in order for this to be an online operation. So, the full statement should be

    ALTER INDEX ALL ON Production.Product

    REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,

    STATISTICS_NORECOMPUTE = ON, ONLINE = ON);

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • Sorry, I forget to say that online indexing is available ONLY in the Enterprise Edition. So, if you are on the Standard Edition - you are out of luck and need to do it as off-line operation (no one using table).

    Alex Prusakov

  • Thanks for your information.

    I Have 1 TB database nearly 68,000 tables its heavily used database . I am doing on-line indexing on only large tables and large indexed tables I have nearly 100 tables list, Temp DB has enough space like 150 GB and I am doing individual table one by one , Largest table size I have is 75 GB.

    When I am trying to find fragmentation report its taking couple of hours , to avoid this process , I choose to rebuilding index on large tables and large indexed tables.

    I did this operation last time in production with no issues and no user disconnections. now We I am trying to do it again its just killing the users.

    Please let me know your valuable suggestions.

    Thanks

    ichbinraj

  • Read my previous post. Your index rebuild statement was not indexing online and would thereby kill any activity on the table being indexed.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • oh my good ness...

    Thanks guys .. I missed the ONLINE = ON); statement I really Appreciate your help and suggestion

    Thanks

    Ichbinraj

  • Thanks guys ,

    Is It best to put FILLFACTOR = 80 ?

    ichbinraj

  • Glad to be of help. Wish I could say that it never happened to me but I have left that off a time or two and impacted users as well. 😉

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • ichbinraj (5/5/2009)


    Thanks guys ,

    Is It best to put FILLFACTOR = 80 ?

    ichbinraj

    One of those "it depends" answers. How much data is being loaded into the tables? If a lot, 80 may work better to keep your fragmentation lower and page splits less frequent. Are you seeing large amounts of fragmentation on a regular basis but do not have a maintenance window to address the issue?

    -- You can't be late until you show up.

  • Instead of waiting report you can use SSMS and in the index properties of the specific table you can see fragmentation on table by table, index by index. Also, you can use DMVs.... Yes, it takes some time but not that bad and it is much faster than a report.

    Alex Prusakov

  • Its SAP system and every day they have huge transports .. very hard to find maintenance window ..

    Quick Question about my process : Am i doing right thing for indexing process that taking only large indexed and big tables ?

    or shall do it each and every table..

    Last time I was doing Fragmentation process its took nearly 3 hours .. then I stopped my process and decide to go this way..

    Thanks

    Ichbinraj

  • Another "it depends". How large is the maintenance window? By not doing all tables, only focusing on the larger ones, are you seeing peformance degradation? The are some scripts on this site that'll only index those with large amounts of fragmentation (based on your threshold). These are just a couple, but there are others on this site as well.

    http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31846/

    http://www.sqlservercentral.com/scripts/SQL+Server+2005/61278/

    -- You can't be late until you show up.

Viewing 15 posts - 1 through 15 (of 26 total)

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