SQLServerで断片化の状態に応じてIndexを再構築する

SQLServerにおいて、インデックスが断片化してしまうとクエリのパフォーマンスが低下してくる。

インデックスの再構成(Reorganize)や再構築(Rebuild)を行うと断片化が解消される為、定期的にインデックスの再構成(Reorganize)や再構築(Rebuild)をすることでこれを解決することができる。

ただし、やみくもにデータベース内の全てのインデックスを再構成(Reorganize)や再構築(Rebuild)するとデータベースの規模が大きいほど、実行に多くの時間がかかってしまったり、ログファイルが大きくなりすぎてしまう。

そこで、断片化の状態を判断して再構成(Reorganize)または再構築(Rebuild)をする。

まず、インデックスの断片化の状態は sys.dm_db_index_physical_stats を使って調べることができる。

sys.dm_db_index_physical_stats が返す avg_fragmentation_in_percent は論理的な断片化(インデックス内で順序が乱れたページ)の割合を示していて、再構成(Reorganize)または再構築(Rebuild)をするべきかどうかの判断の指標とすることができる。

例えば、以下のようなクエリで再構成(Reorganize)のクエリ、再構築(Rebuild)のクエリ、スキーマ名、テーブル名、インデックス名、インデックスID、オブジェクト内のパーティション番号、断片化の割合、インデックスページまたはデータページの合計数を取得することができる。

avg_fragmentation_in_percent > 10 を指定することで、断片化率が 10 % 以上のインデックスを取得している。

インデックスの断片化の状態がわかったら、それによってインデックスを再構成(Reorganize)するか、または再構築(Rebuild)するか決定する。

例えば、10% 以上 30% 未満であれば再構成(Reorganize)、 30% 以上であれば再構築(Rebuild)するとすれば、上記のクエリで再構成のクエリ、または再構築のクエリを生成してそれぞれ実行する。

SQLServerで1つのクエリにより複数行をinsertする

1つのクエリで、複数行をinsertする

SQLServer2008以降では、INSERT文で追加行をカンマでつなげることで実現できる。

なお、一度に追加できる最大行数は、1000行となる。

 

ちなみに、SQLServer2005以前では複数行のINSERTは動作しないので、以下のような方法をとることになる。

 

 

SQLServerで日付範囲から日(または月)ごとのデータを作成する

日付範囲から日ごと、または月ごとに年月日(月ごとの場合、日は1日)を作成する

”2017年1月1日から2017年1月31日まで”のようなFrom/Toの日付範囲があり、そこからカレンダーのように日ごとのデータを取得したい場合、以下のような共通テーブル式(CTE:Common Table Expression)を利用した再帰クエリで作成することができる。

 

また、月ごと(日は1日)にデータを取得したい場合は以下のクエリで作成できる。

 

 

SQLServerでdatetimeデータ型から日付部分のみを取得する

datetimeデータ型から日付部分のみを取得する

上記のクエリを実行すると、”2017-01-10 17:13:05″のように日付と時刻が取得される。

SQLServer2008以降であれば、以下のクエリで日付だけを取得することができる。

このクエリを実行すると、”2017-01-10″を取得することができる。

 

SQLServerで日付の曜日を取得する

SQLServerでは、曜日を「月曜日」、「火曜日」など文字列で取得する方法と、曜日を表す数字で取得する方法の2つがある。

 

曜日を文字列で取得する

曜日を「月曜日」、「火曜日」などの文字列で取得するには、DATENAME関数を使用する。

※WEEKDAYは、DWでも可(取得対象とするdateの要素を指定)

 

曜日を表す数字を取得する

曜日を曜日を表す数字で取得する場合は、DATEPART関数を使用する。

※WEEKDAYは、DWでも可(取得対象とするdateの要素を指定)

曜日と数字の対応表

曜日返される数字
日曜日1
月曜日2
火曜日3
水曜日4
木曜日5
金曜日6
土曜日7

曜日を表す数字は、CASE式を使って以下のように文字列に変換することができる。

※WEEKDAYは、DWでも可(取得対象とするdateの要素を指定)