Windows 2016 Server Write Throughput

Question

Hello Folks,

I have a windows 2016 standard edition 3 node-cluster. We have clustered SQL(2017 developer edition,SQL+SSIS) installed on this. There is a simple package which exports the data from this SQL server to a disk on the same cluster. Here are the observations:

• Scenario-1 :When I run that package manually, I get good write throughput
• Scenario-2 :When SQL job or task scheduler runs the package, the write throughput gets capped around 7-8 MB\second. Additionally ,I also observed that during this time, a lot of data gets written to c:\Users\service-account\app data folder which suggest that the SQL is sending the data fast enough but something is throttling the writes to the disk causing SSIS to buffer the data locally on disk.
• No memory pressure or disk latencies observed in both cases
• SQL agent and service account has full access on OS level.

Someone suggested that command prompt has higher priority so it gives better write throughput but I don't agree to that. I wanted to know from windows experts if there is some setting either on security or SMB or anything that I can tune to get good throughput.

Regards
Chandan Jha

Answer 1

We finally solved the issue. The problem was the dtexec.exe for that version of SQL 2017 that was causing the issue of slow throughput when invoked by agent job. Two solutions were tried, either apply the latest CU(not available when I had the issue) or force SQL Server to use a specific version of DTEXEC which has been known to work well. Microsoft engineer said it probably is a bug and he will report it.

I hope it helps someone in future.

Please note that windows version had no role to play here as I was suspecting earlier.

Answer 2

Hi @Jha, Chandan Kumar ,

The following links will be helpful:
Data Flow Performance Features
Integration Services Performance Best Practices – Data Flow Optimization
Integration Services Performance Best Practices – Writing to the Destination

Best Regards,
Mona

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @Jha, Chandan Kumar ,

Hope the following links will be helpful:
Install SQL Server with SMB fileshare storage
Using SMB fileshare as a storage alternative

Best Regards,
Mona

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.