3

Is there a way to prevent SQL INSERT timeouts during SQL Server index rebuild/reorganize operations? We are using NHibernate, command timeout set at 10 minutes, SQL Server 2008 R2, index rebuild is being done online.

Charles
  • 50,943
  • 13
  • 104
  • 142
vgru
  • 49,838
  • 16
  • 120
  • 201

1 Answers1

1

Hm, no. Well, yes. If you do an online index rebuild then the table is still accessible. Requirement: ENTERPRISE edition.

What is the difference between OFFLINE and ONLINE index rebuild in SQL Server? has more information on the difference and requirements.

Community
  • 1
  • 1
TomTom
  • 61,059
  • 10
  • 88
  • 148
  • 1
    But it *is* an online rebuild operation. Tables are still accessible, but insert times can get very large during these operations, resulting in timeouts. – vgru Jan 29 '12 at 13:23
  • Check resource governer - make sure you have enough IO budget set aside ;) Not sure what else to say - faster IO helps on the server. maybe your hard disc system is just way too small. Would not be the first such server I see. – TomTom Jan 29 '12 at 15:02
  • It's a 6 disk RAID5 system, I don't think that upgrading is an option right now. But I hoped therr would be a way to throttle indexing a bit to prevent this. – vgru Jan 29 '12 at 15:28
  • 6 disc RAID5? You can also say "a 1 disc system" because this is exactly the write perfomance you get ;) Totally unsuitable for a non-trivial load database. – TomTom Jan 29 '12 at 20:36
  • hm.. Now that you've mentioned it, it is probably RAID1+0 for the db actually. So, no throttling is possible? – vgru Jan 29 '12 at 21:07
  • 1
    Given that you already have enterprise edition (or no online rebuild) - check the resource governor. THrottling may / should be possible with that. – TomTom Jan 30 '12 at 06:17
  • Thanks, I will look into it. I checked, 4-disk RAID1+0 is used for the db (2x write speed, could have done better, but what the heck). We are using the good ol' [MaintenanceSolution](http://ola.hallengren.com/) script for indexing, which runs as CmdExec (sqlcmd) command, and since I have no experience with res. governor, I will need to google a bit to see how and where to use it. – vgru Jan 30 '12 at 16:37