Monitor SQL Server Machine Learning Services using dynamic management views (DMVs)

Applies to: SQL Server 2016 (13.x) and later Azure SQL Managed Instance

Use dynamic management views (DMVs) to monitor the execution of external scripts (Python and R), resources used, diagnose problems, and tune performance in SQL Server Machine Learning Services.

In this article, you will find the DMVs that are specific for SQL Server Machine Learning Services. You will also find example queries that show:

• Settings and configuration options for machine learning
• Active sessions running external Python or R scripts
• Execution statistics for the external runtime for Python and R
• Performance counters for external scripts
• Memory usage for the OS, SQL Server, and external resource pools
• Memory configuration for SQL Server and external resource pools
• Resource Governor resource pools, including external resource pools
• Installed packages for Python and R

For more general information about DMVs, see System Dynamic Management Views.

Tip

You can also use the custom reports to monitor SQL Server Machine Learning Services. For more information, see Monitor machine learning using custom reports in Management Studio.

Dynamic management views

The following dynamic management views can be used when monitoring machine learning workloads in SQL Server. To query the DMVs, you need VIEW SERVER STATE permission on the instance.

Dynamic management view	Type	Description
sys.dm_external_script_requests	Execution	Returns a row for each active worker account that is running an external script.
sys.dm_external_script_execution_stats	Execution	Returns one row for each type of external script request.
sys.dm_os_performance_counters	Execution	Returns a row per performance counter maintained by the server. If you use the search condition WHERE object_name LIKE '%External Scripts%', you can use this information to see how many scripts ran, which scripts were run using which authentication mode, or how many R or Python calls were issued on the instance overall.
sys.dm_resource_governor_external_resource_pools	Resource Governor	Returns information about the current external resource pool state in Resource Governor, the current configuration of resource pools, and resource pool statistics.
sys.dm_resource_governor_external_resource_pool_affinity	Resource Governor	Returns CPU affinity information about the current external resource pool configuration in Resource Governor. Returns one row per scheduler in SQL Server where each scheduler is mapped to an individual processor. Use this view to monitor the condition of a scheduler or to identify runaway tasks.

For information about monitoring SQL Server instances, see Catalog Views and Resource Governor Related Dynamic Management Views.

Settings and configuration

View the Machine Learning Services installation setting and configuration options.

Run the query below to get this output. For more information on the views and functions used, see sys.dm_server_registry, sys.configurations, and SERVERPROPERTY.

SELECT CAST(SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS INT) AS IsMLServicesInstalled
    , CAST(value_in_use AS INT) AS ExternalScriptsEnabled
    , COALESCE(SIGN(SUSER_ID(CONCAT (
                    CAST(SERVERPROPERTY('MachineName') AS NVARCHAR(128))
                    , '\SQLRUserGroup'
                    , CAST(serverproperty('InstanceName') AS NVARCHAR(128))
                    ))), 0) AS ImpliedAuthenticationEnabled
    , COALESCE((
            SELECT CAST(r.value_data AS INT)
            FROM sys.dm_server_registry AS r
            WHERE r.registry_key LIKE 'HKLM\Software\Microsoft\Microsoft SQL Server\%\SuperSocketNetLib\Tcp'
            AND r.value_name = 'Enabled'
            ), - 1) AS IsTcpEnabled
FROM sys.configurations
WHERE name = 'external scripts enabled';

The query returns the following columns:

Column	Description
IsMLServicesInstalled	Returns 1 if SQL Server Machine Learning Services is installed for the instance. Otherwise, returns 0.
ExternalScriptsEnabled	Returns 1 if external scripts is enabled for the instance. Otherwise, returns 0.
ImpliedAuthenticationEnabled	Returns 1 if implied authentication is enabled. Otherwise, returns 0. The configuration for implied authentication is checked by verifying if a login exists for SQLRUserGroup.
IsTcpEnabled	Returns 1 if the TCP/IP protocol is enabled for the instance. Otherwise, returns 0. For more information, see Default SQL Server Network Protocol Configuration.

Active sessions

View the active sessions running external scripts.

Run the query below to get this output. For more information on the dynamic management views used, see sys.dm_exec_requests, sys.dm_external_script_requests, and sys.dm_exec_sessions.

SELECT r.session_id, r.blocking_session_id, r.status, DB_NAME(s.database_id) AS database_name
    , s.login_name, r.wait_time, r.wait_type, r.last_wait_type, r.total_elapsed_time, r.cpu_time
    , r.reads, r.logical_reads, r.writes, er.language, er.degree_of_parallelism, er.external_user_name
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_external_script_requests AS er
ON r.external_script_request_id = er.external_script_request_id
INNER JOIN sys.dm_exec_sessions AS s
ON s.session_id = r.session_id;

The query returns the following columns:

Column	Description
session_id	Identifies the session associated with each active primary connection.
blocking_session_id	ID of the session that is blocking the request. If this column is NULL, the request is not blocked, or the session information of the blocking session is not available (or cannot be identified).
status	Status of the request.
database_name	Name of the current database for each session.
login_name	SQL Server login name under which the session is currently executing.
wait_time	If the request is currently blocked, this column returns the duration in milliseconds, of the current wait. Is not nullable.
wait_type	If the request is currently blocked, this column returns the type of wait. For information about types of waits, see sys.dm_os_wait_stats.
last_wait_type	If this request has previously been blocked, this column returns the type of the last wait.
total_elapsed_time	Total time elapsed in milliseconds since the request arrived.
cpu_time	CPU time in milliseconds that is used by the request.
reads	Number of reads performed by this request.
logical_reads	Number of logical reads that have been performed by the request.
writes	Number of writes performed by this request.
language	Keyword that represents a supported script language.
degree_of_parallelism	Number indicating the number of parallel processes that were created. This value might be different from the number of parallel processes that were requested.
external_user_name	The Windows worker account under which the script was executed.

Execution statistics

View the execution statistics for the external runtime for R and Python. Only statistics of RevoScaleR, revoscalepy, or microsoftml package functions are currently available.

Run the query below to get this output. For more information on the dynamic management view used, see sys.dm_external_script_execution_stats. The query only returns functions that have been executed more than once.

SELECT language, counter_name, counter_value
FROM sys.dm_external_script_execution_stats
WHERE counter_value > 0
ORDER BY language, counter_name;

The query returns the following columns:

Column	Description
language	Name of the registered external script language.
counter_name	Name of a registered external script function.
counter_value	Total number of instances that the registered external script function has been called on the server. This value is cumulative, beginning with the time that the feature was installed on the instance, and cannot be reset.

Performance counters

View the performance counters related to the execution of external scripts.

Run the query below to get this output. For more information on the dynamic management view used, see sys.dm_os_performance_counters.

SELECT counter_name, cntr_value
FROM sys.dm_os_performance_counters 
WHERE object_name LIKE '%External Scripts%'

sys.dm_os_performance_counters outputs the following performance counters for external scripts:

Counter	Description
Total Executions	Number of external processes started by local or remote calls.
Parallel Executions	Number of times that a script included the @parallel specification and that SQL Server was able to generate and use a parallel query plan.
Streaming Executions	Number of times that the streaming feature has been invoked.
SQL CC Executions	Number of external scripts run where the call was instantiated remotely and SQL Server was used as the compute context.
Implied Auth. Logins	Number of times that an ODBC loopback call was made using implied authentication; that is, the SQL Server executed the call on behalf of the user sending the script request.
Total Execution Time (ms)	Time elapsed between the call and completion of call.
Execution Errors	Number of times scripts reported errors. This count does not include R or Python errors.

Memory usage

View information about the memory used by the OS, SQL Server, and the external pools.

Run the query below to get this output. For more information on the dynamic management views used, see sys.dm_resource_governor_external_resource_pools and sys.dm_os_sys_info.

SELECT physical_memory_kb, committed_kb
    , (SELECT SUM(peak_memory_kb)
        FROM sys.dm_resource_governor_external_resource_pools AS ep
        ) AS external_pool_peak_memory_kb
FROM sys.dm_os_sys_info;

The query returns the following columns:

Column	Description
physical_memory_kb	The total amount of physical memory on the machine.
committed_kb	The committed memory in kilobytes (KB) in the memory manager. Does not include reserved memory in the memory manager.
external_pool_peak_memory_kb	The sum of the maximum amount of memory used, in kilobytes, for all external resource pools.

Memory configuration

View information about the maximum memory configuration in percentage of SQL Server and external resource pools. If SQL Server is running with the default value of max server memory (MB), it is considered as 100% of the OS memory.

Run the query below to get this output. For more information on the views used, see sys.configurations and sys.dm_resource_governor_external_resource_pools.

SELECT 'SQL Server' AS name
    , CASE CAST(c.value AS BIGINT)
        WHEN 2147483647 THEN 100
        ELSE (SELECT CAST(c.value AS BIGINT) / (physical_memory_kb / 1024.0) * 100 FROM sys.dm_os_sys_info)
        END AS max_memory_percent
FROM sys.configurations AS c
WHERE c.name LIKE 'max server memory (MB)'
UNION ALL
SELECT CONCAT ('External Pool - ', ep.name) AS pool_name, ep.max_memory_percent
FROM sys.dm_resource_governor_external_resource_pools AS ep;

The query returns the following columns:

Column	Description
name	Name of the external resource pool or SQL Server.
max_memory_percent	The maximum memory that SQL Server or the external resource pool can use.

Resource pools

In SQL Server Resource Governor, a resource pool represents a subset of the physical resources of an instance. You can specify limits on the amount of CPU, physical IO, and memory that incoming application requests, including execution of external scripts, can use within the resource pool. View the resource pools used for SQL Server and external scripts.

Run the query below to get this output. For more information on the dynamic management views used, see sys.dm_resource_governor_resource_pools and sys.dm_resource_governor_external_resource_pools.

SELECT CONCAT ('SQL Server - ', p.name) AS pool_name
    , p.total_cpu_usage_ms, p.read_io_completed_total, p.write_io_completed_total
FROM sys.dm_resource_governor_resource_pools AS p
UNION ALL
SELECT CONCAT ('External Pool - ', ep.name) AS pool_name
    , ep.total_cpu_user_ms, ep.read_io_count, ep.write_io_count
FROM sys.dm_resource_governor_external_resource_pools AS ep;

The query returns the following columns:

Column	Description
pool_name	Name of the resource pool. SQL Server resource pools are prefixed with SQL Server and external resource pools are prefixed with External Pool.
total_cpu_usage_hours	The cumulative CPU usage in milliseconds since the Resource Governor statistics were reset.
read_io_completed_total	The total read IOs completed since the Resource Governor statistics were reset.
write_io_completed_total	The total write IOs completed since the Resource Governor statistics were reset.

Installed packages

You can to view the R and Python packages that are installed in SQL Server Machine Learning Services by executing an R or Python script that outputs these.

Installed packages for R

View the R packages installed in SQL Server Machine Learning Services.

Run the query below to get this output. The query use an R script to determine R packages installed with SQL Server.

EXECUTE sp_execute_external_script @language = N'R'
, @script = N'
OutputDataSet <- data.frame(installed.packages()[,c("Package", "Version", "Depends", "License", "LibPath")]);'
WITH result sets((Package NVARCHAR(255), Version NVARCHAR(100), Depends NVARCHAR(4000)
    , License NVARCHAR(1000), LibPath NVARCHAR(2000)));

The columns returned are:

Column	Description
Package	Name of the installed package.
Version	Version of the package.
Depends	Lists the package(s) that the installed package depends on.
License	License for the installed package.
LibPath	Directory where you can find the package.

Installed packages for Python

View the Python packages installed in SQL Server Machine Learning Services.

Run the query below to get this output. The query use an Python script to determine the Python packages installed with SQL Server.

EXECUTE sp_execute_external_script @language = N'Python'
, @script = N'
import pkg_resources
import pandas
OutputDataSet = pandas.DataFrame(sorted([(i.key, i.version, i.location) for i in pkg_resources.working_set]))'
WITH result sets((Package NVARCHAR(128), Version NVARCHAR(128), Location NVARCHAR(1000)));

The columns returned are:

Column	Description
Package	Name of the installed package.
Version	Version of the package.
Location	Directory where you can find the package.

Next steps

• Extended events for machine learning
• Resource Governor Related Dynamic Management Views
• System Dynamic Management Views
• Monitor machine learning using custom reports in Management Studio