Solved

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

Posted on 2014-12-02
10
419 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher 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 49

Expert Comment

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

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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
 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

730 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