Мониторинг служб машинного обучения SQL Server с помощью динамических административных представлений

Область применения: SQL Server 2016 (13.x) и более поздних версий Управляемый экземпляр SQL Azure

Динамические административные представления можно использовать для отслеживания выполнения внешних скриптов (Python и R), мониторинга используемых ресурсов, диагностики проблем и настройки производительности в службах машинного обучения SQL Server.

В этой статье вы найдете динамические административные представления, относящиеся к службам машинного обучения SQL Server. Кроме того, в ней приводятся примеры запросов, которые демонстрируют:

• Настройки и параметры конфигурации для служб машинного обучения
• Активные сеансы, выполняющие внешние скрипты Python или R
• Статистика выполнения для внешней среды выполнения Python и R
• Счетчики производительности для внешних скриптов
• Использование памяти для ОС, SQL Server и внешних пулов ресурсов
• Конфигурация памяти для SQL Server и внешних пулов ресурсов
• Пулы ресурсов регулятора ресурсов, включая внешние пулы ресурсов
• Установленные пакеты для Python и R

Дополнительные общие сведения о динамических административных представлениях см. в разделе Системные динамические административные представления.

Динамические административные представления

При наблюдении за рабочими нагрузками на службы машинного обучения в SQL Server можно использовать следующие динамические административные представления. Для запроса динамических административных представлений требуется разрешение VIEW SERVER STATE на экземпляр.

Сведения о мониторинге экземпляров SQL Server см. в разделе "Представления каталога" и "Связанные с регулятором ресурсов" динамические административные представления.

Параметры и конфигурация

Просмотр параметров установки и конфигурации служб машинного обучения.

Чтобы получить эти выходные данные, выполните приведенный ниже запрос. Дополнительные сведения об используемых представлениях и функциях см. в разделах sys. dm_server_registry, sys.configurations и 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';

Этот запрос возвращает следующие столбцы:

Активные сеансы

Просмотр активных сеансов, выполняющих внешние скрипты.

Чтобы получить эти выходные данные, выполните приведенный ниже запрос. Дополнительные сведения об используемых динамических административных представлениях см. в разделах sys.dm_exec_requests, sys.dm_external_script_requests и 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;

Этот запрос возвращает следующие столбцы:

Статистика выполнения.

Просмотр статистики выполнения для внешней среды выполнения R и Python. В настоящее время доступна статистика по функциям пакетов RevoScaleR, revoscalepy или microsoftml.

Чтобы получить эти выходные данные, выполните приведенный ниже запрос. Дополнительные сведения об используемом динамическом административном представлении см. в разделе sys.dm_external_script_execution_stats. Запрос возвращает только те функции, которые были выполнены несколько раз.

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

Этот запрос возвращает следующие столбцы:

Счетчики производительности

Просмотр счетчиков производительности, связанных с выполнением внешних скриптов.

Чтобы получить эти выходные данные, выполните приведенный ниже запрос. Дополнительные сведения об используемом динамическом административном представлении см. в разделе 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 выдаются следующие счетчики производительности для внешних скриптов:

Использование памяти

Просмотр сведений о памяти, используемой ОС, SQL Server и внешними пулами.

Чтобы получить эти выходные данные, выполните приведенный ниже запрос. Дополнительные сведения об используемых динамических административных представлениях см. в разделе sys.dm_resource_governor_external_resource_pools и 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;

Этот запрос возвращает следующие столбцы:

Настройка использования памяти

Просмотр сведений о максимальной конфигурации памяти (в процентах) для SQL Server и внешних пулов ресурсов. Если SQL Server работает со значением max server memory (MB) по умолчанию, оно принимается за 100% памяти ОС.

Чтобы получить эти выходные данные, выполните приведенный ниже запрос. Дополнительные сведения об используемых динамических административных представлениях см. в разделе sys.configurations и 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;

Этот запрос возвращает следующие столбцы:

Пулы ресурсов

В регуляторе ресурсов SQL Serverпул ресурсов представляет подмножество физических ресурсов экземпляра. Вы можете задать ограничения на загрузку ЦП, физические средства ввода-вывода и объем памяти, доступный для входящих запросов приложений, включая выполнение внешних скриптов, в пуле ресурсов. Просмотр пулов ресурсов, используемых для SQL Server и внешних скриптов.

Чтобы получить эти выходные данные, выполните приведенный ниже запрос. Дополнительные сведения об используемых динамических административных представлениях см. в разделах sys.dm_resource_governor_resource_pools и 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;

Этот запрос возвращает следующие столбцы:

Установленные пакеты

Вы можете узнать, какие пакеты R и Python установлены в службах машинного обучения SQL Server, выполнив сценарий R или Python, который выводит эти данные.

Установленные пакеты для R

Просмотр пакетов R, установленных в службах машинного обучения SQL Server.

Чтобы получить эти выходные данные, выполните приведенный ниже запрос. В запросе используется скрипт R, позволяющий определить, какие пакеты R установлены в 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)));

Возвращаются следующие столбцы:

Установленные пакеты для Python

Просмотр пакетов Python, установленных в службах машинного обучения SQL Server.

Чтобы получить эти выходные данные, выполните приведенный ниже запрос. В запросе используется скрипт Python, позволяющий определить, какие пакеты Python установлены в 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)));

Возвращаются следующие столбцы:

Следующие шаги

• Расширенные события для служб машинного обучения
• Динамические административные представления регулятора ресурсов
• Системные динамические административные представления
• Мониторинг служб машинного обучения с помощью настраиваемых отчетов в Management Studio