Surveiller SQL Server Machine Learning Services à l’aide de vues de gestion dynamique (DMV)

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Azure SQL Managed Instance

Utilisez des vues de gestion dynamique pour surveiller l’exécution de scripts externes (Python et R), les ressources utilisées, diagnostiquer les problèmes et optimiser les performances dans SQL Server Machine Learning Services.

Dans cet article, vous trouverez les DMV spécifiques de SQL Server Machine Learning Services. Vous trouverez également des exemples de requêtes qui affichent les éléments suivants :

• Paramètres et options de configuration avancées pour le Machine Learning
• Sessions actives exécutant des scripts R ou Python externes
• Statistiques d’exécution du runtime externe pour Python et R
• Compteurs de performances pour les scripts externes
• Utilisation de la mémoire pour le système d’exploitation, SQL Server et les pools de ressources externes
• Configuration de la mémoire pour SQL Server et les pools de ressources externes
• Pools de ressources Resource Governor, y compris les pools de ressources externes
• Packages installés pour Python et R

Pour plus d’informations générales sur les DMV, consultez Vues de gestion dynamique du système.

Vues de gestion dynamique (DMV)

Les vues de gestion dynamique suivantes peuvent être utilisées lors de l’analyse des charges de travail de Machine Learning dans SQL Server. Pour interroger les vues de gestion dynamique, vous devez disposer de l’autorisation VIEW SERVER STATE sur l’instance.

Pour obtenir des informations sur l’analyse des instances SQL Server, consultez Affichages catalogue et Vues de gestion dynamiques pour Resource Governor.

Paramètres et configuration

Affichez les paramètres d’installation de Machine Learning Services et les options de configuration.

Exécutez la requête ci-dessous pour obtenir cette sortie. Pour plus d’informations sur les vues et les fonctions utilisées, consultez sys. dm_server_registry, sys. configurationset 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';

La requête retourne les colonnes suivantes :

Sessions actives

Affiche les sessions actives exécutant des scripts externes.

Exécutez la requête ci-dessous pour obtenir cette sortie. Pour plus d’informations sur les vues de gestion dynamique utilisées, consultez sys. dm_exec_requests, sys. dm_external_script_requests et 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;

La requête retourne les colonnes suivantes :

Statistiques d'exécution

Affiche les statistiques d’exécution du runtime externe pour Python et R. Seules les statistiques des fonctions de package RevoScaleR, revoscalepy ou microsoftml sont actuellement disponibles.

Exécutez la requête ci-dessous pour obtenir cette sortie. Pour plus d’informations sur la vue de gestion dynamique utilisée, consultez sys.dm_external_script_execution_stats. La requête renvoie uniquement les fonctions qui ont été exécutées plusieurs fois.

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

La requête retourne les colonnes suivantes :

Compteurs de performance

Affiche les compteurs de performance liés à l’exécution de scripts externes.

Exécutez la requête ci-dessous pour obtenir cette sortie. Pour plus d’informations sur la vue de gestion dynamique utilisée, consultez 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 génère les compteurs de performances suivants pour les scripts externes :

Utilisation de la mémoire

Affichez des informations sur la mémoire utilisée par le système d’exploitation, SQL Server et les pools externes.

Exécutez la requête ci-dessous pour obtenir cette sortie. Pour plus d’informations sur les vues de gestion dynamique utilisées, consultez sys. dm_resource_governor_external_resource_pools et 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;

La requête retourne les colonnes suivantes :

Configuration de la mémoire

Affiche des informations sur la configuration de mémoire maximale en pourcentage de pools de ressources externes et SQL Server. Si SQL Server s’exécute avec la valeur par défaut de max server memory (MB), cela représente 100 % de la mémoire du système d’exploitation.

Exécutez la requête ci-dessous pour obtenir cette sortie. Pour plus d’informations sur les vues utilisées, consultez sys.configurations et 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;

La requête retourne les colonnes suivantes :

Pools de ressources

Dans SQL Server Resource Governor, un pool de ressources représente un sous-ensemble des ressources physiques d’une instance. Vous pouvez spécifier des limites sur la quantité d’UC, les E/S physiques et la mémoire que les demandes d’application entrantes, y compris l’exécution des scripts externes, peuvent utiliser avec le pool de ressources. Affichez les pools de ressources utilisés pour les scripts SQL Server et les scripts externes.

Exécutez la requête ci-dessous pour obtenir cette sortie. Pour plus d’informations sur les vues de gestion dynamique utilisées, consultez sys.dm_resource_governor_resource_pools et 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;

La requête retourne les colonnes suivantes :

Packages installés

Vous pouvez afficher les packages R et Python qui sont installés dans SQL Server Machine Learning Services en exécutant un script R ou Python qui les génère.

Packages installés pour R

Affiche les packages R installés dans SQL Server Machine Learning Services.

Exécutez la requête ci-dessous pour obtenir cette sortie. La requête utilise un script R pour déterminer les packages R installés avec 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)));

Les colonnes renvoyées sont les suivantes :

Packages installés pour Python

Affiche les packages Python installés dans SQL Server Machine Learning Services.

Exécutez la requête ci-dessous pour obtenir cette sortie. La requête utilise un script Python pour déterminer les packages Python installés avec 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)));

Les colonnes renvoyées sont les suivantes :

Étapes suivantes

• Événements étendus pour le Machine Learning
• Vues de gestion dynamique relatives à Resource Governor
• Vues de gestion dynamique système
• Surveiller le Machine Learning à l’aide de rapports personnalisés dans Management Studio