Solved

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

Posted on 2014-12-02
10
404 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: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 47

Expert Comment

by:Vitor Montalvão
ID: 40478270
What's your DB recovery model? And what are you trying to do in SSIS?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

You might have come across a situation when you have Exchange 2013 server in two different sites (Production and DR). After adding the Database copy in ECP console it displays Database copy status unknown for the DR exchange server. Issue is strange…
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 steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
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 …

770 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