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. |