動的管理ビュー (DMV) を使用して SQL Server Machine Learning Services を監視する

適用対象: SQL Server 2016 (13.x) 以降 Azure SQL Managed Instance

動的管理ビュー (DMV) を使用して、外部スクリプト (Python と R) の実行、使用されたリソース、問題の診断、SQL Server Machine Learning Services のパフォーマンスの調整を監視します。

この記事では、SQL Server Machine Learning Services に固有の DMV について説明します。 また、次のようなクエリの例も紹介します。

• 機械学習の設定オプションと構成オプション
• 外部の Python または R スクリプトを実行しているアクティブなセッション
• Python および R 用の外部ランタイムの実行の統計
• 外部スクリプトのパフォーマンス カウンター
• OS、SQL Server、および外部リソース プールのメモリ使用量
• SQL Server および外部リソース プールのメモリ構成
• 外部リソース プールを含むリソース ガバナーのリソース プール
• Python および R 用にインストールされたパッケージ

DMV に関する一般情報については、「システムの動的管理ビュー」を参照してください。

動的管理ビュー

SQL Server で機械学習のワークロードを監視するときに、次の動的管理ビューを使用できます。 DMV に対してクエリを実行するには、インスタンスに対する VIEW SERVER STATE 権限が必要です。

SQL Server インスタンスの監視の詳細については、「カタログ ビュー」と「リソース ガバナー関連の動的管理ビュー」を参照してください。

設定と構成

Machine Learning Services のインストール設定と構成オプションを表示します。

この出力を取得するには、次のクエリを実行します。 使用するビューと関数の詳細については、「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';

このクエリは次の列を返します。

Active sessions

外部のスクリプトを実行しているアクティブなセッションを表示します。

この出力を取得するには、次のクエリを実行します。 使用される動的管理ビューの詳細については、「sys. dm_exec_requests」、「sys. dm_external_script_requests」、および「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 は、外部スクリプトの次のパフォーマンス カウンターを出力します。

メモリ使用量

OS、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) の既定値で実行されている場合、OS メモリの 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 のリソース ガバナーでは、リソース プールは、 インスタンスの物理リソースのサブセットを表します。 受信するアプリケーション要求 (外部スクリプトの実行を含む) がリソース プール内で使用できる CPU、物理 IO、およびメモリの量に制限を指定できます。 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;

このクエリは次の列を返します。

インストール済みパッケージ

SQL Server Machine Learning Services にインストールされている R パッケージと Python パッケージを表示するには、これらを出力する R または Python のスクリプトを実行します。

R のインストール済みパッケージ

SQL Server Machine Learning Services にインストールされた R パッケージを表示します。

この出力を取得するには、次のクエリを実行します。 このクエリでは、R スクリプトを使用して、SQL Server を使用してインストールする R パッケージを決定します。

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 用にインストールされたパッケージ

SQL Server Machine Learning Services にインストールされた Python パッケージを表示します。

この出力を取得するには、次のクエリを実行します。 このクエリでは、Python スクリプトを使用して、SQL Server を使用してインストールする Python パッケージを決定します。

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 でカスタム レポートを使用して機械学習を監視する