April 6, 2009 at 8:41 am
I have a series of SSIS packages that are transferring AS400 table structures to MS SQL2005.
The SSIS package structures are very simple.
1) Ensure a table exists and create if necessary
2) Transfer AS400 data to SQL table
3) Index SQL table.
The problem I have is that the transfer of data from the AS400 is painfully slow.
This has been an ongoing issue with SSIS.
However with SP3 the performance of our particular ETL went from poor to ridiculous.
As an example in SP2 the entire ETL process took 25 minutes (transferring approximately 10 tables totalling around 750 MB).
The day after the SQL Server was patched to SP3 the build time went to 16 hours! What!
I have scoured the release notes for any relevant changes to SSIS and seen nothing that hints at changes that might cause this.
It all comes down to the data transfer being terrible.
There are no fancy transforms going on.
The table structures were generated from the SSIS import wizard.
The source on the AS400 are SQL tables not views.
If anyone has any insight that would be great.
April 7, 2009 at 11:42 am
I also have a tier one application that has a process that runs nightly that is doing nothing more exciting than import, export, insert and updates in a nightly process that has nearly doubled in process time since applying SP3.
This is the same code that was running just fine a few weeks before SP3 was applied so the fingers are pointing at SP3 and the DBA's. I've had two SRX's open with Microsoft and they state that it's not their problem and that nobody else is complaining so it must be bad code. Obviously that isn't selling well to my developers.
Now there are two of us complaining.
I would love to see if anyone else has any metrics on before and after SP3.
Here's a little about my environment.
I have several x64 Bit servers that I have this problem on. The things they have in common are:
They are attached to an EMC DMX 2500
They are running Windows Server 2003 Enterprise Edition R2, SP2
They have 32 Gigs of RAM.
My server properties are:
Edition = Enterprise Edition
BuildClrVersion = v2.0.50727
EngineEdition = 3
ProductVersion = 9.00.4035.00
ProductLevel = SP3
ResourceVersion = 9.00.4035
Let me know if anyone else is experiencing performance degradation post SP3.
Bob
April 7, 2009 at 12:38 pm
I've had two SRX's open with Microsoft and they state that it's not their problem and that nobody else is complaining so it must be bad code. Obviously that isn't selling well to my developers.
It may not be Microsoft's problem and also not related to bad code I think SP3 takes SQL Server 2005 to .NET 3.5 SP1 which the IBM drivers are not aware of. So you may find out more if you talk to IBM, please post back so others can use it.
Kind regards,
Gift Peddie
April 7, 2009 at 12:53 pm
Well I got excited there for a minute Gift but after looking at my "add remove programs" I'm only at .Net Framework 2 SP1. And I'm on HP hardware.
But your post did get me wondering what other KB article patches may have been applied. So here's a list in case that helps.
Thanks!
Q147222
KB933854 - QFE
SP1 - SP
KB926601 - Update
KB925398_WMP64
KB914961 - Service Pack
KB921503 - Update
KB924667-v2 - Update
KB925336 - Update
KB925902 - Update
KB926122 - Update
KB927891 - Update
KB929123 - Update
KB930178 - Update
KB931784 - Update
KB931836 - Update
KB932168 - Update
KB932755 - Update
KB933360 - Update
KB933729 - Update
KB933854 - Update
KB935839 - Update
KB935840 - Update
KB936021 - Update
KB936357 - Update
KB936357-v2 - Update
KB936782 - Update
KB938127 - Update
KB938464 - Update
KB939653 - Update
KB941202 - Update
KB941568 - Update
KB941569 - Update
KB941644 - Update
KB941693 - Update
KB942615 - Update
KB942763 - Update
KB943055 - Update
KB943460 - Update
KB943485 - Update
KB944338 - Update
KB944533 - Update
KB944653 - Update
KB945553 - Update
KB946026 - Update
KB947864 - Update
KB948496 - Update
KB948590 - Update
KB948881 - Update
KB950759 - Update
KB950760 - Update
KB950762 - Update
KB950974 - Update
KB951066 - Update
KB951698 - Update
KB951748 - Update
KB952069 - Update
KB952954 - Update
KB953155 - Update
KB953838 - Update
KB954211 - Update
KB954600 - Update
KB955069 - Update
KB955839 - Update
KB956391 - Update
KB956802 - Update
KB956803 - Update
KB956841 - Update
KB957097 - Update
KB958215 - Update
KB958644 - Update
KB958687 - Update
KB960714 - Update
April 7, 2009 at 6:01 pm
Hey Bob,
I must say that it is a relief to hear that it is not just me!
I have been surprised that there are not more complaints, but my feeling is that most developers have given up on SSIS with the AS400 ODBC drivers.
There were a lot of posts around 18 months to 2 years ago on various forums regarding the poor performance of SSIS in compassion to DTS in this regard. All of these posts seem to have died without any real resolution, Microsoft's response to your issue may support this theory!
Our server specs are at a much lower level than yours.
We are still running 32 bit machines and OS and have 9GB on the servers.
OS is Win2003 RS SP2
SQL2005 is Standard edition patched to SP3.
As I indicated our build went from 25 minutes to 16 hours! I kid you not. I have had to stop the ETL process and only run it on the weekends which is driving our users crazy.
I was about to send this through to Microsoft, but I have to say I am feeling a little discouraged by your experiences. I will try the IBM route though and see what they have to say.
Thanks again.
April 8, 2009 at 7:32 am
Poor datatransfer speed may also happen if you connect to other sources than AS400.
If you any form of data encryption that will slow down the speed.
I gave up SSIS and use a work around which is to write out the content as a textfile and
then use bulk insert. I use VB.net for that. Hopefully this issue will be solved by MS and IBM
in corporation. To be honest I think SSIS for an oldtimer like me is a step back compared to
DTS or am I just oldfashion?
/Gosta
April 8, 2009 at 8:26 am
Okay folks I think we've been concentrating on just ETL but some research done last night showed a significant slowdown with some standard SQL reindexing too. I'd be interested in hearing if anyone else has tested similar functionality and had similar results. This is a process that normally takes 10 minutes and now takes 40.
Here's the test. (Abbreviated but the functionality portions are depicted below)
We loop through our tables and feed this info into a temp table and determine what needs reindexed based on fragmentation results. (The entire script is kind of long but this is the meat.)
DBCC SHOWCONTIG (' + @tableid + ') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'
Then based on matches for fragmentation we run this (there's some extra stuff in there for our logging but you'll get the gist. Essentially we're just running a DBCC DBREINDEX):
SELECT @username = user_name(objectproperty(@tableid,'ownerid'))
SELECT @execstr = 'DBCC DBREINDEX (' + '''' + db_name()
+ '.' + RTRIM(@username) + '.' + RTRIM(@tablename) + '''' + ')'
PRINT @execstr
+ ' ...Frag Level = '
+ RTRIM(CONVERT(varchar(15),@curr_frag_level)) + '%'
+ ' Starting: ' + cast(getdate() as varchar)
SET @StartTime = GetDate()
--Do the reindex here
EXEC (@execstr)
SET @EndTime = GetDate()
This is on a 360 Gig database and went from taking 10 minutes to 40 minutes. And there's no real ETL associated with it. After this is run we update statistics send a few email alerts but that's about it.
And the moral of the story is the only change on this box has been SP3....
April 29, 2009 at 5:14 am
Hey Bob,
I must say that it is a relief to hear that it is not just me!
I have been surprised that there are not more complaints, but my feeling is that most developers have given up on SSIS with the AS400 ODBC drivers.
Hello Bob and Newbie!
Here is another system engineer that's experiencing the exact same problems unfortunaly.
We have the same problem at a customer with a SQL 2005 box.
I checked all network connections and they appear to be good, then I tested/tryied the following things:
- Recreate transfer jobs in SQL
- Shrink databases/log files
- upgrade to SP3 (Yes, I had this problem also with SP2)
- Uninstalled all update's before 7-4-2009 (this is the date when the problem occured)
Then I saw this topic and unfortunaly you guys didn't find a solution to this problem.
gr.
Wesley Niels (MCSE, MCITP:EA)
April 29, 2009 at 9:19 am
I have the alerts set so that when someone posts to the thread I get an email. I'm afraid that since there had been a lack of posts I let my updates slip. So here it is maybe you'll find something you can use.
STORAGE
In our environment we utilize EMC as our SAN connection. It works well because we have a talented former EMC employee on staff. I've known several companies that have EMC products and the ones most enamoured with their SAN products all do have former EMC employees on staff. So to you who don't you should up your offer letters.
I'm saying that not to bash EMC in any way but to let you know that we have a higher skilled SAN tech on site so we were able to eliminate the physical SAN box early as the cause of our problem.
But I digress....
SAMPLES
We use the EMC Replication technology to replicate the database from our production cluster to 7 other servers for various reasons. Some of these have SP3 on them and some do not. Six are x64 bit proccessor machines and two are x86. But they have clones of the disks and databases and using Business Continuity Volumns (BCV's) or mirrored clones of the disks on each of the other 7 instances. So the disks, access to them and the physical media is as alike as you can get.
That said I got different results on 2 of the boxes and they were not the x86 machines. (In case you were wondering yes I use 64 bit SQL on the x64 bit boxes)
INVESTIGATION
The only delta's then were the hardware. To date we've found that we had to reboot the machine after applying SQL 2K5 SP3 and each of these boxes seems to have had some series of patches applied to them from our infrastructure group since we installed SQL on the box. Most of these without a reboot. And I understand that if the patch doesn't say it requires a reboot why be disruptive?
But we found AFTER the application of SP3 and the subsequent reboot. Let me say it again. AFTER the reboot these things showed up in the event logs and error logs. Before? nada...:
1 Server had a corrupt page file that had to be recreated.
2 servers had half a nic pair not respond when they came back up.
1 server had a bad power supply software patch that caused it to report a bad UPS and was filling up the event queue
When the tech came out to check out the software he actually did a physical exam on the power supply and when he left we found that the HBA cards had backed out so we had disk issues. Those got reseated and that server is fine now.
TESTS
We went with reindexing and DBCC CHECKDB as our tests and found that only one server had and still has the problem. At this point we're still looking into it but we're convinced that the issue is apparent on only one server now and that the seeming correlation that we were experiencing with SP3 was just hardware that needed to be inspected closely after a bounce.
PROCESS CHANGE
Everything is a learning experience when you work in a culture of continual improvement right? AKA Lemonaid time....
So now our process prior to installing any patch is a thourough review of the SQL Logs, event logs and any other logs that might be lying about in the forest of data. Then we bounce the box and review logs again. If we can then say that it bounces clean we will inflict our patch on the server. When it's done whether it needs it or not we bounce again and review logs again. At this point we're reviewing more logs than the lumberjacks. But we believe that it will make patching a more repeatable and secure process. And will make sure that when fingers get pointed at our patch we've had ample opportunity to ensure the hardware was without fault.
I'm anxious to hear if anyone else has had experiences similar or contrary.
Bob
May 4, 2009 at 1:25 am
Hi Bob Lee,
Thanks for your reply!
We haven't tried the things you did and my SQL knowledgde doesn't go that deep.
Unfortunaly we still didn't solve the problem, but is there a way to create a work-a-round for this problem. Forum Member SSC-Enthusiastic mentioned that the SSIS technique is very old and there are other technique's to transfer files between an AS400 and SQL Server.
An other thing is that I don't think it's an coincidence that we have the same problems on the same date, has there been a change after installating SQL update's?
gr.
Wesley Niels (MCSE, MCITP:EA)
May 4, 2009 at 6:31 am
Too early to tell Wesley. We just had the hardware looked at this weekend when the machine blue screened. If there are any changes I'll let you know.
Bob
July 29, 2009 at 2:59 pm
Bob -
Any update?
We do AS400 to SQL server, and I'm going to start looking at SP3 prior to SQL 2008 upgrade.
Thanks, Greg E
July 30, 2009 at 9:09 am
I may be in over my head here, and I certainly dont have the
hardware knowledge that the rest of you do but...
I had to prove to mgmt the reasons why I did not want to access
our AS400 production system directly via SSIS for my data warehouse project.
So I ran some tests using the ODBC driver via an ADO.NET connection and
it was pretty slow - 1/2 hour to read - read alone and nothing else - for
1 million records. Since I need 500 million for a fact table load and would
never want to use this for change data capture my messgae was well-received.
Anyway...in the process of all this I found there is an OLEDB for DB2 provider that
is supposed to be much faster for SSIS to use. I believe it does require some
work on the AS400 side for a patch or whatnot, but I thought I'd toss this on this
post as I didnt see anyone else mention it.
July 30, 2009 at 10:09 am
So the quick answer is that we found no problems we could directly attribute to SP3. However this has been a protracted problem and a lengthy examination of indexes, triggers and query costs. There was apparently something different in SP3 in how it handled query optimization and statistics because after we rebuilt all of our indexes, removed duplicates and began a daily update of statistics on some key points the business process owner has relented that it was their code that was at fault. That said we also ended up rebuilding a cluster and retiring a box in the process.
What is unique is that this is a database where we bcv replica's of the database to 7 other servers and it responded inconsistently on them.
So the less informative answer is that SP3 is no longer the scapegoat and we've moved on for now.
July 30, 2009 at 11:45 am
I'll have to do a little more pre / post SP testing than I thought. Sounds like nothing was determined for sure. We have a little room in our nightly window, and were hoping not to lose anything.
We have never had an issue with AS400 to SQL Server speed. The only thing we found real slow, and didn't troubleshoot much, was linked servers. And as the project got larger, we had to control the flow / split some things apart a bit.
Thanks for the response.
Greg E
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply