MemoryError: Microsoft ML Services with Python

Question

The following script works well is small samples

EXECUTE sp_execute_external_script
@language = N'Python',
@script = N'
print(df_training["flResp"].value_counts())',
@input_data_1 = N'SELECT * FROM tb_training_teste',
@input_data_1_name = N'df_training';

I tested with 8419 records and the results is OK, like as following:

Mensagem(ns) STDOUT do script externo:
0 4964
1 3452
9 3
Name: flResp, dtype: int64

But, my original table has more than 500,000 records and I could not run because the following error. Could someone help indentify what's wrong? And how to fix it?

Error in execution. Check the output for more information.
MemoryError

SqlSatelliteCall error: Error in execution. Check the output for more information.
Mensagem(ns) STDOUT do script externo:
SqlSatelliteCall function failed. Please see the console output for more information.
Traceback (most recent call last):
File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 587, in rx_sql_satellite_call
rx_native_call("SqlSatelliteCall", params)
File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 358, in rx_native_call
ret = px_call(functionname, params)
RuntimeError: revoscalepy function failed.

score 0 · Answer 1 · answered Sep 07 '21 at 04:37

I just hit this problem, so I'll add my findings in case it helps someone in future.

It seems SQL server uses resource pools to limit the resources available to external processes like Python and R. The allocations can be viewed with

SELECT * FROM sys.resource_governor_external_resource_pools

By default, there'll be one pool (called default) with a max_memory_percent of 20. That can be increased using the ALTER EXTERNAL RESOURCE POOL command, for example:

ALTER EXTERNAL RESOURCE POOL [default]
WITH (
    MAX_MEMORY_PERCENT = 95
)
GO

ALTER RESOURCE GOVERNOR RECONFIGURE
GO

Alternatively, it can be altered in SQL Server Management Studio:

MemoryError: Microsoft ML Services with Python

1 Answers1

Linked