Wednesday, May 14, 2014

Make sure that you write/create optimized SSIS Packages

Recently I worked on the package that loops many text files and insert the data into a staging table and then from the staging into original table which has  five columns only defined as VARCHAR(50) in the database. Pretty simple , not? Text files have many data and the destination table has many columns (90+) and very wide data tape client defines (VARCHAR(3000)). Actually he needs to get only few columns but  who cares, right? We have observed that the memory during the package execution grow  rapidly and very quickly MSSQL Server service was become unavailabe. You can imagine that during this spike nobody can work and a local DBA started getting complains from the end users. I would like to say that the server is very strong (4CPU+ 28RAM)...So what's problem?

At the source component , the estimated sized of a row is determined by the maximum columns sizes of all columns returned by the query (remember we have 90+ columns defined as VARCHAR(3000)). This is where  the performance problem resides.

After sometime investigations we dropped the wide stage table and created a stage table with exactly same structure as original table has.
That means columns have VARCHAR(50) and we also changed the job schedule to run it frequently to deal with small data sets.  So performance was drastically improved. We have not seen any more memory growing and not reducing, there is no more compliance from the end user.

Please read this article if you deal with packages especially if it works with large data sets.
http://technet.microsoft.com/en-us/library/cc966529.aspx

12 comments:

lovesports said...

Hi, I enjoy reading your site! Is it okay to contact your through your email? Please email me back.

Thanks!

Cailyn
cailynxxx gmail.com

sriki said...

Hello,

I had specific question regarding SSRS and didnt know how to post it on your blog. Please let me know if i can post the question somewhere here.

Thanks
Srikanth

Chang bui thi said...

great post. i like it. feeling great when reading your post .
Signature:
i like play games friv4 online and play games 2 girls 2 Download baixar facebook movel

thị hậu nguyễn said...

Innovative responsive website design and contemporary layouts are sure to excite your users, making them regular visitors of your site and a long term client of your company.
happy wheels
super mario bros
pacman
agario

Online Game said...

You need to kill time, you need entertainment. Refer to our website. hope you get the most comfort.
Thanks you for sharing!
Yepi
Kizi
Friv
Friv Games

Croose Hackle said...

Arinetsupport.com serve the different choices about remote dba specialists, remote dba experts, remote dba bolster, remote database, prophet remote, dba remote, database organization, prophet dba bolster, Oracle backing and Oracle Consultants.

Hieu Nguyen said...

you'd have time to look these kids active. Please visit our website and let us play the game interesting.
Thanks for sharing !
Friv 10
Kizi 10
Yepi 2

Online Friv said...

You need to kill time, you'd have time to look these kids active. Refer to our website. Hope you get the most comfort.
Thanks for sharing !
Friv 5
Kizi 1
Yepi 3

Hieu Nguyen said...

When you're tired, you want to relax after a stressful working hours, you need to have time to take care of the kids active.
Please visit our website and play exciting flash games.Thanks you for sharing!
Friv 4

Kelly Anni said...

It is nice post. It is a great website and nice share. Click to play:
Science Kombat | Science Kombat Game | slither.io skins | slitherio | wings.io | wingsio
Play the fabolous earn to die game and complete all the levels.You can play all the seven parts of the game on our website :
Tank trouble | Tank trouble 2 | Tank trouble 3 | Tank trouble 4

Kizi200 Games said...

Play the Best Free Games! We've picked out the racing games, cooking games, candy crush, games shooting, fashion games, ...
Thanks for sharing !
Kizi 200
Friv 1
Y88
Y9

Lopez Alexander said...

This content is written very well. Your use of formatting when making your points makes your observations very clear and easy to understand. Thank you.
- usps tracking
- iphone 7 release date
- excel