Solved

SSIS - Performance issues, is there a way to disable transactions?

Posted on 2014-12-02
10
378 Views
Last Modified: 2016-02-15
I have a SSIS project which, on my development VM running SQL 2012 Standard, is taking over an hour to run.  I stopped the job but the SSIS service did not release the 2.4GB or memory it  was using.

I then restarted the SQL Server service and that is taking a very long time; in excess of 15 minutes.   The only thing running on this server is the SSIS task.  What I'm wondering is if the SQL Server are doing some monstrous rollbacks.  If this is the case is there a way to turn off the transaction functionality to speed things up?

Also, as the DB is not used by anyone while the SSIS operation completes would there be any benefit to running the DB in single user mode?

I'm pretty desperate to speed this SSIS project up so any tips on debugging, identifying bottlenecks would be greatly appreciated.
0
Comment
Question by:canuckconsulting
10 Comments
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 100 total points
ID: 40477379
No, you cannot disable transactions.  All data mods in SQL Server are done within a transaction.

I think you can specify a batch size in SSIS, which would commit after that number of rows, making the transactions smaller.

For best performance, make sure before you run a big INSERT that you have plenty of log space available.  If necessary, pre-allocate log space to handle the INSERT(s).
0
 
LVL 10

Assisted Solution

by:Walter Padrón
Walter Padrón earned 100 total points
ID: 40477437
You can't disable transactions but you can BULK INSERT data, this is not always possible but when done is very fast.

This is an example for SQL 2008
 https://www.simple-talk.com/sql/reporting-services/using-sql-server-integration-services-to-bulk-load-data/

Best regards
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40478270
What's your DB recovery model? And what are you trying to do in SSIS?
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 300 total points
ID: 40478592
>so any tips on debugging, identifying bottlenecks

Remove all Blocking Transformations, such as sorts and aggregates.   If it is necessary to have data sorted because of JOINs, then sort it in the data sources and not in SSIS.

Check your data sources and limit the columns to only the ones you really need, i.e. a 100-column table where the package only consumes/inserts five columns and ignors the other 95.

If practical use derived column tasks / edit in Advanced Editor column data types to shrink them:  varchar instead of nvarchar, tinyint/smallint instead of int, varchar(100) to varchar(2) for a column like 'state code' that will only ever have two characters, etc.

What time of day are you running this package?  If after midnight it may be waiting for db backup/restores, or competing with other ETL packages for bandwidth.  Also if anyone/any process is using tables there may be locks on them that the SSIS package is waiting for.

Speaking of locks, if the package has multiple flows in the same tables, the package may be locking itself. Each destination task will have a checkbox 'Table Lock' that controls whether the insert locks the table or not.

Never hurts to 'log' each major step into a table, so you can view the metrics of each step and determine what needs to be improved.  I have an article in the works on this, but unfortunately nowhere near ready yet.

How many processors can you commit to this package?  If many and it's currently single threaded, change to multi-threaded.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40502625
Thanks for the split eh.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:canuckconsulting
ID: 40502666
Hi Jim,

Sorry to learn you are disappointed with distribution of points.

ScottPletcher advised that I cannot disable transactions which was  a direct answer to a question in my post.

Walter Padrón advised the same but also provided advice on a workaround, though not applicable in my case.

Your solution was by far the most helpful and which I indicated by both marking it as the Best solution and providing the majority of the points.

If you still feel the distribution of points was unfairly allocated please let me know why and if the system allows I would be more than happy to revise.  I appreciate your help and am sorry if the distribution of points caused offence.

Best regards,

Scott
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40502673
That was not an expression of disappointment, just a courtesy 'thank you' for splitting points among answers that helped you.
0
 

Author Comment

by:canuckconsulting
ID: 40502719
Oh dear.

Sorry my misunderstanding and thank you again!

Scott
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40502740
np.   I grew up in the Upper Peninsula of Michigan, and now live in Minnesota and play adult amateur hockey, so while 'eh' is a regular part of my vocabulary it probably loses a lot of context to where it's more frequently spoken.

Sorry eh.
0
 

Author Comment

by:canuckconsulting
ID: 40502790
I'm Canadian and should know better.

Keep yer stick on the ice!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Resolve DNS query failed errors for Exchange
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This tutorial will walk an individual through the process of installing of Data Protection Manager on a server running Windows Server 2012 R2, including the prerequisites. Microsoft .Net 3.5 is required. To install this feature, go to Server Manager…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now