January 12, 2015 at 10:55 am
Brandie Tarvin (1/12/2015)
And the verdict is in. It was the mirroring after all. Specifically, it was synchronous mode. When I set it to asnychronous, it only took 2 and 1/2 hours to complete.So we need to update the job to switch SAFETY OFF before the process kicks off, then switch it back to FULL when we're done.
YAY! EASY FIX!
Just keep in mind that while it's in asynch. mode, you are introducing the potential for data loss if you have a failure of some sort on the principal during that time.
The Redneck DBA
January 12, 2015 at 11:17 am
The Redneck DBA (1/12/2015)
Brandie Tarvin (1/12/2015)
And the verdict is in. It was the mirroring after all. Specifically, it was synchronous mode. When I set it to asnychronous, it only took 2 and 1/2 hours to complete.So we need to update the job to switch SAFETY OFF before the process kicks off, then switch it back to FULL when we're done.
YAY! EASY FIX!
Just keep in mind that while it's in asynch. mode, you are introducing the potential for data loss if you have a failure of some sort on the principal during that time.
We are aware of that. But needs must and the file imports are usually done in off hours with minimal other activity occuring.
Three days to upload a file was causing too many other issues with both internal and external customers.
January 12, 2015 at 11:22 am
Brandie Tarvin (1/12/2015)
Lynn Pettis (1/12/2015)
Brandie, just curious, when you are importing the file how is it inserted into the table(s)? Does the data go in sequentially on the clustered index? What I am wondering about is page splits and fragmentation.I would love to tell you. Except this process is a vendor process and everything works through SQL jobs and .bat files that call encrypted stored procedures. All I know is the file is pulled into a staging table and then checked against existing records with at two different verifications being run.
Beyond that, I'm clueless. The vendor would not let me get a look at the stuff under the hood, which frustrated my boss and I to no end because it meant we couldn't check for optimizations.
On the other hand, I doubt it's an index issue as we reorged and rebuilt (depending on fragmentation levels) the indexes for other purposes between files and this did not make a difference. Also, table stats are updated every morning based on a vendor script.
During your next import you may want to monitor page splits. Extended Events may help there with impacting the process.
January 12, 2015 at 11:24 am
Lynn Pettis (1/12/2015)
Brandie Tarvin (1/12/2015)
Lynn Pettis (1/12/2015)
Brandie, just curious, when you are importing the file how is it inserted into the table(s)? Does the data go in sequentially on the clustered index? What I am wondering about is page splits and fragmentation.I would love to tell you. Except this process is a vendor process and everything works through SQL jobs and .bat files that call encrypted stored procedures. All I know is the file is pulled into a staging table and then checked against existing records with at two different verifications being run.
Beyond that, I'm clueless. The vendor would not let me get a look at the stuff under the hood, which frustrated my boss and I to no end because it meant we couldn't check for optimizations.
On the other hand, I doubt it's an index issue as we reorged and rebuilt (depending on fragmentation levels) the indexes for other purposes between files and this did not make a difference. Also, table stats are updated every morning based on a vendor script.
During your next import you may want to monitor page splits. Extended Events may help there with impacting the process.
I will definitely take a look at that. It would be nice to say that this issue isn't entirely due to mirroring.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply