Solved

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

Posted on 2014-12-02
10
394 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 46

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…
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…

911 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

26 Experts available now in Live!

Get 1:1 Help Now