as400, ssis, checkpoint

Last Post 30 Jan 2008 12:29 PM by rthames. 13 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
rthames
New Member
New Member

--
28 Jan 2008 11:27 AM
Currently we are conducting a data pull from our as400 into our SQL server. I have created an SSI package that loads the data. The package reads off of my SQL tables what tables and columns from the as400 to pull. It then creates packages specific to the tables and runs them (uses ODBC driver). Once it has loaded data into my "staging" table the same process repeats except for it goes from my SQL "staging" database to a "live" SQL database (provider SQLNCLI.1). This seems to work fine and has been working great for a few months. Now I am getting all kinds of checkpointing going on with the databases when the data loads. The databases are bulklogged and the logs are of good size. I am running log backups against the databases and CHECKDB against them and everyting seems to be fine with the maintenance of the db's. The problem is that it takes forever now for the jobs to compelte. They used to complete in about 50 minutes now it takes 1.5-2 houts for them complete. I see that a lot of checkpointing is going on on the databaes that did not seem to be happening before, but I have not found a way to stop that. Is there a way to stop that? I grew the logs to be twice of what the size was to see if that alleviated the checkpointing and sped things up but it does not seem to have changed anything. Any thoughts? Any thing else that I should be looking at?

Thanks in advance..
rthames
New Member
New Member

--
28 Jan 2008 01:10 PM
yes there are indexes...rebuilt them this weekend...for the longest time while it was in testing it never had the indexes rebuilt on it. I just recently started rebuilding them.
rthames
New Member
New Member

--
29 Jan 2008 03:01 AM
here is the oddest thing of all...I decided to experiment so I deleted the database and readded everything and it ran just fine, the first time. The second time it ran very slow and had tons of checkpoints. I just do not get it. Why would it run fine once but not the second time. To me it does not make any sense.

TIA
rthames
New Member
New Member

--
29 Jan 2008 05:59 AM
the log did not to be extended I did that just to make sure that I was not hitting a 70% threshold and creating a checkpoint. I truncate the tables before each load because they are essentialy a read-only version of what is in our as400. There are about 40 tables that I load this way. If it is doing a minimally logged load instead of a bulk logged load then would that create a checkpoint after each SSIS package? I am wondering if I need to change the way that my package runs...instead of having code that ganerates each table as a seperate package have it all run together as a single package. I will read up onthe minimally logged BOL...any other thoughts? How do other people pull from the as400 or other sources?

TIA
rthames
New Member
New Member

--
29 Jan 2008 06:37 AM
Yes the DB's are right now all simple (I had the staging DB as the only simple one but I was just experimenting with speeding this up so I changed them to all simple). For the most part the tables on the 400 do not have any primary keys so we are just truncating the tables and doing a reload everytime, our tables on the 400 are quite the mess. These are just straight inserts. The tables do not hold any other data except for what we pull from the 400. Then we make some changes to the data and create some reporting tables.

I will try only using the SSIS packages from the 400 and then using SQL statements from there...that might do the trick.

Thanks, it is apprecaited.
rthames
New Member
New Member

--
29 Jan 2008 08:04 AM
At first it was to minimize down time for the "live" database but I think that we over thought this. At first we loaded data into a staging table then loaded to other tables from it using a switch to direct the applictions that used it. I think that from what we know now we can load the data in to 1 table in the AM, have a copy of the table and then reload it again at say noon. We have the applications so that it is set to look at a flag so that it make the connection to the "live" database. Maybe we do just change it so that it goes straight in.
rthames
New Member
New Member

--
30 Jan 2008 02:26 AM
Currently I am trying to have the jobs go to a staging database and then go to the "live" database. Since it is a read only database I am trying it with everything set to simple recovery. Just to see how well it can perform. I am adding about 10 million rows across about 40 or so tables so I do not think that this is that big. It could be a lot worse. The things that seems oddest to me is that I can run the jobs and they will run quick, I can reset the schedule (not touch anything else) and they will crawl and I can see the checkpoints continously happening, reset the schedule and the next time they will run quick again. I just do not understand why it seems to be inconsistent.

Thanks for the help and thoughts.

SQLUSA
New Member
New Member

--
30 Jan 2008 03:44 AM
The jump in execution time likely size related.

Is there an ongoing growth?

Indexes get fragmented during the upload? Test for fragmentation before and after.

Can you drop the indexes, upload, rebuild them?

Turn on SQL profiler to trace what is going on.

Kalman Toth
http://www.sqlusa.com/order2005highperformance
rthames
New Member
New Member

--
30 Jan 2008 06:08 AM
It is a cluster with 2 physical boxes, the boxes are 8 way w/ 16GB RAM...there are not a lot of other applications that use the SQL server and those that do are not very robust. most are not even over 50 MB and there are only one or two that are even over 100 MB. most have been out there for a year or two with no archiving.
Yeah I reset the job schedule so that the agent would run it. Last night it ran fast and I did something just testing things out...I dropped all the tables (which I think also drops the indexes) Readded all of the tables and the indexes...did the data load and it ran in 30 minutes....this AM the same exact job took 2 hours. It ran late last night so no one else should have been on the system and then it ran early this morning so no one else should have been on the system. I guess I will be setting up some traces and see what I can find.

TIA
SQLUSA
New Member
New Member

--
30 Jan 2008 06:57 AM
I it feasible

1. Drop indexes
2. Run the ETL job
3. Put indexes back

on ongoing basis?

Kalman Toth
http://www.sqlusa.com/order2005ssis
rthames
New Member
New Member

--
30 Jan 2008 09:32 AM
MAX memory on the processor tab is set to 2147483647. I have tested it the following ways :

    dropped the tables and recreated them, add indexes , loaded the data
    dropped the tables and recreated them, loaded the data, create the indexs
    dropped the indexes, loaded the data, and then recreated the indexes

and I get the same results...the first time it runs fast but the second time it crawls. I am in the process of running it just dropping the indexes loading the data and recreating the indexes while I monitor the server to see what else is going on.

TIA
SQLUSA
New Member
New Member

--
30 Jan 2008 09:41 AM
That is ODD!

When you drop indexes, INSERT should be fast.

Can you try BULK INSERT task?

Can you list your table schema with indexes? What is your clustered index on?

Kalman Toth, Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005grandslam/
rthames
New Member
New Member

--
30 Jan 2008 12:29 PM
The server is not using AWE. The files have all just been broken out. When I first got here the SQL boxes had two drives...C and D with D holding all of the data and log files. I have already split out all of the data files and log files onto seperate drives. I even have seperate drives just for the data files involved in this process to sit on. It is 32 bit STD ed.
I am running some more test now to see if I can find out anything different...if it is the memory then wouldn't it act the same at all times.

TIA
rthames
New Member
New Member

--
31 Jan 2008 06:28 AM
Actually according to MS the 2005 STD edition limit memory is the OS max...( http://www.microsoft.com/sql/prodin...tures.mspx ) ..not sure if it is correct but that is what they say... I did not go in to detail about the drives being hooked into our SAN...there are 6 drives coming off of our SAN for the cluster to use...that was the first thing I did when I got here. I inherited the setup and have been trying to make changes as we go along as well as develop applications for the intranet. Our OS is Windows Server 2003 R2 service pack 2. They are acutally adding another drive this weekend so that I can put tempdb by itself.
You are not authorized to post a reply.

Acceptable Use Policy