We recently upgraded from SQL Server 2012 to 2016 and have noticed it takes longer than we expected to insert from one database into the other. See below for details and specific question
• SQL Server 2016 (SP1-CU6)
• Virtual Server (32 processers and 32GB RAM)
• Windows Server 2012 R2
• 1 instance of SQL server
• Database A (compatibility =130)
• Database B (compatibility =130)
• Table Statistics in database A up-to-date
SSIS package runs Stored Procedure on DatabaseA (Estimated Cost = 2750) (35Million rows returned from query)
Loads to table in DatabaseB. Table has 2 indexes, one clustered another non clustered.
Stored Procedure returns 25 columns. Most are integers, money or small varchars (255) is the biggest varchar.
When deployed to the server, the package take over 30 minutes to run.
In SSIS we have tried “AutoAdjustBufferSize” = True
And DefaultBufferMaxRows = 10,000
AutoAdjustBuffer size seems to be slower than using DefaultBufferMaxRows = 10,000.
Is there anyway to make this run faster?
What are some possible things to check which might cause it to run slowly?