April 16, 2013 at 11:39 am
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
April 16, 2013 at 11:45 am
First question, are you doing a BULK INSERT into existing partitions or are you filling new unused partitions?
April 16, 2013 at 11:49 am
Hi Lynn, thanks for the reply.
I'm doing a BULK INSERT into existing partitions.
Thanks
April 16, 2013 at 12:12 pm
Seeing if I can get you some more help. What I was going to suggest won't work based on your answer to my earlier question.
April 16, 2013 at 12:22 pm
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
April 16, 2013 at 1:33 pm
There is very excellent blog post by Kendra Little @brentozar.com
See if that could help.
http://www.brentozar.com/archive/2012/03/how-decide-if-should-use-table-partitioning/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply