January 10, 2011 at 10:18 pm
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
January 11, 2011 at 6:15 am
Any one advise me on this ? please
Regards,
Ravi
January 11, 2011 at 7:11 am
I would suggest you take a look at Michelle Ufford's scripts on index rebuilds and defrags[/url]. 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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2011 at 12:07 pm
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.
January 12, 2011 at 3:47 am
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..
Failed:(-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.
January 12, 2011 at 4:23 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 12, 2011 at 8:54 am
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 on googles mail service
January 12, 2011 at 10:40 am
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
January 12, 2011 at 1:19 pm
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 )
January 12, 2011 at 2:52 pm
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 on googles mail service
January 20, 2011 at 12:56 am
Sorry for the late reply.
Thanks of your valid suggestions and help !!
Is there any impact to database if we do the index rebuild simultaneously/parellely except the performance issue means database goes to suspect mode or...?please
Please advise me on this.
Thanks and Regards,
Ravi.
January 20, 2011 at 11:14 am
It should be fine except for the clustered indexes. But please test it on the test machine before implementing.
Thank You,
Best Regards,
SQLBuddy
January 20, 2011 at 9:12 pm
Thanks for the reply.
But we don't have testing environment to test this.
Please advise whether it's fine to proceed or any impact to database.
Regards,
Ravi.
January 20, 2011 at 9:40 pm
You should be testing anything new before you do it on your production database. No development environment or anything. You're developing straight onto production? That's very dangerous. I sure hope your backups are extremely well maintained. I won't ask if they're tested.
"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
January 21, 2011 at 7:57 am
ravisamigo (1/20/2011)
Sorry for the late reply.Thanks of your valid suggestions and help !!
Is there any impact to database if we do the index rebuild simultaneously/parellely except the performance issue means database goes to suspect mode or...?please
Please advise me on this.
Thanks and Regards,
Ravi.
1) Unless you are on the Enterprise Edition and use the ONLINE option, rebuiding an index will exclusively lock that table and prevent ANY access to it while the index is being rebuilt. So you can completely shut down your application if you rebuild important tables.
2) assuming your IO is on the same spindles for these tables then yes you can seriously affect IO throughput with index maintenance. They can also hammer CPU usage as well.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply