What is SQL Server R Services

Machine Learning Services is also available in Azure SQL Managed Instance. It is not part of Azure SQL Database.

How it works

sp_execute_external_script does not run R or Python inside the SQL Server process. The Database Engine hands the script to the SQL Server Launchpad service, which starts a separate R or Python process under a low-privilege account, streams the input rows to it and reads the result rows back.

Inside the script, two names matter:

• InputDataSet: a data frame holding the rows returned by the query you pass in @input_data_1.
• OutputDataSet: the data frame your script must assign if you want rows back.

Everything else is ordinary R or Python, including any package installed in the instance's library.

Enable it

The steps below are for SQL Server 2022 and later on Windows. On 2016 to 2019, Setup installs the runtimes for you and you can skip to step 4.

1. Run SQL Server Setup, choose New SQL Server stand-alone installation or add features to an existing installation, and on the Feature Selection page tick Machine Learning Services and Language Extensions under Database Engine Services.

2. Install the runtime. Microsoft's instructions for SQL Server 2022 call for R 4.2 or Python 3.10, installed for all users, not the newest release of either.

3. Install Microsoft's RevoScaleR (R) or revoscalepy (Python) package into that runtime and register the runtime with the instance using RegisterRext.exe, which ships inside the package. The package download URLs and the dependency list change, so copy them from the install guide linked at the end. Registration looks like this for a default instance:

   cd "C:\Program Files\R\R-4.2.3\library\RevoScaleR\rxLibs\x64"
   .\RegisterRext.exe /configure /rhome:"C:\Program Files\R\R-4.2.3" /instance:"MSSQLSERVER"
   

4. Turn on external scripts:

   EXEC sp_configure 'external scripts enabled', 1;
   RECONFIGURE WITH OVERRIDE;
   

5. Restart the SQL Server service. That restarts Launchpad as well.

6. Verify:

   EXEC sp_configure 'external scripts enabled';   -- config_value and run_value should both be 1
   
   EXEC sp_execute_external_script
        @language = N'R',
        @script   = N'print(R.version.string)';
   

   The R version string appears on the Messages tab. For Python, use @language = N'Python' and @script = N'import sys; print(sys.version)'.

Run a script against table data

Create a small table to work with:

CREATE TABLE dbo.Orders (
    OrderId  int IDENTITY PRIMARY KEY,
    Region   varchar(10)   NOT NULL,
    Amount   decimal(10,2) NOT NULL
);

INSERT INTO dbo.Orders (Region, Amount) VALUES
('North', 10.00), ('North', 20.00), ('North', 60.00),
('South', 15.00), ('South', 45.00);

Pass a query in, compute in R, get a result set back:

EXEC sp_execute_external_script
     @language     = N'R',
     @script       = N'
        OutputDataSet <- aggregate(Amount ~ Region, data = InputDataSet, FUN = median)
     ',
     @input_data_1 = N'SELECT Region, CAST(Amount AS float) AS Amount FROM dbo.Orders'
WITH RESULT SETS ((Region varchar(10), MedianAmount float));

Region     MedianAmount
---------- ------------
North      20
South      30

The same thing in Python uses pandas:

EXEC sp_execute_external_script
     @language     = N'Python',
     @script       = N'
OutputDataSet = InputDataSet.groupby("Region", as_index=False)["Amount"].median()
     ',
     @input_data_1 = N'SELECT Region, CAST(Amount AS float) AS Amount FROM dbo.Orders'
WITH RESULT SETS ((Region varchar(10), MedianAmount float));

Three details save time:

• Python is indentation-sensitive. Start the script text at column one inside the string, as above.
• WITH RESULT SETS names the output columns. Without it the columns come back unnamed.
• Not every SQL type maps cleanly. Cast decimal, money and similar types to float in the input query, and avoid passing types the runtime has no equivalent for, such as datetimeoffset or sql_variant.

To pass scalar values, declare them in @params exactly as you would with sp_executesql. The usual production pattern is to train a model in one procedure, store the serialized model in a varbinary(max) column, and score new rows in another procedure. Models trained with RevoScaleR or revoscalepy can also be scored by the T-SQL PREDICT function, which does not start an external process.

Permissions

A database user who is not db_owner needs one extra permission to run scripts, plus the usual permissions on the tables the input query reads:

GRANT EXECUTE ANY EXTERNAL SCRIPT TO [reporting_user];

Common errors

"'sp_execute_external_script' is disabled on this instance of SQL Server." The external scripts enabled option is off, or you changed it and have not restarted the service. Check that run_value is 1.

"SQL Server was unable to communicate with the LaunchPad service." The Launchpad service is stopped, or the feature was never installed for this instance. Check it in SQL Server Configuration Manager.

"A 'R' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004." The R or Python code itself failed. The real error is in the lines after this message.

A package is "not found" even though you installed it. Packages must be in the library of the runtime that is registered with the instance. Installing to your own user library, or to a second copy of R or Python on the same server, does not make a package visible to SQL Server.

Edition limits

Every edition, including Express, can run R and Python scripts. On Standard and Express the integration is limited to two cores and to data sets that fit in memory. Enterprise edition removes both limits.

When not to use it

Machine Learning Services is the right tool when the data must not leave the database server, or when you want a scoring step to live in a stored procedure next to the rest of your T-SQL. It is the wrong tool in several common situations:

• The database server is already busy. External scripts use CPU and memory on the same machine as your transactional workload. Resource Governor's external resource pools can cap them, but the contention is real.
• You need current runtimes or GPUs. You are tied to the R and Python versions Microsoft supports for your SQL Server release, and the feature is not built for GPU training. Train elsewhere and bring only the model, or only the predictions, back.
• The calculation is simple. Medians, percentiles, moving averages and linear trends can be done with window functions and PERCENTILE_CONT in plain T-SQL, with no extra service to patch.
• You are on Azure SQL Database. The feature is not there. Use a managed instance, or run the code in an application or a notebook that queries the database.
• Nobody will own it. Each cumulative update, runtime upgrade and package install is now part of your database change process. If your data scientists already have a platform they deploy to, a second one inside the database rarely pays for itself.

To switch the feature off again, set external scripts enabled to 0, run RECONFIGURE, and restart the service.

Further reading

• What is SQL Server Machine Learning Services?
• Install SQL Server 2022 Machine Learning Services on Windows
• sp_execute_external_script (Transact-SQL)

What causes slow queries in SQL Server, and how do I fix them?

A diagnostic catalogue of slow query causes in SQL Server: how to tell running from waiting, the plan or wait signature of each cause, and the fix.

#execution-plans#performance#troubleshooting