rafa.aborges (4/16/2013)
Hello all,I'm facing a serious performance issue on my SQL Server 2008 database.
Let me give you my scenario.
I have a database with almost 4 TB of data. Only one table has 1,5 TB of space. This table is partitioned and have, including the clustered index, 5 indexes. This table is partitioned by a DATETIME column, with 3 hours of data in each partition.
This table is constantly populated via BULK INSERT and that's where the problem is. The number of files to be inserted through BULK INSERT keeps increasing and the database doesn't keep the same pace, it's always delayed.
I tried to do several tests: changed the recovery model, increased the BATCHSIZE, adjusted the ORDER clause. No success.
My question: is it possible to disable a particular index partition, do the BULK INSERT and, then, REBUILD the specific "broken" partition? I don't want to disable the whole index, because there are data that are constantly accessed and all of those indexes are used. We studied all those indexes and removed some unused ones and optimized the remains.
Can anyone shed a light over this?
Thanks
I don't believe you can disable an individual partition of an index. I just tried it out and I get syntax errors.
I would suggest creating a new partition, doing the bulk insert into the empty partition and then merging it into the exiting partition. If possible, I understand your data might not fit neatly into new partitions with every insert.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams