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

今年もいよいよ残り2日。
会社も休みに入り、1年間を振り返り、反省する時期になりました。時間を取って1年を総括し、来年に向けてしっかりと準備していければと思いますが、今年は正月に実家にも帰って、成長した孫の姿を親に見せたいと思います。
写真は、会社で7月に行った、飯能市のムーミンバレーパークで撮った写真です。家族も連れて行ってもいいということで、子供たちも写真を撮りまくっていましたね。感謝です。

さて、今年のブログは、ほんの数回しか上げることができなかったのですが、データベーストリガーについて、参考になったという方がいましたので、次は早いうちにSQL Server版も取り上げていけたらと思っています。

来年も、皆様にとって良い年となりますように、心からお祈りいたします。

今日は以上まで

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

今年もあっという間にあと数日というところまで来てしまいました。今年の後半は仕事もかなり忙しくなり、なかなか時間も取れず、ブログの更新ができませんでしたが、気力を振り絞って書いています。
写真は、今年田舎に帰省した時に寄った出雲大社です。久しぶりに父親と一緒にお参りしたのですが、父親が子供のころは、正月に自転車で数時間をかけてお参りに行ったという話を聞き、今は車で1時間もかからずに行けるので、昔は大変だったんだなと改めて感じました。中を歩くと、旧暦の10月に全国から出雲大社に集う八百万の神様がお泊りになるという十九社という社があるのも初めて知りました。出雲地方以外、旧暦の10月のことを普通は神無月といいますが、出雲地方では神在月と言います。おもしろいですね。

さて、今年は元号が新しく令和になった記念すべき年でしたね。私としては、特にラグビーワールドカップでは、ONE TEAMという言葉がとても心に残りました。思い出せばいろいろなことがありましたが、大晦日に1年を振り返ってよかったと言えるように、最後まで頑張ろうと思います。皆さんも頑張りましょう！

＜本日の題材＞
照合順序（SQL Server）の異なるデータベース間のジョイン

SQL Serverの照合順序について、以前取り上げましたが、今回は、異なる照合順序のデータベース間でのデータのジョインをしたいケースが発生した場合に、可能なのかどうかについて確認してみました。これは、新しく作成したデータベースから、元々運用していた基幹のデータベースのテーブルを参照して抽出を行おうとしたら、照合順序が違うためにエラーが発生したという問合せを受けたことがあったので、自分でも一度試してみたいと思ったためです。照合順序とは、データの文字の大小関係を比較する場合の基準となるものをいうということで、前回説明しましたように、複数のテーブル間のジョインを行う際にも、重要な要素になります。

例）
照合順序が「Japanese_CI_AS」の既存のデータベース「BLOG」とは別に、「Japanese_90_BIN」という新しいデータベース「BLOG2」を作成します。

 ここで、元々「Japanese_CI_AS」の照合順序のデータベース「BLOG」にあるテーブル「BUMON_M」と同じレイアウトのテーブルを、新しく作成した照合順序が「Japanese_90_BIN」のデータベース「BLOG2」に作成して、データも登録します。

その後、「SYAIN_M」と「BUMON_M」とをジョインしてデータを抽出する処理を行ってみます。

まず、「BLOG」データベース内でジョインする場合は、

SELECT b.BUMON_CD, b.BUMON_NAME, s.SYAIN_CD, s.SYAIN_NAME
FROM dbo.SYAIN_M s
JOIN dbo.BUMON_M b
ON s.BUMON_CD = b.BUMON_CD
ORDER BY b.BUMON_CD, s.SYAIN_CD;

 上記を、「BLOG」データベースの「SYAIN_M」と先ほど作成した「BLOG2」データベースの「BUMON_M」とをジョインしてデータを抽出する処理を行ってみます。

すると、下記のようなエラーが返ってきます。
「メッセージ 468、レベル 16、状態 9、行 4
equal to 操作の "Japanese_90_BIN" と "Japanese_CI_AS" 間での照合順序の競合を解決できません。」

上記の場合に正しく結果を出す方法は、データベースの照合順序を同じになるように、データベースを作り直すという方法もありますが、ジョインする項目について、COLLATE句を使用する方法があります。

SELECT b.BUMON_CD, b.BUMON_NAME, s.SYAIN_CD, s.SYAIN_NAME
FROM BLOG.dbo.SYAIN_M s
JOIN BLOG2.dbo.BUMON_M b
ON s.BUMON_CD = b.BUMON_CD COLLATE Japanese_CI_AS
ORDER BY b.BUMON_CD, s.SYAIN_CD;

エラーなく、結果が抽出されたことが確認できます。ただし、ジョインなどで必要な項目については全て、SQLの中でCOLLATE句の設定が必要になるため、面倒な部分もあります。

ちなみに、データベース自体の照合順序を変更する場合は、前回のときに取り上げたように、ただデータベースの照合順序を変更するだけではなく、既存のテーブルの各項目について、COLLATE文で照合順序を指定してALTER TABLE文を実行する必要がありますので、照合順序を正しく設定した空の新規データベースを作成した後に、テーブルの作成と、既存データの登録を行ったほうが早いケースもあると思います。

今日は以上まで

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

今年は、例年になく暑い5月でしたね。26日には北海道で５月としての全国の観測史上最高気温となる３９・５度を観測するなど、異常な暑さでした。今からこの分だと、今年の夏はどうなるか？と心配してしまいます。

5月の中頃（5/19）はまだそれほどではなかったので、知人のご家族と一緒にバーベキューに行ってきましたが、天気も良く快適でした。
写真は埼玉県の羽生スカイスポーツ公園というところで、公園の名前にもあるように、スカイスポーツへの関心をもってもらおうという目的のもと整備された公園で、毎週土日祝日にはグライダーの飛行活動も行っていて、そこのクラブに入会すれば、実際にグライダーに乗ってスカイスポーツを楽しむことも出来るようです。

当日は、母の日も近かったので、日頃苦労している妻に感謝しようということで、準備は全てお父さんと子供たちで行って、妻には食べて楽しんでもらおうという企画で行いました。サプライズの花のプレゼントも用意して、妻もとても喜んでくれました。
たまにはごまをすっておかないと！

＜本日の題材＞
データベーストリガー2（Oracle）

ORACLEでのデータベーストリガーについて、前回取り上げましたが、前回は代表的なものとして、特定の表に対してDML操作(INSERT, UPDATE, DELETEなど)が行われるときに起動する「DMLトリガー」について取り上げてみました。
今回は、DDL操作(CREATE, ALTER, DROPなど)により起動する「DDLトリガー」について、試してみたいと思います。

例）
今回は、Oracleのデータベースについて、CREATE、DROP、ALTERという操作を行ったときに、どのユーザーが、どのオブジェクトに対して、いつ操作を行ったかをログに記録するというトリガーを作成してみます。
このとき、データベースに対してのトリガーを作成するためには、ADMINISTER DATABASE TRIGGER権限が必要です。
今回は、DBAロールを持つ、「test_admin」というユーザーでトリガーを作成します。

test_adminでログインした後、まず、処理のログを登録する「database_ddl_log」テーブルを作成します。

CREATE TABLE database_ddl_log (
user_name VARCHAR2(30),
activity VARCHAR2(20),
obj_name NVARCHAR2(30),
obj_type VARCHAR2(20),
event_date DATE
);

次に、トリガーを作成します。

CREATE OR REPLACE TRIGGER db_ddl_record
AFTER CREATE OR DROP OR ALTER ON DATABASE
DECLARE
   V_SYSEVENT   VARCHAR2(20);
   V_OBJ_OWNER   VARCHAR2(30);
   V_OBJ_NAME   VARCHAR2(30);
   V_OBJ_TYPE   VARCHAR2(20);
BEGIN
   V_SYSEVENT   := ora_sysevent;         -- イベントの取得
   V_OBJ_OWNER := ora_dict_obj_owner;   -- オブジェクトの所有者の取得
   V_OBJ_NAME   := ora_dict_obj_name;   -- オブジェクト名の取得
   V_OBJ_TYPE   := ora_dict_obj_type;   -- オブジェクトタイプの取得

INSERT INTO database_ddl_log
(user_name, activity, obj_name, obj_type, event_date)
VALUES (V_OBJ_OWNER, V_SYSEVENT, V_OBJ_NAME, V_OBJ_TYPE, SYSDATE);
END;
/

2行目に、AFTER CREATE OR DROP OR ALTER ON DATABASE と記載していますように、DATABASE全体において、CREATE、DROP、ALTER 文が実行された後に、起動するトリガーになります。そして、9行目から12行目までは、ora_ で始まるイベント属性関数を参照することで、必要な情報を一旦変数に格納し、最後にテーブルに登録します。

それでは、「BLOG_TEST」ユーザーでログインして、(1)新しくテーブルを作成、(2)定義の変更、(3)テーブルの削除をしてみます。

(1)   テーブルの作成
CREATE TABLE test_tab(
id INT
,name NVARCHAR2(30)
,CONSTRAINT PK_test_tab PRIMARY KEY (id));

(2)   テーブル定義の変更
ALTER TABLE test_tab
MODIFY name NVARCHAR2(50);

(3)   テーブルの削除
DROP TABLE test_tab;

それでは、上記の操作がログとして残っていることを確認します。
「test_admin」ユーザーで接続して、「database_ddl_log」テーブルを確認してみます。

SET LIN 120
COL user_name FORMAT A15
COL activity FORMAT A15
COL obj_name FORMAT A20
COL obj_type FORMAT A10
COL event_date FORMAT A20

SELECT
user_name, activity, obj_name, obj_type
, TO_CHAR(event_date, 'YYYY/MM/DD HH24:MI:SS') event_date
FROM database_ddl_log
ORDER BY event_date;

ログテーブルを抽出したところ、先ほどの処理（テーブル作成、テーブル定義変更、テーブル削除）が、ログの履歴として登録されていることが確認できました。

今日は以上まで

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

桜の時期も終わってしまいました。ただ、今年は桜が咲いてから、少し寒かったので、桜がすぐには散らず、例年より長く、桜を楽しむことができたように思います。
東北や北海道などは、まだこれからがいよいよ本番ということなので、仕事で出張とかあればついでにお花見もできるのですが、そういう機会があるかな～。

＜本日の題材＞
データベーストリガー（Oracle）

データベーストリガーについて、まだブログで取り上げていなかったので、今回、取り上げてみたいと思います。
データベーストリガーとは、プロシージャやファンクションと同じように、データベースに格納されたPL/SQLのプログラムですが、プログラムなどから明示的にコールされて呼び出すのではなく、何らかのイベントをきっかけとして自動的に起動されるものです。

トリガーには、大きくは「DMLトリガー」「DDLトリガー」「その他」の3つの種類があります。
「DMLトリガー」は、特定の表に対してDML操作(INSERT, UPDATE, DELETEなど)が行われるときに起動するもの
「DDLトリガー」は、DDL操作(CREATE, ALTER, DROPなど)により起動するもの
「その他」については、
　・データベースにログインやログオフするとき
　・データベースの起動時や停止時
　・エラーの発生時
などで起動するものがあります。
一般的によく使用される「DMLトリガー」についてですが、文トリガー、行トリガーという種類があり、さらに、処理の前後のどちらで起動させるかという、「BEFOREトリガー」「AFTERトリガー」という分類があります。

例）
今回は、ユーザー管理用の「ACCOUNTS」テーブルというものがあったとして、それに対しての、INSERT, UPDATE, DELETE が行われるタイミング(AFTER)で、処理のログを「ACCOUNTS_LOG」テーブルに登録するトリガーを作成してみます。

まず、処理のログを登録する「ACCOUNTS_LOG」テーブルを作成します。

CREATE TABLE accounts_log(
log_date DATE
,old_account_id INT
,new_account_id INT
,action VARCHAR2(50));

次に、トリガーを作成しますが、「ACCOUNTS」テーブルに対しての、INSERT, UPDATE, DELETE が行われてデータが更新されるたびに、「ACCOUNTS_LOG」テーブルに、処理日、変更前後の「ACCOUNT_ID」、そして処理内容を記録させます。
今回は、行トリガーのAFTERトリガーとして作成します。

CREATE OR REPLACE TRIGGER accounts_change_trigger
  AFTER INSERT OR UPDATE OR DELETE
  ON accounts FOR EACH ROW
DECLARE
  log_action  accounts_log.action%TYPE;
BEGIN
  IF INSERTING THEN
    log_action := 'Insert';
  ELSIF UPDATING THEN
    log_action := 'Update';
  ELSIF DELETING THEN
    log_action := 'Delete';
  ELSE
    DBMS_OUTPUT.PUT_LINE('This code is not reachable.');
  END IF;

  INSERT INTO accounts_log (log_date, old_account_id, new_account_id, action)
    VALUES (SYSDATE, :OLD.account_id, :NEW.account_id, log_action);
END;
/

トリガーの中で、「FOR EACH ROW」を付けることで、行トリガーであることを示しています。そして、:OLD.列名、:NEW.列名とすることで、変更前と変更後の列名の値を取得することができます。
それでは、下記のように、「ACCOUNTS」テーブルへのデータの登録、修正、削除を順に行ってみます。

INSERT INTO accounts(account_id, name) VALUES(101, '山田　太郎');

INSERT INTO accounts(account_id, name) VALUES(102, '高橋　三郎');

UPDATE accounts SET NAME = '高橋　一郎' WHERE account_id = 102;

DELETE FROM accounts WHERE account_id = 101;

それでは、「ACCOUNTS_LOG」テーブルを確認してみます。

「LOG_DATE」項目の表示を時刻まで出すようにフォーマットを変更します。
ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';

データを確認します。
SELECT * FROM accounts_log
ORDER BY log_date;

たしかに、「ACCOUNTS_LOG」テーブルへの更新履歴が登録されています。
ここで、INSERTの場合は、変更前はデータがないので、old_accounti_id は NULL であり、DELETEの場合は、変更後はデータがなくなるので、new_accounti_id は NULLになることがわかります。

また、COMMITをする前にROLLBACKをしてみると「ACCOUNTS_LOG」のデータはどうなるでしょうか？

ROLLBACK;

再度、「ACCOUNTS_LOG」テーブルを確認してみます。

先ほど見えていたデータは、処理が取り消されたので、ログからも取り消されているのが確認できます。

今日は以上まで

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

先月、会社の同僚と海釣りに行ってきました。
横浜の金沢八景の近くの海で、釣り船を予約して楽しんできました。その日はとてもよい天気で、アジ、サバ、イシモチなどが結構たくさん釣れましたね。私は最初なかなか釣れなくて、悪戦苦闘していたのですが、隣にいた釣り好きの先輩においては、竿を投げればすぐに引きが来る感じで、本当にびっくりです。同じようにやっているつもりなのに、どこかが違うんですね。やはり釣りは奥が深い！それでも、後半は多少釣れたこともあり、楽しむことができました。
上の写真は、船から八景島シーパラダイスのジェットコースターを撮ったものです。
釣れた魚は、その日のうちにさばいて刺身にして食べました。アジがぷりぷりしてとっても美味しかった。魚好きの妻も、美味しいと喜んでくれたので、よかった。＼(＾▽＾)／

さて、今年も残りわずかとなりました。風邪など引かないで、よい年を迎えられますことをお祈りいたします。来年も、よろしくお願いいたします。

＜本日の題材＞
計算列（SQL Server）

SQL Serverで、計算列というものがあるということなので、試してみたいと思います。

例）
以前、身長と体重からBMI（肥満指数）をストアド・ファンクションを使って求めたことがありましたが、今回は計算列でこれを行ってみたいと思います。

CREATE TABLE dbo.syain_health(
id      INT
,height  DECIMAL(5,1)
,weight  DECIMAL(5,1)
,bmi     AS (
      weight /(height/100 * height/100))
,CONSTRAINT PK_syain_health PRIMARY KEY (id));

テーブル作成後に、テーブルの定義を、SQL Server Management Studio で確認すると、

最後の「bmi」項目が計算列になります。
それでは、データを登録してみます。このとき、計算列にはInsertしません。

INSERT INTO dbo.syain_health(id, height, weight) VALUES(1, 165.1, 61.2);
INSERT INTO dbo.syain_health(id, height, weight) VALUES(2, 174.4, 65.6);
INSERT INTO dbo.syain_health(id, height, weight) VALUES(3, 182.3, 71.3);
INSERT INTO dbo.syain_health(id, height, weight) VALUES(4, 168.8, 63.1);
INSERT INTO dbo.syain_health(id, height, weight) VALUES(5, 172.0, 84.9);

計算列にデータを登録しようとすると、下記のようにエラーが出ます。

INSERT INTO dbo.syain_health(id, height, weight, bmi) VALUES(6, 176.0, 73.5, 25.3);

 それでは、データをSELECTしてみます。

SELECT * FROM dbo.syain_health
ORDER BY id;

計算列で指定した「bmi」列は、自動的に計算されて抽出されていることが確認できます。

また、計算列にはインデックスを作成することもできます。
データを100万件ほど作成して、インデックスの有無によるレスポンスを比較してみます。

身長は、140センチ以上200センチ以内、体重は40キロ以上100キロ以内でランダムに作成してみます。このとき、RAND関数を利用してみます。RAND関数は、0～1までの範囲の乱数をfloat型で取得するものです。

DECLARE
  @v_count INT = 5;     -- id は既に1～5は作成済なので初期値を5

WHILE @v_count < 1000000
BEGIN
    SET @v_count = @v_count + 1;
 
   INSERT INTO dbo.syain_health(id, height, weight)VALUES
    (@v_count, ROUND(140+RAND() * 600/10,1), ROUND(40+RAND() * 600/10,1));
END;

再度データを確認してみます。

SELECT * FROM dbo.syain_health
ORDER BY id;

 件数を確認すると、

SELECT COUNT(*) FROM dbo.syain_health;

それでは、bmi の値が 20 ～ 25 の人の件数を確認します。
時間を計測するため、以下のコマンドを実行します。

SET STATISTICS TIME ON

SELECT COUNT(*) FROM dbo.syain_health
WHERE bmi BETWEEN 20 AND 25;

メッセージタブで表示されている時間は、
SQL Server 実行時間:
、CPU 時間 = 671 ミリ秒、経過時間 = 673 ミリ秒。

次に、インデックスを作成してみます。

CREATE INDEX IX_syain_bmi ON dbo.syain(bmi);

一度、データバッファキャッシュをクリアします。
DBCC DROPCLEANBUFFERS

再度、先ほどの処理を実行してみます。
SELECT COUNT(*) FROM dbo.syain_health
WHERE bmi BETWEEN 20 AND 25;

このときの時間を確認すると、
SQL Server 実行時間:
、CPU 時間 = 16 ミリ秒、経過時間 = 26 ミリ秒。

インデックスを作成することで、処理は速くなっていることが確認できました。

また、計算列は物理的にデータを保存することもできるということで、その場合には、テーブル作成時に、計算列の後ろにPRESISTED を付けます。

CREATE TABLE dbo.syain_health2(
id      INT
,height  DECIMAL(5,1)
,weight  DECIMAL(5,1)
,bmi     AS (
      weight /(height/100 * height/100)) PERSISTED
,CONSTRAINT PK_syain_health2 PRIMARY KEY (id));

インデックスも追加します。
CREATE INDEX IX_syain_health_bmi2 ON dbo.syain_health2(bmi);

その後、同様にデータを100万件作成して、先ほどと同様のことを行ってみます。

SQL Server 実行時間:
、CPU 時間 = 16 ミリ秒、経過時間 = 17 ミリ秒。

物理的にデータが保存されている場合とそうでない場合での処理時間の違いについては、それほど違いは無いようですが、多少速い感じでしょうか。

今日は以上まで

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

先日、妻の実家のある富山のほうに行く機会があり、そこできれいな夕焼けが見れたので、撮った写真です。車に乗っていた娘が、きれいだよ！というので、慌てて車の中から撮ったものです。
そのときも、本当に昼間は暑い日が続いていて、これは大変だと思っていましたが、その後もさらに暑さが増して、今年は本当に危険な暑さになってますよね。埼玉の熊谷では、41.1℃という、国内最高気温を更新するという状況になりました。クーラーが効いていないところでは、本当に大変かと思います。
皆さん、本当にお体には注意して頂きたいと思います。

＜本日の題材＞
インデックス再構築・再構成（SQL Server）

前回、SQL Serverの動的管理ビューを使用することで、パフォーマンスに影響があるクエリーの確認や不足しているインデックスなどを確認できることをブログで取り上げましたが、今回もその続きということで、取り上げてみたいと思います。
データベースはインデックスをうまく設定することで、抽出処理のレスポンスを効果的に向上させることができますが、当初それほど遅くなかった抽出処理が、いつの間にかとても遅くなっていることに気づくことがあります。その原因の一つに、インデックスの断片化というものがあります。

以前、大手企業の分析用のシステムを見ていたときがありました。日々基幹システム側から日次のデータが分析用のデータベースに送られてくるので、削除・登録などの処理を夜間に行って、分析用データベースを本番環境の前日終わり時点のデータにしていました。毎日、大量のトランザクションデータがあるため、夜間に送られてくるデータもかなりのボリュームがあり、夜間バッチ処理も結構な時間がかかっていました。
そして、分析用に作成したクエリーやプロシージャなどの処理が、作成した当初はそれほど遅くなかったのに、とても遅いと感じるようになることがありました。
原因を調べてみると、インデックスの断片化がかなりの割合で発生しており、ページ密度が大きく低下し、読み取りのページ数もその分多くなって、効率が悪くなっていたことが大きな要因でした。
当時は、SQL Server 2000を使っていたので、DBCC SHOWCONTIG コマンドで断片化を確認し、DBCC DBREINDEXコマンドで再構築を行ったり、 DBCC INDEXDEFRAGコマンドで再構成を行ったりということを、不定期に行っていました。

今は、SQL Server のバージョンも上がり、動的管理ビューを使用することで、インデックスの断片化も確認できます。以下がそのSQLの例になります。

SELECT DB_NAME(s.database_id), OBJECT_NAME(s.object_id), s.index_type_desc, i.name, s.page_count,s.fragment_count, s.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('dbsdb'), NULL, NULL, NULL, 'LIMITED') s
INNER JOIN sys.indexes i ON i.object_id = s.object_id
  and i.index_id = s.index_id
WHERE s.avg_fragmentation_in_percent > 10
ORDER BY avg_fragmentation_in_percent DESC

ここでの、主な項目の意味としては、
page_count                                                 ページ数
fragment_count                                       断片化しているページ数
avg_fragmentation_in_percent      断片化の割合(%)

上記の結果では、断片化の割合が10%以上のものを抽出していますが、結構高い割合のものがあることが確認できます。

また、抽出SQLのdm_db_index_physical_stats 関数の5番目の引数であるスキャンモードには、LIMITED, SAMPLED, DETAILEDの3種類があります。（上記の例は、LIMITED モード）
それぞれのスキャンモードの意味は以下になります。

LIMITED           
        最も高速なモードで、スキャンするページ数は最小

SAMPLED         
        SAMPLED モードでは、インデックスまたはヒープの全ページの 1% のサンプルに基づく統計情報が返されます。インデックスまたはヒープのページが 10,000 ページに満たない場合は、SAMPLED モードの代わりに DETAILED モードが使用されます。
 
DETAILED       
        DETAILED モードではすべてのページがスキャンされ、すべての統計が返されます。

それで、断片化を解消するには、以下の3つの方法があります。1)     インデックスのオフライン再構築
2)     インデックスのオンライン再構築（Enterprise エディション のみで利用可能）
3)     インデックスの再構成

1)     インデックスのオフラインでの再構築のコマンドは以下になります。

ALTER INDEX インデックス名 ON テーブル REBUILD

インデックスの再構築（オフライン）は、新しい領域へインデックスを再作成し、古いインデックスを削除することで、断片化を解消します。したがって、インデックスの再構築中は、インデックス全体がロックされるので、再構築が完了するまでは、ユーザー操作は待ち状態になり、注意が必要です。

例)
ALTER INDEX PK_T_USER_LOG ON dbo.T_USER_LOG REBUILD

再構築の後で、再度このテーブルについての断片化を見てみます。

SELECT DB_NAME(s.database_id), OBJECT_NAME(s.object_id), s.index_type_desc, i.name, s.page_count, s.fragment_count, s.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('dbsdb'), OBJECT_ID(N'T_USER_LOG'), NULL, NULL, 'LIMITED') s
INNER JOIN sys.indexes i ON i.object_id = s.object_id
  and i.index_id = s.index_id;

上記の結果から、断片化の割合は 0% になり、断片化が解消されたことがわかります。

2)     インデックスのオンライン再構築のコマンドは以下になります。

ALTER INDEX インデックス名 ON テーブル REBUILD WITH ONLINE = ON

3)     インデックスの再構成のコマンドは以下になります。

ALTER INDEX インデックス名 ON テーブル REORGANIZE

例)
先ほどの、断片化の一覧で、断片化が35%程度であった、「T_USER_LOG_V6」について、再構成を行ってみます。最初に、現状の断片化を再度確認してみます。

SELECT DB_NAME(s.database_id), OBJECT_NAME(s.object_id), s.index_type_desc, i.name, s.index_id, s.alloc_unit_type_desc, s.page_count, s.fragment_count, s.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('dbsdb'), OBJECT_ID(N'T_USER_LOG_V6'), NULL, NULL, 'LIMITED') s
INNER JOIN sys.indexes i ON i.object_id = s.object_id
and i.index_id = s.index_id;

再構成を実施してみます。

ALTER INDEX PK_T_USER_LOG_V6 ON dbo.T_USER_LOG_V6 REORGANIZE;

件数が多少多かったこともあり、少し時間がかかった感じがありました。

断片化を再度確認してみます。先ほどと同じSQLを実行してみます。

先ほど35%程度あったところは、2.4% ほどに変わりました。断片化がかなり解消されたことがわかります。

★インデックスの再構築と再構成の違いについて簡単に記します。
〇再構築
・ひとつのトランザクションでインデックスを完全に再作成。
　そのため、キャンセルすればそのトランザクションはロールバ
　ックされ、断片化した状態に戻る。
・インデックスを作り直すので、テーブルのレコード数が増えな
　い限り、大きく処理時間が変わることはない。
・一時的には、断片化した古いインデックスと再作成した新しい
　インデックスがふたつ存在する状況となり、それらを格納する
　ための容量、及びソート用の領域が必要になる。

〇再構成
・処理単位ごと (処理単位はページ) にトランザクションが分割さ
　れていて、リーフページ間でインデックス行を移動させること
　で行を前に詰めて断片化を解消している。
　キャンセルしても、既に完了しているトランザクションの結果
　は維持される。
・前のページに空きがある場合に後ろのページから前のページへ
　行を移動するという処理であるため、断片化の度合が大きいほ
　ど処理時間が長くなる。
・追加の領域は基本的には必要としない。

実際の運用では、上記のような違いを考慮してどちらを選択するかを決定する必要があると思います。

今日は以上まで

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 (
[金額], [粗利率], [粗利額]
);

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

今日は以上まで