Windows 2016 Server Write Throughput

Jha, Chandan Kumar 191 Reputation points
2020-09-23T18:58:20.573+00:00

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

Windows Server 2016
Windows Server 2016
A Microsoft server operating system that supports enterprise-level management updated to data storage.
2,377 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,756 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,456 questions
0 comments
Accepted answer
  1. Jha, Chandan Kumar 191 Reputation points
    2020-10-01T17:10:02.717+00:00

    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.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Monalv-MSFT 5,896 Reputation points
    2020-09-24T06:26:21.837+00:00

    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.

  2. Monalv-MSFT 5,896 Reputation points
    2020-09-25T09:57:57.653+00:00

    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.

    0 comments