Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rebuild Index


Rebuild Index

Author
Message
ravisamigo
ravisamigo
SSC Veteran
SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)

Group: General Forum Members
Points: 269 Visits: 760
Dear All,

Can some one advise me on the below?please

When I ran a below query ,it will do index rebuilding for all indexes one after the other on particular table

Alter index all on <table_name> rebuild

But, I want to do index rebuilding for all indexes Simultaneously/Parallel.

Please advise.

Thanks and Regards,
Ravi
ravisamigo
ravisamigo
SSC Veteran
SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)

Group: General Forum Members
Points: 269 Visits: 760
Any one advise me on this ? please

Regards,
Ravi
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17605 Visits: 32267
I would suggest you take a look at Michelle Ufford's scripts on index rebuilds and defrags. She has the most comprehensive and stable approach out there.

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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
sqlbuddy123
sqlbuddy123
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1212 Visits: 2243
As Grant mentioned it's better to use Michelle Ufford's scripts or Ola hallengren scripts for the index rebuilds.

I don't think there is any straight forward way to do the parallel index rebuild. It will only rebuild the indexes one after the another.

I think you can try this ...

If Table has 3 indexes I1,I2,I3,

Then Create 3 seperate ALTER INDEX REBUILD commands for each of those indexes and create three jobs and then allow them to run at the same time. I think this will work only for the Non clustered Indexes.

Also online rebuilds are available only in the Enterprise edition.

Please test this method. I didn't test this.


Thank You,

Best Regards,
SQLBuddy.
ravisamigo
ravisamigo
SSC Veteran
SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)

Group: General Forum Members
Points: 269 Visits: 760
Thanks a lot ! for your reply.

Please advise on the below.


I have set the maintenance plan for index rebuilding for all tables but it was failed due to the deadlock.
My question is whether it's executed for all the tables apart from the index, on which index rebuild got failed due to the dead lock or it's executed upto that index.

ERROR:
------

Status: Warning: One or more tasks failed..
FailedSad-1073548784) Executing the query "ALTER INDEX [index_one] ON [dbo].[table_one] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF )
" failed with the following error: "Transaction (Process ID 224) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


Thanks and Regards,
Ravi.
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17605 Visits: 32267
I'm fairly certain that would have stopped execution of the maintenance plan. But the best thing to do is check. Have you run the maintenance plan before? How long did it take then? How long did this one take? That can tell you right there. I assume you're scheduling this through SQL Agent? You can look at the history of the job and see what happened.

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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5970 Visits: 8313
You can do your reindexing in parallel by explicitly using multiple jobs that operate on portions of your tables exclusively. Michele or Ola's stuff (latter is my preference because it has much more than just indexing stuff and it also creates jobs for you) allow you to do this I believe. AVOID SQL Server's maintenance plans COMPLETELY!!

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
sqlbuddy123
sqlbuddy123
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1212 Visits: 2243
The Maintenance plan failed when it was trying to rebuild the index [index_one] ON [dbo].[table_one] due to a deadlock. The best place to check is the Maintenance Plan Log file which could give you more information on what tables or indexes the operation had succeded.

It is not recommended to do the rebuilds diuring the Office Hours. Do those rebuilds during the night or during the weekend.

Thank You,

Best Regards,
SQLBuddy
farax_x
farax_x
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 451
HI,
I had the same problem and I changed some queries with [with (nolock)] and fastfirstrow ,... to reduce locking and rebuild with ALLOW_PAGE_LOCKS = OFF, ONLINE = on !
ALTER INDEX [index_Name] ON [table_name] 
REBUILD PARTITION = ALL WITH
( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, ONLINE = on, SORT_IN_TEMPDB = OFF )


TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5970 Visits: 8313
farax_x (1/12/2011)
HI,
I had the same problem and I changed some queries with [with (nolock)] and fastfirstrow ,... to reduce locking and rebuild with ALLOW_PAGE_LOCKS = OFF, ONLINE = on !
ALTER INDEX [index_Name] ON [table_name] 
REBUILD PARTITION = ALL WITH
( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, ONLINE = on, SORT_IN_TEMPDB = OFF )



Everyone PLEASE be aware that NOLOCK hint can get you BAD data - not just uncommitted data!!

Point two is that fastfirstrow can be DEVASTATING to performance for queries that hit lots of rows of data (whether or not it returns them) because it will basically lead to index seeks and bookmark lookup query plans just to quickly get some rows back. In large plan cases, scans and hashes/merges for joins can be WAY faster and less resource intensive in total!!!

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search