Can you temporarily suspend index updates in SQL Server?

Post #346,642 by drook 8/23/11 3:56:40 PM Reply	Can you temporarily suspend index updates in SQL Server? We need to do bulk inserts and updates to a very large table. The update in particular is getting very slow. I know it's possible to drop an index, do the update, then rebuild the index, but we need the DB to remain available during the process. (We're working toward replication/load balancing so that this is no longer true.) Is it possible to temporarily suspend updating the index until after the batch update, then reindex all at once? -- Drew
Post #346,646 by crazy 8/23/11 4:15:19 PM Reply	Can you drop the index before the activity?
Post #346,647 by drook 8/23/11 4:22:38 PM Reply	Mentioned that The table needs to remain available during the process. Yes, it would be much better if we could take it offline, but until we get replication/load balancing working, it needs to stay up. -- Drew
Post #346,654 by crazy 8/23/11 5:59:25 PM Reply	Confused Does the table access REQUIRE the index?
Post #346,656 by drook 8/23/11 6:05:05 PM Reply	Only if we want it to not suck This is multiple millions of records, and we're searching on it constantly. If we drop all indices for several minutes while we do bulk updates, it's the same as being offline for that amount of time, isn't it? Unless we're assuming very different things. -- Drew
Post #346,660 by crazy 8/23/11 7:53:07 PM Reply	Nope, we are in agreement Does this work for you? http://blog.sqlautho...ndex-alter-index/
Post #346,662 by drook 8/23/11 8:15:17 PM Reply	That's it I knew there was a command for it. We'll need to understand the ramifications of course, but that's exactly what I was looking for. Thanks. -- Drew
Post #346,674 by crazy 8/24/11 5:17:26 AM Reply	Enjoy
Post #346,677 by drook 8/24/11 10:06:45 AM Reply	Not the word I'd use :-D -- Drew
Post #346,728 by S1mon_Jester 8/25/11 3:13:44 PM Reply	using BulkCopy bcp program? My references are from Sybase, but I'm 90% it's identical to MS-SQL. Use BulkCopy with a blocking factor - I recommend 5000 but it'll depend on your logspace. Don't forget to run an update statistics...if I recall correctly, that's a no-load operation.
Post #346,729 by S1mon_Jester 8/25/11 3:15:12 PM Reply	oh, and I'd check to see if you could turn replication off (speaking as someone who's filled the replication logs before).
Post #346,741 by drook 8/25/11 4:28:39 PM Reply	This isn't (currently) replicated If it were, this would be a non-issue. In the future, we plan to have multiple slaves. Then we just drop the master from the load balancer and do the update. Drop one slave and kick off replication. Put that slave back in, drop and replicate the next. Repeat as necessary. This is a mostly read-only DB. We need to do bulk insert/update operations. Currently we do the update in a DEV environment, then move the changes up to PROD. What I'm looking at now are the issues around simply copying the files and doing a detach/attach on PROD. If that's a fast operation, with a small chance of failing, and high confidence in rolling back to the old version if it fails, that might be the way to go. -- Drew

Welcome to IWETHEY!