none
SSIS running long time after moving from physical server to VM RRS feed

  • Question

  • Hi Experts, Kindly please help with issue i am having with SSIS running for ever.



    Prod Server: 
    Physical Machine-SQL 2012 Ent edition-256GB memory(186GB Assigned to SQL Sever), 36GB Page file(virtual memory). SQL Server, SSIS, SSRS-All 3 run on same machine.
    ETL run for 12 minutes 



    Future Prod Server:
    Virtual Machine-SQL 2016 Ent edition-200GB memory(120GB Assigned to SQL Sever), 36GB Page file(virtual memory). SQL Server, SSIS, SSRS-All 3 run on same machine.
    ETL run for 8 hours


    FYI- we had 120GB memory Initillay and we had memory issue/out of memory errors, so we made it to 200GB but still the ETL is running forever. 8 hrs.

    Things I did:
    Increased the buffer in the package
    Tweaked memory settings in SQL Server ( tried with assigned 100GB and 120GB, 150GB) to sql Server and nothing helped
    Restarted SQL SERVER
    Temp db is configured as per CPU  and we dont have any CPU hike 
    cost threshold for parallelism =5
    Changed Max degree of parallelism to 4 from 0
    tried EXEC sp_configure 'query wait', 20 ;  

    nothing helped.

    Kindly please provide some suggestions.

    Thanks, 


    Friday, November 15, 2019 5:56 PM

All replies