Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

[Bulk Insert] Performance issues Expand / Collapse
Author
Message
Posted Tuesday, April 16, 2013 11:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 12:05 PM
Points: 45, Visits: 344
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
Post #1442891
Posted Tuesday, April 16, 2013 11:45 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:00 AM
Points: 23,244, Visits: 31,940
First question, are you doing a BULK INSERT into existing partitions or are you filling new unused partitions?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1442895
Posted Tuesday, April 16, 2013 11:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 12:05 PM
Points: 45, Visits: 344
Hi Lynn, thanks for the reply.

I'm doing a BULK INSERT into existing partitions.

Thanks
Post #1442898
Posted Tuesday, April 16, 2013 12:12 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:00 AM
Points: 23,244, Visits: 31,940
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1442918
Posted Tuesday, April 16, 2013 12:22 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, August 7, 2014 7:44 PM
Points: 2,762, Visits: 7,235
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
Post #1442927
Posted Tuesday, April 16, 2013 1:33 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:24 AM
Points: 811, Visits: 1,162
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/
Post #1442974
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse