August 26, 2020 at 1:10 pm
Hello Folks,
I have an SSIS package on a machine(SQL Server 2017, Windows Server 2016) that has user configured variables for table names and exports 3-4 tables from SQL Server database to a flat file and later zip it so essentially it is doing a select * from view (the view in question here is a simple select * from table with no joins).
The package is kept on a one of the disk drives on SQL Server and that folder is shared. The data flow as I explained simply exports the table to let's say W: drive.
Here are my observations:
When the SQL Server job is set to invoke the package (through file system option), the source query hammers the DB good enough but the destination is never able to keep up leading to Async_Network_IO wait. Perfmon and resource monitor tells me that it is never able to write at more than 8-10 MB\sec to the destination.
Same package with same options (picked from SQL Server Agent command line only) is run manually by opening a command prompt and invoking DTexec utility, the destination gives throughput of 25-30 MB\s consistently.
I did a lot of digging and even though there are SQL Server 2008, 2014 and 2017 versions installed on this machine, the SQL Server in question is 2017 and the SQL Server Agent or the manual DTexec version - both invokes the latest DTexec utility (found from file location in task manager).
This package was pretty old and I had upgraded it with VS 2017 and put target version as SQL Server 2017, also verified that package format version is 8 suggesting the package version is good.
As of now, I am not looking into statistics since this is a table export through a view (view definition is nothing but select * from table) so it has to do clustered index scan anyways.
I am not sure where to start looking since this does not look like a storage issue because the manual execution looks fine, SQL Server Agent has admin privileges everywhere.
Kindly suggest me why the behavior is different in two cases as described above. This has kept me curious enough for weeks now so looking for advise.
Regards
Chandan Jha
August 26, 2020 at 2:10 pm
Hello Folks,
Same package with same options (picked from SQL Server Agent command line only) is run manually by opening a command prompt and invoking DTexec utility, the destination gives throughput of 25-30 MB\s consistently.
Chandan Jha
do you mean that you RDP to the server and execute the package on the command line there?
or you do it on the command line on your own pc (or another server)?
and can you tell us the spec for the server (cpu/ram) as well as drive for data and output files - and are files written to a local drive or network share?
regarding the new package can you confirm which driver it is using - sqlncli or new one MSOLEDBSQL? if new one try and force it to use the old one to see if it improves.
August 26, 2020 at 2:33 pm
chandan_jha18 wrote:Hello Folks,
Same package with same options (picked from SQL Server Agent command line only) is run manually by opening a command prompt and invoking DTexec utility, the destination gives throughput of 25-30 MB\s consistently.
Chandan Jha
do you mean that you RDP to the server and execute the package on the command line there?
or you do it on the command line on your own pc (or another server)?
and can you tell us the spec for the server (cpu/ram) as well as drive for data and output files - and are files written to a local drive or network share?
regarding the new package can you confirm which driver it is using - sqlncli or new one MSOLEDBSQL? if new one try and force it to use the old one to see if it improves.
This is a windows 2016 cluster with with 2 nodes and each node has 765 MB RAM, 2 core with total 64 CPUs. SQL has been given max RAM till 600 GB and other than my job pretty much nothing else runs.
Thanks for replying.Yes, I login to the node and open command prompt and fire up dtexec. When job runs, same dtexec utility gets invokved. No remote server is involved here, it fetches the data from table on the SQL server running on same node and writes it to flat file(on another volume attached to same cluster).
The original package was old, I even upgraded it(so that connections use Native client 11.0) and tinkered with max buffer memory and max default rows. This tuning does hit the DB hard but since the flat file is never giving more than 10 MB\sec throughput SQL server keeps showing Async-Network-IO wait
Above symptoms does not appear running that manually from command line. The drive it writes to is another clustered drive but shared one and since no other remote server is involved, we ruled out any network latency to play foul.
August 26, 2020 at 5:45 pm
I would try and change from executing a SSIS step (which seems to be what you are doing) to execute a command line instead - see if it makes a difference.
I would also check that the current step is set to execute in 64bit mode
August 26, 2020 at 5:59 pm
I would try and change from executing a SSIS step (which seems to be what you are doing) to execute a command line instead - see if it makes a difference.
I would also check that the current step is set to execute in 64bit mode
I tried command line option too but same bad performance.
Also, it is executing in 64-bit mode. For 32-bit one has to explicitly check that box but that's not done.
August 26, 2020 at 6:19 pm
can't think of much more - except - is the output filename UNC or Drive letter based? if UNC change it to local drive .
August 27, 2020 at 1:59 pm
can't think of much more - except - is the output filename UNC or Drive letter based? if UNC change it to local drive .
Only the package and its configuration file is on UNC on same server, the paths which it pick dynamically from the database is hardwired to local drive notation. I have opened a case with MS on this.
September 22, 2020 at 4:18 pm
Even Microsoft has not been able to make any progress on this. We are stuck at the same point. SSIS is able to write to disk with 20+ MB\sec when ran manually from command prompt but with sql job or task scheduler it gets capped to 8 MB\sec.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply