SNOW-1786191: SNOW-1747415 High Memory Usage SFReusableChunk

[hadighattas]

1. What version of .NET driver are you using?
   4.1.0 also tried to build and use latest version of master including fixes mentioned in SNOW-1612981: SNOW-1640968 SNOW-1629635 Massive Memory Load  #1004

2. What operating system and processor architecture are you using?
   macOS 14.7 ARM

3. What version of .NET framework are you using?
   .net standard 2.0

4. What did you do?

Pulling a lot of data is memory intensive.
We tried pulling 100M rows and the memory usage is averaging around ~800-900MB for a unit test, forcing garbage collection does not change anything.
This test is using the dapper unbuffered API which fully supports streaming.
The memory profiler is indicating that almost all of the objects allocated are in SFReusableChunk BlockResultData.

Profiler screenshots

Reproducing this issue is pretty straightforward, we pulled 100M records using this query of sample data

SELECT * from snowflake_sample_data.TPCDS_SF100TCL.inventory

5. What did you expect to see?

Memory buffer sizes (chunks) should be more conservative/configurable.

6. Can you set logging to DEBUG and collect the logs?

Reproducing this is pretty straightforward. I can do it necessary.

[sfc-gh-dszmolka]

hi - this issue looks awfully similar to the ones we have in the

• Node.js driver - SNOW-1747415 SNOW-1215393: Out of Memory Issue in memory-limited environment while streaming data snowflake-connector-nodejs#790
• JDBC driver - SNOW-972142: SNOW-1747415 The CLIENT_RESULT_CHUNK_SIZE parameter is not taken into account in different ways of executing the request snowflake-jdbc#1563
• Python driver - SNOW-899773: SNOW-1747415 Allow specification of of batch_size for batch-generating functions snowflake-connector-python#1712

because the issue is coming from the backend; currently the chunks sizes are not really configurable and if you're retrieving a huge amount of data, then it will be memory intensive (or crash with OOM, depending how limited the memory is)

You can give setting CLIENT_RESULT_CHUNK_SIZE=16 a shot [reference(https://docs.snowflake.com/en/sql-reference/parameters#client-result-chunk-size)] but I can imagine it won't change the situation very much.

What you can use as a workaround while the backend issue is sorted out, and should be working, is to use the LIMIT ... OFFSET ... argument to your select, to 'paginate' through the huge resultset, if you need to bring down the memory usage under a certain level.

[sfc-gh-dszmolka]

hi @hadighattas there's something you can try, maybe it helps. In 4.2.0 driver version we improved memory usage in SFReusableChunk. Can you please upgrade to 4.2.0 or higher and see if the issues cease? Thank you for the feedback in advance!

[hadighattas]

Hello @sfc-gh-dszmolka
Thanks for your response!
I tried with both CLIENT_RESULT_CHUNK_SIZE and upgrading to 4.2.0, in both cases the memory usage for the test I had is the same as for 4.1.0 😕

[sfc-gh-dszmolka]

thank you for testing and the feedback! this was necessary to confirm and rule out other reasons , besides what I originally suspected (see my first response). We'll need time to address this, on the backend.