Shoeisha Technology Media

EnterpriseZine(エンタープライズジン)

EnterpriseZine(エンタープライズジン)

テーマ別に探す

SQL Server稼働マシンのHW構成とSW構成を把握しよう

2017/04/14 06:00

 はじめまして。NECソリューションイノベータの太田です。去年の10月、各国のSQL Serverの専門家たちがシアトルに集結する年次イベント、PASS SUMMITに参加してきました。3,000人に近い参加者による熱気にあふれた4日間のイベントで印象的だった事柄のひとつは「日本人参加者が1%にも満たない」ことです。昨今SQL Serverの存在が大きくなる一方で、日本国内の専門家が不足している実感はないでしょうか。そこで、本連載では、これから専門家を目指すエンジニアの一助になるような情報を発信していきます。

 この連載では“SQL Server管理者のための動的管理ビュー入門編”として「動的管理ビュー」を中心にSQL Serverをより深く知るための情報採取の手段を紹介します。

「動的管理ビュー」とはSQL Serverの状態情報を返すビューで例えばOracleの動的パフォーマンスビュー、PostgresSQLの稼働統計情報ビューに相当し、健康状態の監視、問題の解析、性能のチューニングなど様々な用途に活用します。

  製品ごとの対応機能
SQL Server 動的管理ビュー(sys.dm_*)
Oracle 動的パフォーマンスビュー(V$*)
DB2 SQL管理ビュー(SNAP*)
PostgreSQL 稼働統計情報ビュー(pg_stat*)
MySQL  パフォーマンススキーマ(performance_schema)

 この連載を通じて採取できる情報の一通りをおさえることで、問題に直面した際の調査の入り口を得ることがゴールです。

 なお、昨今は仮想基盤やクラウド上のSQL Serverをサービスとして利用するケースが増えています。こうした背景を踏まえ今回はあえてSQL Server以外にはタッチできないという制約を設け、「SQL Serverを介した標準機能による情報採取」にこだわり、「動的管理ビュー」に限らずに各種の関数やカタログも紹介します。

 第1回目はウォーミングアップとしてSQL Serverが稼働するサーバのHW構成とSW構成を把握することから始めます。ターゲットをはっきりと認識することに加え、後の問題解析や性能チューニングのための基礎情報となります。

SQL Server稼働マシンのHW構成を把握する

 ここではプラットフォーム、CPU、メモリ、ストレージを確認していきます。

 プラットフォーム

 ・SQL Serverが稼働する環境の確認

SELECT 
  virtual_machine_type		-- 0=物理マシン環境、0≠仮想マシン環境
  , virtual_machine_type_desc	-- 種類の説明
FROM
  sys.dm_os_sys_info

 virtual_machine_typeが0の場合は物理マシン環境、0以外の場合は仮想マシン環境と解釈します。

例)左図:物理マシン環境での実行結果  右図:仮想マシン環境での実行結果

 ・システムのメーカとモデルの確認

EXEC sys.xp_readerrorlog 0, 1, "Manufacturer", "Model"
 例)NEC製物理マシン環境での実行結果
例)NEC製物理マシン環境での実行結果
 例)Microsoft Azure仮想マシン環境での実行結果
例)Microsoft Azure仮想マシン環境での実行結果

 システムのメーカとモデルの確認はERRORLOGを参照します。sys.xp_readerrorlogはERRORLOGから指定のキーワードにヒットするレコードを抽出する拡張ストアドプロシージャです。パラメータの詳細はここで解説されています。

 CPU

 ・コア数とプロセッサ数の確認

SELECT 
  cpu_count					   -- 論理コア総数
  , hyperthread_ratio				   -- 単一プロセッサ当たりの論理コア数
  , cpu_count/hyperthread_ratio AS processor_count  -- 搭載しているプロセッサ数
FROM 
  sys.dm_os_sys_info
例)8つの物理コアをもつプロセッサを4つ搭載した物理マシン環境(Hyper Threadingを有効化)での実行結果

 cpu_countは論理コア総数を示します。またhyperthread_ratioは単一プロセッサ当たりの論理コア数を示します。よってcpu_countをhyperthread_ratioで割ることで搭載しているプロセッサ数(processor_count)を導出できます。

 なお、仮想マシン環境における論理コア数とは仮想マシンに割り当てた仮想コア数となります。一方、物理マシン環境における論理コア数とは、物理マシンに搭載された物理コア数として見える場合と、Hyper-Threading TechnologyのようなCPUがもつ機能により実際の物理コア数を超過するコア数(OSが認識している論理コア数)として見える場合の2通りがあるため論理コア数からは物理コア数を特定できません。そのため物理コア数を特定するためにはERRORLOGを参照します。

EXEC sys.xp_readerrorlog 0, 1, "detected", "socket"

 先ほどと同様にsys.xp_readerrorlogを通じてERRORLOGから該当レコードを抽出します。以下の例は8つの物理コアをもつプロセッサを4つ搭載した物理マシン環境(Hyper Threadingを有効化)での実行結果です。

 例)8つの物理コアをもつプロセッサを4つ搭載した物理マシン環境(Hyper Threadingを有効化)での実行結果
例)8つの物理コアをもつプロセッサを4つ搭載した物理マシン環境(Hyper Threadingを有効化)での実行結果

 赤線箇所の前者は物理コア数、後者はOSが認識する論理コア数を示します。Hyper Threadingが有効になっている場合は前者と後者の数が異なり、無効になっている場合は前者と後者の数が等しくなります。

 なお、仮想マシン環境での実行結果は以下の通り、前者も後者も仮想マシンに割り当てた仮想コア数が示され、等しい数になります。

 例)仮想CPUを2つ割り当てた仮想マシン環境での実行結果
例)仮想CPUを2つ割り当てた仮想マシン環境での実行結果

 ・CPUモデルの詳細確認

EXEC sys.xp_instance_regread
  N'HKEY_LOCAL_MACHINE'
  , N'HARDWARE\DESCRIPTION\System\CentralProcessor\0'
  , N'ProcessorNameString'

 例)CPUモデルの詳細確認クエリの実行結果
例)CPUモデルの詳細確認クエリの実行結果

 sys.xp_instance_regread はSQL Serverを介してレジストリを参照する拡張プロシージャです。

 メモリ

 ・サーバに割り当てられた物理メモリサイズの確認

SELECT
  physical_memory_kb	-- 物理メモリサイズ [kb]
  , virtual_memory_kb	-- この環境におけるユーザモード仮想アドレス空間の上限サイズ [kb]
FROM 
  sys.dm_os_sys_info
例)512GBの物理メモリを搭載した物理マシン環境での実行結果

 physical_memory_kbが物理メモリサイズを示します。virtual_memory_kbの直訳は仮想メモリサイズですが、正しい解釈は「この環境におけるユーザモード仮想アドレス空間(User-mode Virtual Address Space)の上限サイズ」です。環境ごとの上限サイズはここに記載されています。

 ストレージ

 ・SQL Server のデータベースが配置された論理ボリューム情報の確認

SELECT DISTINCT
  vs.volume_mount_point						  -- ボリュームラベル名
  , vs.total_bytes/1024/1024 as total_mb			  -- サイズ [kb]
  , vs.available_bytes/1024/1024 as available_mb		  -- 使用サイズ [kb]
  , 100 * (vs.total_bytes - vs.available_bytes)/vs.total_bytes as 'available_%'	-- 使用率 [%]
  , vs.supports_compression	 -- OSによる圧縮をサポートするかどうかを示します
  , vs.supports_alternate_streams -- 代替ストリームをサポートするかどうかを示します。
  , vs.supports_sparse_files	  -- スパース ファイルをサポートするかどうかを示します
  , vs.is_read_only		  -- 読み取り専用としてマークされているかどうかを示します
  , vs.is_compressed		  -- 圧縮されているかどうかを示します
FROM
  sys.master_files mf
 CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
例)論理ボリューム確認クエリの実行結果

 sys.master_filesはデータベースファイル(データファイルとトランザクションログファイル)の一覧を返します。またsys.dm_os_volume_statsは引数に指定されたデータベースファイルが保管されているOSの論理ボリュームの情報を返します。その内容は論理ボリュームのサイズや使用率に加え、ファイルシステムの種類や属性などの付帯情報も確認できます。

※この続きは、会員の方のみお読みいただけます(登録無料)。


著者プロフィール

  • 太田智行(オオタトモユキ)

    NECソリューションイノベータ株式会社 2002年入社以来、SQL Server、Oracle、MySQL、PostgreSQLを活用したSIを多数経験。 2013年Microsoft社と「In-Memory OLTP機能」の徹底検証を実施。 以来、SQL Serverの高...

この記事もオススメ

All contents copyright © 2006-2017 Shoeisha Co., Ltd. All rights reserved. ver.1.5