November 3, 2008 at 2:34 pm
Not sure if this can be done, please clue me in if so.
I'm adding large amounts of data from a staging database into a production database. The new data doesn't doesn't have to be available immediately.
The index maintenance is dragging the performance down. Since this is a production database, I can't just take the easy route of disabling the indexes.
So, can I disable ongoing maintenance on the non-clustered/non-unique indexes, keeping those indexes online? Those indexes would continue to be used by the production system to access the data known at the point of stopping. Then when the load is complete, those indexes can be rebuilt to pick up the new records.
This is using 2005 Enterprise.
November 3, 2008 at 2:39 pm
Can you give some more info?
While you insert the huge data the table will be locked for that time.
November 3, 2008 at 2:51 pm
Production has several 100MM records, and I'm adding 100MM new each week. My add process is the only way records get added or changed for these tables.
These new records are never read by the application until the next weekend when they reach their "effective date". During the load, the existing data and indexing needs to stay alive because the application will be using them.
Because these new records aren't needed until the next week, I'm hoping for a way to stop the maintenance on the indexes during my load, and then some night before the records go live I rebuild those indexes. In the meantime the indexes live on with what they had before maintenance was stopped.
November 3, 2008 at 3:19 pm
The load is done with SSIS, not using the Table Lock, using Rows per batch and Maximum insert commit size to help keep locking somewhat reasonable.
I've yet to have problems with table locks (although I could just be lucky so far).
November 4, 2008 at 12:58 am
Tony (11/3/2008)
So, can I disable ongoing maintenance on the non-clustered/non-unique indexes, keeping those indexes online? Those indexes would continue to be used by the production system to access the data known at the point of stopping. Then when the load is complete, those indexes can be rebuilt to pick up the new records.
It's not possible. Indexes will always be updated as part of a data modification. SQL doesn't allow the tables and the indexes to be inconsistent.
You can disable the index, which will mean it's not available for use, and then rebuild it later.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply