We help IT Professionals succeed at work.

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

693 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.
Comment
Watch Question

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
What's your DB recovery model? And what are you trying to do in SSIS?
SQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Thanks for the split eh.

Author

Commented:
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
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
That was not an expression of disappointment, just a courtesy 'thank you' for splitting points among answers that helped you.

Author

Commented:
Oh dear.

Sorry my misunderstanding and thank you again!

Scott
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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.

Author

Commented:
I'm Canadian and should know better.

Keep yer stick on the ice!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.