Bewerken

Delen via


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.

Output from the settings and configuration query

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.

Output from the active settings query

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.

Output from the execution statistics query

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.

Output from the performance counters query

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.

Output from the memory usage query

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.

Output from the memory configuration query

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.

Output from the resource pools query

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.

Output from the installed packages for R query

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.

Output from the installed packages for Python query

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