InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 01:21:32 GMT20http://www.sqlservercentral.com/Forums/Topic1045686-360-1.aspx[quote][b]ravisamigo (1/20/2011)[/b][hr]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...?pleasePlease advise me on this.Thanks and Regards,Ravi.[/quote]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.Fri, 21 Jan 2011 07:57:14 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1045686-360-1.aspxYou 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.Thu, 20 Jan 2011 21:40:10 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1045686-360-1.aspxThanks 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.Thu, 20 Jan 2011 21:12:57 GMTravisamigohttp://www.sqlservercentral.com/Forums/Topic1045686-360-1.aspxIt should be fine except for the clustered indexes. But please test it on the test machine before implementing. Thank You,Best Regards,SQLBuddyThu, 20 Jan 2011 11:14:30 GMTsqlbuddy123http://www.sqlservercentral.com/Forums/Topic1045686-360-1.aspxSorry 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...?pleasePlease advise me on this.Thanks and Regards,Ravi.Thu, 20 Jan 2011 00:56:51 GMTravisamigohttp://www.sqlservercentral.com/Forums/Topic1045686-360-1.aspx[quote][b]farax_x (1/12/2011)[/b][hr]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 ! [code="sql"]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 )[/code][/quote]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!!!Wed, 12 Jan 2011 14:52:03 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1045686-360-1.aspxHI,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 ! [code="sql"]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 )[/code]Wed, 12 Jan 2011 13:19:44 GMTfarax_xhttp://www.sqlservercentral.com/Forums/Topic1045686-360-1.aspxThe 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,SQLBuddyWed, 12 Jan 2011 10:40:38 GMTsqlbuddy123http://www.sqlservercentral.com/Forums/Topic1045686-360-1.aspxYou 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!!Wed, 12 Jan 2011 08:54:34 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1045686-360-1.aspxI'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.Wed, 12 Jan 2011 04:23:07 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1045686-360-1.aspxThanks 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.Wed, 12 Jan 2011 03:47:27 GMTravisamigohttp://www.sqlservercentral.com/Forums/Topic1045686-360-1.aspxAs 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.Tue, 11 Jan 2011 12:07:12 GMTsqlbuddy123http://www.sqlservercentral.com/Forums/Topic1045686-360-1.aspxI would suggest you take a look at Michelle Ufford's scripts on [url=http://sqlfool.com/2009/06/index-defrag-script-v30/]index rebuilds and defrags[/url]. She has the most comprehensive and stable approach out there.Tue, 11 Jan 2011 07:11:50 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1045686-360-1.aspxAny one advise me on this ? pleaseRegards,RaviTue, 11 Jan 2011 06:15:37 GMTravisamigohttp://www.sqlservercentral.com/Forums/Topic1045686-360-1.aspxDear All,Can some one advise me on the below?pleaseWhen 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> rebuildBut, I want to do index rebuilding for all indexes Simultaneously/Parallel.Please advise.Thanks and Regards,RaviMon, 10 Jan 2011 22:17:59 GMTravisamigo