I have a setup where there is SQL Server on premises (MS SQL Server 2016 13.0.5366.0) but database files (log and data) are stored in cloud storage account. Setup described here.
The interesting part is the performance of physical reads of data. Basically i am executing very simple select statement from table and cleaning buffers/cache before running it.
If i read data in single thread query from table and physical reads are needed i get maximum network utilization never exceeding 32 Mbps- much slower than i expect
If i read data in many threads that also requires physical reads i get maximum network utilization close to 500 Mbps that is something that i would expect.
Trying to understand:
Why sequential execution are not reading data faster? Is there some limits of max read performance for single thread?
Why query is not starting to work parallel so that multiple threads would read same data file (and in that way achieving higher performance)?
Did try splitting data to multiple data files- no affect.
Did try query hints (query was still not executed in parallel)
OPTION(QUERYTRACEON 8649) OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))