ITエンジニアの成長ブログ

ITエンジニアとして行う勉強の発信&日々の生活で体験した楽しいことをゆるく発信

SQL Server実行計画のコストについて

今回は、いつも実行計画を取得する際に目にする「コスト」について気になりましたので調べてみました。

以下のように赤枠で囲っているところに、色々なコストの情報が出力されています。実際にこの情報をどのように受け止めれば良いか分からなかったので今回この記事を書いてみました。

SQL Serverの実行計画のコスト

結論から言えば、このコストから具体的な処理時間やその負荷などを把握することはできない。また、複数の環境で比較するための値としても使用できない。そのため、あくまでも参照値という位置づけのようです。

また、このコストは推定実行プランから算出されているため実際の処理コストとは異なることがあるということを念頭に置いておく必要があるということです。

結果として、まったく意味がない値なのかというとそうでもなくて、クエリ内でのボトルネックを把握するためには使用できるみたいなので、上手に使いこなしていきたいと思いました。

以下のブログ記事をとても参考にさせていただきました。
SQL Server のクエリ実行プランのコスト値について at SE の雑記

SQL Serverの実行計画をテキストで表示する

前回は、以下の記事でSQL Serverの実行計画をグラフィカルに表示する方法を見ていきました。
SQL Serverの実行計画をグラフィカルに表示する - ITエンジニアの成長ブログ

今回は、タイトルどおりグラフィカルではなくテキストベースの結果で取得する方法を見ていきたいと思います。最初にお話しておくと、この方法どちらで取得しても同様の内容が取得できるので、自分の好きな方法で取得すれば良いかなと思います。

テキストベースで実行計画を取得する

それでは、さっそく実行計画をテキストで取得する方法を見ていきましょう。

まずは、「SET STATISTICS PROFILE」で取得する方法を試してみます。
SET STATISTICS PROFILE (Transact-SQL) - SQL Server | Microsoft Learn

使い方としては以下のようにします。「SET STATISTICS PROFILE ON」ステートメントを実行することで、通常の実行結果に加えて実行計画が追加で表示されるようになります。以下のように、「SET STATISTICS PROFILE OFF」の間のステートメントはすべて実行計画が追加で表示されます。

SET STATISTICS PROFILE ON;
GO
-- ここに実行計画を取得するSQL文を記載する START
SELECT * FROM dbo.Table_1;
-- ここに実行計画を取得するSQL文を記載する END
GO
SET STATISTICS PROFILE OFF;
GO


それでは、実際の環境で試してみます。今回は、Microsoftが提供しているサンプルデータベース"AdventureWorks2016"の"[Person].[Address]"テーブルのすべてのレコードを取得するSQLの結果を表示しています。

テキストベースで実行計画を取得した結果1

いかがでしょうか。結果ペインの上段は、実際のクエリの実行結果が表示され、下段になにやら追加で表示されているのが見えます。こちらがテキストベースの実行計画になります。

SQLを実行せずに、実行計画を取得する

テキストベースで取得する方法をもう一つ紹介したいと思います。先ほどは、実際のクエリも実行して実行計画を取得しました。今回紹介する方法は、実際にクエリは実行せずに実行計画を取得する方法です。
SET SHOWPLAN_TEXT (Transact-SQL) - SQL Server | Microsoft Learn

以下のようにします。「SET SHOWPLAN_TEXT ON」ステートメントを実行することで、そのクエリの実行計画のみが追表示されるようになります。以下のように、「SET SHOWPLAN_TEXT OFF」の間のステートメントはすべて実行計画が表示されます。

SET SHOWPLAN_TEXT ON;
GO
-- ここに実行計画を取得するSQL文を記載する START
SELECT * FROM dbo.Table_1;
-- ここに実行計画を取得するSQL文を記載する END
GO
SET SHOWPLAN_TEXT OFF;
GO


それでは、こちらも実際の環境で試してみます。

テキストベースで実行計画を取得した結果2

こちらは上段に実行計画を取得する対象のクエリが表示され、下段には実行計画が表示されています。この方法はクエリの実行方法にのみ関わる情報しか表示されないため、非常にコンパクトな出力結果です。

上記の結果よりさらに、詳細情報を取得するためには以下のように実行します。
SET SHOWPLAN_ALL (Transact-SQL) - SQL Server | Microsoft Learn

テキストベースで実行計画を取得した結果3

構文はほぼ同じですが、「SET SHOWPLAN_ALL」となっており"ALL"が先ほどの簡易な出力結果となる取得方法と異なります。出力結果の細かい説明は割愛しますが、さきほどと比べてたくさんの情報を取得することができます。通常であれば、こちらを使用すれば良いと思います。

ついでにもう一つ説明しておくと、以下のような構文もあります。
SET SHOWPLAN_XML (Transact-SQL) - SQL Server | Microsoft Learn

テキストベースで実行計画を取得した結果4

以下の実行結果のXMLはリンクとなっていて、押下すると以下のようにグラフィカルな結果を表示することができます。

グラフィカルな実行計画

おわりに

いかがでしょうか。今回はSQL Serverの実行計画をテキストベースで取得する方法を見ていきました。

パフォーマンスチューニングでは、ほぼ必ず見ることになるので上手に使っていきたいですね。

今回はこの辺で失礼いたします。最後までお読みいただきありがとうございました。

SQL Serverの実行計画をグラフィカルに表示する

本日は、SSMSでSQL Serverの実行計画をグラフィカルに表示する方法を簡単に紹介したいと思います。

テキストベースで表示する方法は以下の記事になりますので、よろしければ合わせてご確認いただければ嬉しいです。
SQL Serverの実行計画をテキストで表示する - ITエンジニアの成長ブログ

推定実行プランを取得する

まずは、推定実行プランを取得する方法を紹介します。これは、実際にクエリを実行することなくグラフィカルな実行プランを取得することができます。手軽に確認したいケースにぴったりだと思います。
推定実行プランの表示 - SQL Server | Microsoft Learn

実際に取得する方法とその表示を見ていきます。SSMSを開き、クエリシートに実行計画を確認したいクエリを記載し、下記の赤枠のボタンを押下します。

推定実行プランの実行方法

すると、推定実行プランを取得した結果が以下のような形で表示されると思います。今回は、Microsoftが提供しているサンプルデータベース"AdventureWorks2016"の"[Person].[Address]"テーブルのすべてのレコードを取得するSQLの結果を表示しています。

推定実行プランの実行結果

実行計画の処理順は右から左となるようです。今回は、「Clustered Index Scan」とあるとおり、"[Person].[Address]"テーブルのクラスターインデックスをスキャンして実際のレコードを取得していると解釈することができます。

また、以下のようにアイコンをマウスオーバーするとその処理の詳細情報を見ることができますね。内容としては、どのくらいのコストになりそうか、読み取りするレコードの予測はどの程度か、処理対象のオブジェクトは何か、この処理で何を出力するのかなどが見えます。

アイコンをマウスオーバーする1

今回は、一つのテーブルを単純なインデックスのフルスキャンなのでとてもシンプルな実行計画となりましたが、複雑なクエリの場合は多くのアイコンが表示されることがあります。

実際の実行プランを取得する

次に、実際の実行プランを取得する方法を紹介します。先ほどの推定実行プランの取得では、クエリは実際に実行されることはありませんでした。しかし、実際の実行プランを取得する場合には名前の通り実際にクエリが実行されて実行プランを取得することになります。
実際の実行プランの表示 - SQL Server | Microsoft Learn

推定実行プランではクエリ実行していないため、あくまで予測の結果となっていましたが、こちらの取得方法では実際のリソース使用状況を含めた結果を取得することができるため、正確な情報を取得したい場合はこちらを選択するのがいいかもしれません。

こちらも実際に取得する方法とその表示を見ていきます。SSMSを開き、クエリシートに実行計画を確認したいクエリを記載し、下記の赤枠のボタンを押下します。こちらのボタンを押下しておくことで、クエリを実行すると実際の実行プランも合わせて取得してくれるようになります。

実際の実行プランを実行方法

上記の設定後、通常どおりクエリを実行すると以下のとおりクエリ結果が表示されると思います。

実際の実行プランを取得1

上記をよくみると、「実行プラン」というタブが表示されていると思います。それを選択すると推定実行プランと同様な実行計画をグラフィカルに見えることができます。

実際の実行プランを取得2

基本的な見方は先ほどの推定実行プランと同様です。一つ補足としては、アイコンをマウスオーバーして見れる情報に若干の違いがあります。先ほどはあくまで予測でしたが、こちらは実際の実行結果による情報も合わせて取得されているということだと思います。

アイコンをマウスオーバーする2

おわりに

いかがでしょうか。簡単にSSMSで実行計画をグラフィカルに表示する方法を見ていきました。実際には表示した実行計画を解読する必要がありますが、そこらへんの細かい話は今回は割愛します。正直、めちゃくちゃ読み込みできるわけではないのでそこらへん詳しくなったら紹介したいなと思っています。

今回は、実行計画をグラフィカルに表示する方法を見ていきましたが、実はテキストベースでも取得可能です。グラフィカルな結果より、やや解釈するのが難しいかもしれませんが、慣れればテキストベースの方が良いかもしれません。こちらはまた別の機会に紹介したいと思います。

今回も最後までお読みいただきありがとうございました。今回はこの辺で失礼いたします。

「SQL実践入門 第10章 インデックスを使いこなす」の読書メモ

SQL実践入門の書籍の中の第10章を読んでみました。
www.amazon.co.jp

個人的に気になるところや、重要な部分を抜粋しながら紹介していきたいと思います(完全な個人的メモです)。

RDBで使われるインデックスの種類

RDBでは基本的には以下3つの種類のインデックスが使用されるようです。

  • B-treeインデックス
  • ビットマップインデックス
  • ハッシュインデックス

インデックスに上記のような種類が存在することは知っていましたが、正直どういう時にどのインデックスを使えば良いのかはわかっていませんでした。書籍ではそこの使い分けの細かい部分までは記載されていませんでしたが、通常であれば「B-tree」インデックスのみを知っていれば良さそうです。

どんなときにもベストなインデックスというわけではないけど、だいたい平均点がとれるようなインデックスという感じみたいです。

インデックスを有効活用するための条件

インデックスは必要なデータを取得するために作成するものですが、むやみに作成すれば良いわけではありません。誤ったインデックスを作成することで、逆に性能が悪化することもあります。そこらへんの話をしていきます。

インデックスをどの列に作成すればよいかの一つ目のポイント「カーディナリティ」

カーディナリティは値のばらつき具合を示す概念です。

たとえばあるネットショッピングのシステムで、注文テーブルがあるとします。注文テーブルでは、主キーとして「注文番号」を設定しているとします。主キーなのでもちろん注文テーブル内で注文番号は一切重複しません。
この場合には、「カーディナリティが高い」と表現します。

逆に、たとえば「商品名」は注文テーブルではまま重複することが考えられます(たくさんの人が同じ商品を買うことはよくあると思います)
この場合には、「カーディナリティが低い」と表現します。

インデックスは、「カーディナリティが高い」列に作成することがインデックスに選択する列の一つのポイントになります。
上記の例では、「商品名」ではなく「注文番号」にインデックスを作成するのが良いということになります。

インデックスをどの列に作成すればよいかの2つ目のポイント「選択率」

選択率とは、絞り込む列の条件が全体からみてどの程度絞り込むことができるかを示す概念です。

先ほどの注文テーブルの例で言えば、「注文番号」は主キーであるため一意となるため選択率は非常に低いことになります。
逆に「商品名」は選択率は注文番号に比べれば、高くなると思います。

インデックスは、「選択率が低い」列に作成することがインデックスに選択する列の2つ目のポイントになります。
上記の例では、「商品名」ではなく「注文番号」にインデックスを作成するのが良いということになります。また、選択率としては5~10%前後というのが目安ということみたいです。全体のテーブルの半分以上が同じ値を持つ列にインデックスを設定しても、選択率が高くなるのでインデックスではなくテーブルフルスキャンの方が速くなる可能性があるみたいですね。

インデックスによる性能向上が難しいケース

ここでは、インデックスによる性能向上が難しいケースを紹介していきます。

絞り込み条件が存在しない

そもそも要件として、絞り込みするための条件がないケースですね。このようなケースでは、絞り込み条件がないのでもちろんインデックスは使えません。かなり極端なケースですが、そのようなことももちろんあると思います。

ほとんどレコードを絞り込めない

これは、先ほどのケースでいえば注文テーブルの「注文ステータス」と呼ぶ列が存在するとします。データの抽出要件で、お届け済みの注文をすべて取得したいといった場合には、注文テーブルの多くのレコードが対象となります。

ある程度システムが運用している場合では、注文テーブルはほぼお届け済みですからほとんどレコードを絞り込むことができません。
これは先ほど紹介した「選択率」が高い状態となっていまい、仮にインデックスを作成していても逆効果になるパターンになりえるみたいです。

個人的には、このケースに該当するパターンをシステムでよく見たことがあります。カラムの種類が2つしかない列にインデックスとしている実装などもありました。これは上記のようにインデックスが逆効果になっているパターンだったのかと、勉強になりました。最初のうちは、抽出条件にある列にインデックスをとりあえず作成すればいいんでしょ、とか思っていたので何も知らないことは怖いですね。。。

他にも入力パラメータによって選択率が変動するケースがあります。これは、システムを使用するユーザーの入力内容によって、選択率が大きく変わるケースですね。たとえば、今回も先ほどの注文テーブルを例にしますが、注文日を列に持っているとします。

ユーザーによって、注文日の範囲検索ができるようなシステムである場合に、注文日を「2022-12-01」とすれば1日分の注文データしか検索されないため、選択率は低くなります。
しかし、注文日を「2022-01-01」~「2022-12-31」の範囲検索とした場合は1年分の検索となり、先ほどと比べて選択率は高くなってしまいます。

インデックスが使えない検索条件

インデックスを上手に適用するためには、SQLの書き方に一定の条件があります。すべてのRDBで同じではないと思いますが、一般的なものを紹介していますね。個人的には聞いたことがあったので、さらっと紹介します。

LIKE述語の中間一致・後方一致

これはよく言われるケースで、LIKE述語を使うあいまい検索では、前方一致(AAA%)のみインデックスが効きます。中間一致(%AAA%)、後方一致(%AAA)ではインデックスが効きません。

インデックスの列で演算を行っている

たとえば、注文テーブルに「注文数量」列があるとして「WHERE 注文数量 * 5 > 10」のような検索条件の場合にはインデックスが効きません(どんな条件なんだというのは置いておいて・・・)。

インデックスの列に関数を使用している

こちらは、注文テーブルの「商品名」列で言えば、「WHERE LENGTH(商品名) > 10」のような検索条件の場合にはインデックスが効きません。

インデックスの列で否定形を用いている

こちらも、注文テーブルの「商品名」列で言えば、「WHERE 商品名 <> 'パン'」のような検索条件の場合にはインデックスが効きません。

インデックスが使えない場合どう対処するか

今までの内容はインデックスを使う上で基本的な部分かなという感じでしたので、なんとな~く知っているものでした。ここら辺はとても参考になりました。インデックスを上手く使えない場合は、大きく2通りがあるということです。それぞれ簡単に紹介します。

  • アプリケーション設計で対処する
  • あくまでインデックスで対処する
アプリケーション設計で対処する「外部設計による対処」

ここで言及しているのは、インデックスが効かないようなSQLを実行しないようにシステムの画面設計を変えてしまおうというものです。なるほど、自由な条件で検索できるシステムでは性能劣化となるクエリが発生してしまうので、適切な必須条件をあらかじめ決めておくことで、適切なクエリのみが実行されるように制御するやり方ですね。

これしかないよなと思いつつも、最初からどの程度までは問題ないのかなどが分からなかったり、ユーザーによっては許してくれないこともありそうな気がしたりしますが、そこらへんの妥協点を見つけていくのもできるエンジニアなんでしょうね。本にも記載ありましたが、システムがほぼ完成した後にこのような入力制御の調整をすると、ほぼNGとなりそうな感じですね。難しい。。。

アプリケーション設計で対処する「データマート」

アプリケーション設計で対処するもう一つがデータマートを作ることです。

これは、大きなテーブルで必要なデータはその一部のはずなのでそのデータを切り離して、相対的に小さいテーブルを作成してしまうことです。そうすることで、アクセス対象データが小さくなるので、結果I/O量が減り性能向上につながるということですね。

こちらもアプリケーション修正が大変かなと思いつつも、画面を変更しないのでユーザーに何か言われることはない回避方法だなと思いました。外部設計の変更ができない場合の一つの修正案となりそうです。

データマートの注意点として、挙げられていたのがデータの鮮度、つまり作り直しをどのくらいの頻度でするかや、そのデータマートの数ですね。数が多すぎて訳が分からなくならないように注意が必要とのことです。データマート今まで作っているシステムを見たことはないのですが、設計書も作られなさそうなので、後で入った人がよくわからなくなりそうな気はします。

あくまでインデックスで対処する「インデックスオンリースキャン」

アプリケーション設計はしたくない場合は、こちらの方法しかありません。いい感じのインデックスを作ることで、性能向上させる技ですね。

インデックスオンリースキャンとは、実テーブルにはアクセスせずにインデックスのみでアクセスを完結させることのようです。SQL Serverでは、カバリングインデックスと呼んでいるをよく見たり聞いたりしますがそれのことみたいです。

通常であれば、インデックス→実テーブルのアクセスによって目的のデータを取得しますが、カバリングインデックスであればインデックスのみアクセスすることですべてのデータを取得することができます。それはつまり、インデックスに取得する列をすべて含ませるやり方ですね。

このやり方だけであれば、うまくいけばアプリケーションを修正しないでデータベースの設定(インデックス作成)のみなのでとても楽ですね。
注意点として、更新のオーバーヘッドが増える、仕様変更で列が増えたら、結局テーブルへのアクセスが必要になってしまうなどが挙げられてしました。

おわりに

結構なるほどなと思うところが多かったので、とても勉強になりました。パフォーマンスチューニングするときの基礎として、頭に入れておきたい内容だと思いました。パフォーマンスチューニングさらっとできる人はとても、すごいなぁと思います。

少しでもそんな人になれるように日々努力したいと思います。
最後までお読みいただきありがとうございました。今回はこの辺で失礼いたします。

Switchのゲームを売るときにセーブデータの削除は必要?

今日は、いつものIT関連とは全く関連ない話題ですがお話させていただきます。

先日、最近全く遊ぶことがないSwitchのパッケージ版のあるソフトを売ろうと思い、セーブデータを削除しようと思いました。

そこでふと、「セーブデータってゲームカードにあるのか。はたまた、Switch本体もしくは、microSDカードに保存されるのか。」

どちらか分からなかったので、調べてみました。

結論から、「セーブデータはゲームカードには保存されない」ということです。以下任天堂の公式サイトに記載がありました。
データの管理|Nintendo Switch サポート情報|Nintendo


該当の箇所を抜粋しますが、以下の通りです。マトリクスの中のゲームカード列には、すべて「×」となっていますね。つまり、ゲームカードには何も保存されないということになりそうです。

Switchのデータの保存先

なので、Switchのパッケージ版のソフトを購入した場合はセーブデータはゲームカードには保存されないので安心して売ることができますね。

また、上記の通り実際のセーブデータはSwitch本体もしくは、microSDカードに保存されているので、売ってしまう場合は必要ないと思いますので合わせて削除しておく感じですかね。

おわりに

小学生とか中学生くらいに購入するゲームソフトといえば、ゲームカードにすべてセーブデータが保存されていたはずなのでちょっと驚きました。Switchのゲームカードはめちゃくちゃ小さいですが、その小さくするためにもセーブデータの保存先が別になったとかなんでしょうか?

今回はここまで。最後までお読みいただきありがとうございました。

SQL Serverのリンクサーバーについて

今回は、SQL Serverのリンクサーバーについて調べた内容を簡単にまとめておこうと思います。

リンクサーバーとは?

リンクサーバーを利用することで、今ログインしているデータベースから他のデータベースへ、ログイン手続きなしでアクセスできるようになります。
また、どうやら接続できるのは SQL Server だけでなく、他のRDBMSであるOraclePostgreSQL なども問題なく接続が可能とのことです。

リンクサーバーを作成すると、SSMSのオブジェクトエクスプローラーの以下の箇所に表示されます(以下画像ではリンクサーバーを作成していないので何も表示されていません)。

SSMSオブジェクトエクスプローラーで見るリンクサーバー


リンクサーバーの作成方法等は、以下公式サイトを参考にすれば問題なく作成できると思います。
リンク サーバー (データベース エンジン) - SQL Server | Microsoft Learn

私が経験したリンクサーバーの使い方としては、別インスタンスに存在するテーブルを参照したい要件があったため、リンクサーバーとシノニムを使って、別インスタンスにあるテーブルをあたかもローカルにあるテーブルのように参照できるようにしたりしました。

おわりに

いかがでしょうか。小規模な環境の場合は、一つのインスタンスですべて完結することが多いのでリンクサーバーの知識は特に必要にならないと思います。

ただし、大規模な環境だと複数のデータソースから、データを取得したい要件がもしかしたらあるかもしれませんので、そのときに今回の知識が少しあると良いかもしれません。

今回はここまで。最後までお読みいただきありがとうございました。

SQL Serverのテーブルヒント「NOWAIT」について

今回は、SQL Serverのテーブルヒントの一つである「NOWAIT」を簡単に確認してみようと思います。

以前、SQL Serverのロックタイムアウトの設定について以下の記事で触れました。
SQL Serverのロックタイムアウトについて - ITエンジニアの成長ブログ


上記のようなロックタイムアウトの設定に関係なくロック待ちを一切せずに、エラーとするようなテーブルヒントが「NOWAIT」です。
テーブル ヒント (Transact-SQL) - SQL Server | Microsoft Learn


簡単に実機で挙動を確認してみます。まずは「NOWAIT」を付けない場合です。
あるセッションで以下のように、「BEGIN TRAN」を使用し、COMMITまたはROLLBACKせずにロックを保持しておきます。

あるテーブルのロックを保持しておく

そして別のセッションではまず以下のとおり、「lock_timeout」の値を3000(ミリ秒)に設定しておきます。

DBCC USEROPTIONS実行結果

そのあとに最初のセッションと同じレコードを更新しようとします。すると以下のようにロックタイムアウトでエラーが発生しました。

ロックタイムアウトエラー

エラーのタイミングは先ほど設定した3000(ミリ秒)後でした。このように、ロックタイムアウトの設定値まで待ってもロックが解放されない場合は、エラーが発生します。

それでは、テーブルヒント「NOWAIT」を付けてもう一度エラーになったクエリを実行してみます。エラーのメッセージや内容はまったく同じですが、今回は3000(ミリ秒)を待つことなく即座にロックタイムアウトが発生しました。

NOWAITで即座にエラー

構文としては、簡単でテーブル名の後に以下のような文を付与します。

WITH (NOWAIT)


また、今回のようなテーブルヒント「NOWAIT」を使わないでも、例えば以下のようにSETオプションを設定すれば同じような挙動になります。ロックタイムアウトが=0(ミリ秒)なので、一切待たないということですね。

SET LOCK_TIMEOUT 0

おわりに

いかがでしょうか。テーブルヒントは、普段は使うことがないかもしれませんが場合によっては必要になってしまうケースもあるかと思います。
今回のテーブルヒント「NOWAIT」は要件によって使えるかもしれませんので、覚えておいて損はないかと思います。

今回はここまで。最後までお読みいただきありがとうございました。