December 6, 2017 at 9:37 am
We have an SSIS Package that will do a One-time load into our Data Warehouse and then CDC will provide the changes. We have been doing this in our test and staging environments and have tweaked the performance(i.e. SSIS Lookup Tasks etc, etc), however, there is a lot of data to load and we are concerned about running this one-time SSIS Package in a live Production Environment regarding the amount of memory we are currently using in production and what we can expect to be added onto the memory usage during this one-time SSIS Load of the Data Warehouse. Any prior experiences doing this would be greatly appreciated.
thanks in advance
December 15, 2017 at 10:13 am
When you say you have memory concerns is this because you're looking at perfmon or task manager stats? Keep in mind SQL will take all the memory it can up until the max memory setting or beyond if it's not set. That doesn't mean SQL doesn't have enough memory though. If you have memory pressure internal to SQL Server it'll show up as low Page Life Expectancy (PLE), Memory Grants Pending (these are perfmon counters), it will also show up as RESOURCE_SEMAPHORE waits. You might also see high physical IO waits (PAGEIOLATCH_*) in combination with low PLE.
My best recommendation is to take a backup of the production data you are going to run with and test the job in a lower life cycle (i.e. your test environment). Hopefully your hardware is somewhat similar in a lower lifecycle. I would also look at doing some level of monitoring of your prod server. Also, if this is a lot of data keep an eye on your transaction log. You want to be sure you don't run into space issues.
Good luck!
December 20, 2017 at 5:03 am
Depending on the size of the fact load and source you may able to break it up to load parts - you may find this is quicker that forcing it all in one. It really depends where your loading i.e to staging table i.e. no surrogate key insertion etc..
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply