AnsweredAssumed Answered

Azure SQL SDE fetch_buffer_size

Question asked by spiskula on May 9, 2020
Latest reply on May 22, 2020 by spiskula

Hello,

 

I have a SDE database stored in Azure SQL. In there I have several Feature Class with 100s of millions of features in each FC. I copy those to a local FGDB with use of arcpy.Append_management. I have observed that for various Feature Classes I get different data transfer speed at which the features are streamed from SQL to my VM. As part of investigation i noticed that after running this diagnostic SQL on the DB while the data is streamed:

 

SELECT *
FROM sys.dm_exec_cursors (0) c
CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t 

Cursors related to faster running transfers have higher value of  'fetch_buffer_size' column.

 

Although in all cases the data transfer is handled by arcpy.Append_management, requests for different FCs get different values of that buffer (20/40/70). Is there any way to impact/enforce higher value for that fetch_buffer_size parameter? All FCs sit in the same schema in the same database, yet transfer one FC is copied at 2M/s, the other 6MB/s, and I would like to understand how could i maximise the transfer speed.

 

Cheers

Outcomes