?
Solved

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

Posted on 2014-12-02
10
Medium Priority
?
468 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 400 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 400 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 51

Expert Comment

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

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 66

Accepted Solution

by:
Jim Horn earned 1200 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 66

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 66

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 66

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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

OfficeMate Freezes on login or does not load after login credentials are input.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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…

762 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