6月 | 2018 | DB & SQL 技術ブログ

ITコーディネータのシュウです。

いよいよ梅雨の季節が近づいてきました。上の写真は、少し前に娘がスマホで撮って送ってくれたきれいな夕方の空です。少し神秘的ですね。梅雨になるとこういう空もなかなか見れなくなっちゃうな。
さて、最近のスポーツでは、サッカーのワールドカップがいよいよ始まります。2か月前に監督の交代劇があり、このところの強化試合も負け続けているので、難しいと思っちゃいますが、どうか奇跡よ起きて～！　2002年の日韓共催のワールドカップのベスト16を超えてほしい！
また、卓球では先日の荻村杯・ジャパンオープンで、男子は張本智和選手、女子では伊藤美誠選手が優勝しました。接戦を最後まで粘って勝ち切った精神力は本当にすごい、感動します。日本選手が頑張っていると、元気が湧いてきます。よーし、仕事も頑張るぞー！

＜本日の題材＞
動的管理ビュー（SQL Server）

SQL Serverを使用していて、パフォーマンスがいまいちよくない場合など、最もCPUを多く使っているクエリーは何か？、最も多くのI/Oを発生させているクエリは何か？、不足しているインデックスはないか？などを確認したくなることがあると思います。そんなときに、知っていると便利な動的管理ビューについて、今回は取り上げてみたいと思います。

SQL Serverというと、パフォーマンスモニターや利用状況モニターなど、GUIのツールでいろいろと調べるというイメージが強く、逆にOracleは、V$表といわれる動的パフォーマンスビューなどを駆使していろいろと調べるというイメージが強いですね。（最近はOracleもEnterprise ManagerなどでGUIでも見れるかたちにしてきていますが）
ただ、SQL Serverでも、動的管理ビューというものから、いろいろと上記のような内容を調査することができます。
ちなみに、動的管理ビュー（DMV：Dynamic Management View）は、SQL Server2005で追加された機能で、パフォーマンス・チューニングを手掛けるエンジニアにとっては画期的といえるものでした。それまで、ほとんどドキュメント化されず、その状態を参照する方法も知らされていなかったSQL Serverの内部が、広く可視化されたからです。
それでは、以下に例を挙げてみましょう。

●最もCPUを多く使っているクエリーの上位10位までを確認する

SELECT TOP 10
total_worker_time/execution_count AS avg_cpu_cost,
execution_count,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS query_text,
query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY avg_cpu_cost DESC;

上記は、「sys.dm_exec_query_stats」という動的管理ビューから抽出できる情報で、「total_worker_time」（プランの実行で使用されたcpu時間の合計）を「execution_count」（プランが実行された回数）で割った値を平均のcpuコストとして、それの多い順に10件を表示しています。ただし、実行頻度（execution_count）が少なければ、それほど重要ではないと思われます。
（※注意点としては、現在キャッシュされているクエリに対する集計情報のみを示していること。メモリが多くなければ、過去に行ったコストの高いクエリがキャッシュから削除されている可能性もあるので、定期的にこの抽出を行えば、コストの高いクエリを特定できる可能性が高くなると思われます。）

なお、上記のSQLでは、前回取り上げた「CROSS APPLY」を使用しています。また、「querry_plan」の列を最後に表示していますが、これをクリックすると、以下のように「querry_text」で表示されたSQLの実行時の実行プランが表示できます。（ただし、一緒に表示するとその分抽出処理が重たくなると思われるので、後から plan_handleを元に実行プランを抽出することも可能です。）

参考：https://docs.microsoft.com/ja-jp/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql?view=sql-server-2017

●最も多くのI/Oを発生させているクエリの上位10位までを確認する

SELECT TOP 10
(qs.total_logical_reads/qs.execution_count) AS avg_logical_reads,
(qs.total_logical_writes/qs.execution_count) AS avg_logical_writes,
(qs.total_physical_reads/qs.execution_count) AS avg_phys_reads,
execution_count,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS query_text,
query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY (total_logical_reads + total_logical_writes) DESC;

こちらは、「total_logical_reads」（論理読み取りの合計数）と「total_logical_writes」（論理書き込みの合計数）の合計の多いものから順に上位10件を表示しています。

●不足していると思われるインデックスの一覧

SELECT gs.avg_user_impact AS [予測されるクエリパフォーマンス改善率],
gs.avg_total_user_cost AS [削減できたクエリの平均コスト],
gs.last_user_seek AS [最後にシークした時間],
id.statement AS [テーブル名] ,
id.equality_columns AS [等値述語に使用できる列],
id.inequality_columns AS [不等値述語に使用できる列] ,
id.included_columns AS [包括列として必要な列],
gs.unique_compiles AS [コンパイルおよび再コンパイルの数],
gs.user_seeks AS [クエリによって発生したシーク数]
FROM sys.dm_db_missing_index_group_stats AS gs
INNER JOIN sys.dm_db_missing_index_groups AS ig
ON gs.group_handle = ig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS id
ON ig.index_handle = id.index_handle
-- WHERE id.[database_id] =DB_ID(‘db名’)
-- コメントをはずすと指定したデータベースでの抽出となる
Order By gs.avg_user_impact DESC;

上記は、「sys.dm_db_missing_index_group_stats」「sys.dm_db_missing_index_groups」「sys.dm_db_missing_index_details」という動的管理ビューから抽出できる情報で、インデックスが存在していたなら改善されるであろうパフォーマンス改善率などを表示するものになっています。
上記の動的管理ビューの結果からインデックスを追加作成するとした場合には、[equality_columns（等値述語に使用できる列）]を最初に指定し、その次に、[inequality_columns（不等値述語に使用できる列）]を指定し、INCLUDE句に[included_columns（包括列として必要な列）]を指定するかたちになります。

※参考：https://docs.microsoft.com/ja-jp/previous-versions/sql/sql-server-2008-r2/ms345405(v=sql.105)

例えば、1番目の結果からインデックスを追加するとしたら、

CREATE NONCLUSTERED INDEX [KEY1_製品区分別積上] ON [dbo].[製品区分別積上]
(
[年度], [月], [バージョン], [部門コード], [製品区分], [製品項目]
)
INCLUDE (
[金額], [粗利率], [粗利額]
);

レスポンス改善を行いたいときに、結構有効かも知れませんね。

今日は以上まで

にほんブログ村

ITコーディネータのシュウです。

自宅から会社に行く途中に道路のそばに咲いているポピーの花です。自転車で通るので、きれいに咲いているときには少し止まって眺めたりするこの頃ですが、若いときは、頭の中は仕事のことか、差し迫ったやるべきことなどでいっぱい（もしくはぼーっとしている？）で、見ていても記憶に残らないという感じだったと思います。妻にあそこにきれいな花が咲いているでしょう？と聞かれても、そうだっけ？という返事をするので、そういう感覚が欠落しているかわいそうな人だとあきれられていたものですが、少しずつ花などの自然も意識するようになってきました。

ちなみに、ポピーは色とりどりの花を咲かせるケシ科の植物の総称で、その実から採れる乳液には、入眠や麻痺の作用があることから、紀元前400年頃のギリシアでは麻酔薬や睡眠導入薬として用いられていたそうです。花言葉も「なぐさめ」「心の平静」「いたわり」「思いやり」などその効能にちなんだものが多くあり、相手をいたわる気持ちや、助けたい気持ちを表現するときによく贈られる花だそうです。

＜本日の題材＞
APPLY（SQL Server）

今回は、APPLYという演算子について取り上げてみます。
以前、テーブル値関数について記事として載せたことがありますが、あるテーブルのデータの値と、その値によって取得されるテーブル値関数の結果を組み合わせて抽出したいという場合に、APPLY という演算子を使用することができるようです。（SQL Server 2005から）
SQLのFROM句の後に、まずテーブルを記載し、「CROSS APLLY」、もしくは「OUTER APPLY」の後側にテーブル値関数を指定するかたちになり、SELECT のほうではテーブルの列とテーブル値関数の結果の列を含めるかたちになります。
私も今まであまり使ったことがなかったのですが、今回、試してみようと思います。

例）
使用するテーブルは、以前ブログで使ったことのある、日本の都道府県、山、川、湖などの大きさや高さや長さなどをデータにしたランキングのデータを使ってみたいと思います。
今回使用するテーブル（「ランキング」「ランキング区分」）の定義は、下記のような設定。

データを抽出してみると、
「ランキング区分」テーブルは、

「ランキング」テーブルは、

今回使用するテーブル値関数は、「区分NO」の値からランキングテーブルのデータを抽出する簡単なものです。定義は以下：

CREATE FUNCTION [dbo].[ufn_ランク上位取得]
(
@p区分NO DECIMAL(4,0)
) RETURNS @ランク上位 TABLE (
区分名 VARCHAR(40),
ランキング DECIMAL(3,0),
名称 VARCHAR(40),
数値 DECIMAL(8,1)
)
AS
BEGIN
INSERT @ランク上位
SELECT
K.区分名
,RANK() OVER (ORDER BY R.数値 DESC)
,R.名称
,R.数値
FROM dbo.ランキング R
JOIN dbo.ランキング区分 K ON R.区分NO = K.区分NO
WHERE K.区分NO = @p区分NO;

RETURN;
END;

このテーブル値関数にパラメータを直接指定して抽出すると、

SELECT * FROM dbo.ufn_ランク上位取得(1)
ORDER BY ランキング;

これを、「ランキング区分」テーブルの「区分NO」をテーブル値関数にセットして、「区分NO」毎にランキング情報を抽出するように、APPLY句を使用すると、以下のように抽出できます。

SELECT K.区分NO, K.区分名, K.単位, R.ランキング, R.名称, R.数値
FROM dbo.ランキング区分 K
CROSS APPLY dbo.ufn_ランク上位取得(K.区分NO) AS R
ORDER BY K.区分NO, R.ランキング;

ここで、「ランキング区分」テーブルにあっても、テーブル値関数で結果のないものは抽出されないのですが、外部結合のように、上記のようなデータも左側のランキング区分の情報は表示し、テーブル値関数側の結果は null で表示したい場合には、「OUTER APPLY」演算子を使用します。

SELECT K.区分NO, K.区分名, K.単位, R.ランキング, R.名称, R.数値
FROM dbo.ランキング区分 K
OUTER APPLY dbo.ufn_ランク上位取得(K.区分NO) AS R
ORDER BY K.区分NO, R.ランキング;

上記のように、「区分NO」=2, 3 のテーブル値関数では抽出されないデータも表示されることが確認できます。

ちなみに、APPLY演算子の右側は必ずテーブル値関数である必要はなく、上記の内容は、以下のようなSQLでも抽出できます。（CROSS APPLYの例）

SELECT K.区分NO, K.区分名, K.単位, RK.ランキング, RK.名称, RK.数値
FROM dbo.ランキング区分 K
CROSS APPLY
(SELECT
K.区分名
,RANK() OVER (ORDER BY R.数値 DESC) AS ランキング
,R.名称
,R.数値
FROM dbo.ランキング R
WHERE R.区分NO = K.区分NO) AS RK
ORDER BY K.区分NO, RK.ランキング;

今まではあまり使ったことはなかったのですが、今後は、使えるときもあるのではないかと思います。

今日は以上まで

にほんブログ村

DB & SQL 技術ブログ

月別アーカイブ: 2018年6月

動的管理ビュー（SQL Server）

APPLY（SQL Server）

主にSQLについて書いていきたいと思います。