So I have a simple Bulk Insert process to take data from our staging table and move it into our datamart.
The process is a simple data flow task with default settings for "Rows per batch" and the options are "tablock" and "no check constraint".
The table is fairly large. 587,162,986 with a data size of 201GB and 49GB of index space. The clustered index for the table is.
CREATE CLUSTERED INDEX ImageData ON dbo.ImageData ( DOC_ID ASC, ACCT_NUM ASC, MasterID ASC )
And the Primary Key is:
ALTER TABLE dbo.ImageData ADD CONSTRAINT ImageData PRIMARY KEY NONCLUSTERED ( ImageID ASC, DT_CRTE_DOC ASC )
Now we've been having an issue where
BULK INSERT via SSIS is running incredibly slow. 1 hour to insert a million rows. The query that populates the table is already sorted and the query to populate takes under a minute to run.
When the process is running I can see the query waiting on BULK insert which takes anywhere from 5 to 20 seconds and showing a wait type of
PAGEIOLATCH_EX. The process is only able to
INSERT about a thousand rows at a time.
Yesterday while testing this process against my UAT environment I was running into the same issue. I was running the process a few times and attempting to determine what the root cause of this slow insert is. Then all of a sudden it started running in under 5 minutes. So I ran it a few more times all with the same result. Also the number of bulk inserts that were waiting for 5 seconds or greater dropped form hundreds to about 4.
Now this is perplexing because it's not like we had some huge drop off in activity.
CPU during the duration is low.
The times when it's slower there appear to be fewer waits on disk.
Disk latency actually increases during the time frame that the process was running in under 5 minutes.
And The IO was much lower during the times that this process runs poorly.
I've already checked and there was no file growth as the files are only 70% full. The log file still has 50% to go. The DB is on Simple Recovery mode. DB only has one file group but is spread across 4 files.
So what I'm wondering A: why was I seeing such large wait times on those bulk inserts. B: what sort of magic happened that made it run faster?
Side note. It runs like crap again today.
UPDATE it is currently partitioned. However it's done in a method that is at best silly.
CREATE PARTITION SCHEME [ps_Image] AS PARTITION [pf_Image] TO ([FG_Image], [FG_Image], [FG_Image], [FG_Image]) CREATE PARTITION FUNCTION [pf_Image](datetime) AS RANGE RIGHT FOR VALUES ( N'2011-12-01T00:00:00.000' , N'2013-04-01T00:00:00.000' , N'2013-07-01T00:00:00.000' );
This leaves essentially all of the data in the 4th partition. However since it's all going to the same file group. The data is currently split pretty evenly across those files.
UPDATE 2 These are the overall waits when the process is running poorly.
This is the waits during the period that I was able to run the process is running well.
The storage subsystem is locally attached RAID, no SAN involved. The logs are on a different drive. Raid Controller is PERC H800 with 1 GB cache size. (For UAT) Prod is a PERC(810) .
We're using simple recovery with no backups. It's restored from a production copy nightly.
We also have set
IsSorted property = TRUE in SSIS since the data is already sorted.