on-line indexing in MS SQL 2005 Problems

Question

on-line indexing in MS SQL 2005 Problems

ichbinraj

SSCommitted

Points: 1627
More actions
May 5, 2009 at 7:31 am

#155657

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

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

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

tosscrosby SSCoach Points: 18045 More actions · Answer 1

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.

ichbinraj SSCommitted Points: 1627 More actions · Answer 2

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

Alex Prusakov Ten Centuries Points: 1154 More actions · Answer 3

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

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 4

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

Alex Prusakov Ten Centuries Points: 1154 More actions · Answer 5

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

ichbinraj SSCommitted Points: 1627 More actions · Answer 6

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

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 7

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

ichbinraj SSCommitted Points: 1627 More actions · Answer 8

oh my good ness...

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

Thanks

Ichbinraj

ichbinraj SSCommitted Points: 1627 More actions · Answer 9

Thanks guys ,

Is It best to put FILLFACTOR = 80 ?

ichbinraj

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 10

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

tosscrosby SSCoach Points: 18045 More actions · Answer 11

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.

Alex Prusakov Ten Centuries Points: 1154 More actions · Answer 12

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

ichbinraj SSCommitted Points: 1627 More actions · Answer 13

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

tosscrosby SSCoach Points: 18045 More actions · Answer 14

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.