Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Rebuild Index Expand / Collapse
Author
Message
Posted Monday, January 10, 2011 10:17 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 6:30 AM
Points: 250, Visits: 743
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
Post #1045686
Posted Tuesday, January 11, 2011 6:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 6:30 AM
Points: 250, Visits: 743
Any one advise me on this ? please

Regards,
Ravi
Post #1045845
Posted Tuesday, January 11, 2011 7:11 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 6:30 PM
Points: 15,518, Visits: 27,900
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1045885
Posted Tuesday, January 11, 2011 12:07 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:57 PM
Points: 1,194, Visits: 2,211
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.



Post #1046075
Posted Wednesday, January 12, 2011 3:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 6:30 AM
Points: 250, Visits: 743
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.

Post #1046363
Posted Wednesday, January 12, 2011 4:23 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 6:30 PM
Points: 15,518, Visits: 27,900
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1046379
Posted Wednesday, January 12, 2011 8:54 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:19 AM
Points: 4,320, Visits: 6,113
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
Post #1046549
Posted Wednesday, January 12, 2011 10:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:57 PM
Points: 1,194, Visits: 2,211
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
Post #1046633
Posted Wednesday, January 12, 2011 1:19 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 4, 2014 2:22 AM
Points: 60, Visits: 251
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 )

Post #1046763
Posted Wednesday, January 12, 2011 2:52 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:19 AM
Points: 4,320, Visits: 6,113
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
Post #1046843
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse