ODBC DRIVER + SQL SERVER Provider ran out of memory issue

Bruno Rafael Ma
Kilo Contributor

‎05-05-2023 09:09 AM

Hi Everyone,

I am running a VM with Windows Server 2019 + MS SQL SERVER 2019 - 64bit, 8Core 32GbRAM, 500Gb Disk Space

 

I am facing some issues with SQL Server 2019, using ODBC Driver (provider MSDASQL) running out of memory.

 

I am running OPENQUERY like SELECT * FROM OPENQUERY('remote_server', 'select column1, column2, column3 from schema.target_table where created_at>''2023-03-14 00:00:00'' and ''2023-03-15 23:59:59''');

 

This query worked a few times and then I started to get the "provider ran out of memory" error message

Msg 7399, Level 16, State 1, Line 2

The OLE DB provider "MSDASQL" for linked server "remote_server" reported an error. The provider ran out of memory.

 

My intention is to collect some data from the remote_server and store it in a table in SQL Server. I was able to get about 100k (we have around 1M records) records from the remote table and the provider ran out of memory started.

 

I am able to run the query using the interactive SQL from the ODBC driver outside the SQL Server which makes me believe there is some buffer or cache full in the SQL Server provider because I noticed each time doing the queries tried to set 6 months period then, 3 months...1month and now barely a 24hours period work.

It seems this is feeling like a space used by openquery to temporarily store the SELECT but I did not found anything about it so far, where is getting filled or how to clean up this "memory space".

 

I have tried to clean the SQL server cache and buffer but did not work.

 

Also, the max memory is set to 20GbRAM and is using only 4Gb so the physical memory should not be a problem here.

 

Does someone has some suggestion or went through the same issue before?

PS: I have tried to restart the SQL Server and the VM multiple times.

Labels:
0 Helpfuls
  • 1,698 Views
1 REPLY 1

Ratnakar7
Mega Sage
Mega Sage

‎05-17-2023 10:55 PM

Hi @Bruno Rafael Ma ,

 

Here are a few suggestions to address this issue:

  1. Increase the memory allocation for the SQL Server instance: Although you mentioned that the max memory is set to 20GB RAM, it's worth double-checking the configuration to ensure it's correctly set. You may consider increasing the memory allocation if your server has available resources.

  2. Optimize the query: Since you mentioned that reducing the time period of the query improved the situation, it's possible that the query is processing a large amount of data and consuming excessive memory. Try optimizing the query to retrieve and process only the necessary data. This could involve refining the WHERE clause, using appropriate indexes, or considering alternative query approaches.

  3. Split the query into smaller batches: Instead of retrieving all the data in a single query, consider splitting the query into smaller batches. You can fetch a portion of the data at a time, process it, and then repeat the process until all the data is retrieved. This can help alleviate memory constraints.

  4. Adjust ODBC driver settings: Check if there are any specific settings or configurations for the ODBC driver that can be adjusted to optimize memory usage. Refer to the documentation or support resources for the specific ODBC driver you are using.

  5. Monitor server resource usage: Monitor the resource usage of your SQL Server instance, including memory, CPU, and disk usage, during the execution of the query. This can help identify any potential bottlenecks or resource constraints that may be causing the issue.

  6. Consider alternative data transfer methods: If the above approaches don't resolve the issue, you might consider alternative methods to transfer the data from the remote server to SQL Server, such as using SSIS packages or other ETL (Extract, Transform, Load) tools. These tools provide more control over the data transfer process and can help optimize memory usage.

 

Thanks,

Ratnakar

0 Helpfuls