開発のあいまに

SQLServerで、バックアップ先のアーカイブテーブルにあって、本テーブルに無いデータを抽出するSQL

複数列をキーにしている場合、IN()は使えないのでexistsを使う。
where not exists ()の中で「SELECT *」を指定していますがあまり意味はなく、アーカイブテーブルと本テーブルのテーブル定義が違っていてもexists()は使えます。 今回サンプルとしたtSecテーブルは、[通貨ペアNo]と[日時]の2列が主キーでした。

SELECT *
FROM FX_ACV.hstr.tSec as a
where not exists 
(
	SELECT *
	FROM FX.hstr.tSec as b
	where (a.[通貨ペアNo] = b.[通貨ペアNo] and a.[日時] = b.[日時])
)

アーカイブDBに有って本DBには無いデータを全て、本DBへInsertするSQLはこんな感じ。

INSERT INTO FX.hstr.tSec
           (通貨ペアNo
           ,日時
           ,買いRate
           ,売りRate)
SELECT 通貨ペアNo
      ,日時
      ,Rate_買い
      ,Rate_売り
FROM FX_ACV.hstr.tSec as a
where not exists 
(
	SELECT *
	FROM FX.hstr.tSec as b
	where (a.通貨ペアNo = b.通貨ペアNo and a.日時 = b.StartDate)
)

データの上昇角度を求めるSQL

ビッグデータシステムなど、大量のデータを解析する場合、JAVAやC#では計算元のデータを読込むだけで膨大な時間がかかってしまうので、最近では数十テラのデータであってもDBにインポートしてから、SQLで分析するのが主流になっています。
以下は、新しいデータが1つ前のデータに対して、どれだけ値が上昇したのか、その角度を求めるSQLServerのT-SQLです。データが増える度にリアルタイムでこのSQLを実行すると、角度から、データの急激な動きを視覚的に把握できるようになります。

DECLARE @StartDate DATETIME = '2015-08-29 06:45:00.000';

DECLARE @現在の値 FLOAT = 0;
DECLARE @1つ前の値 FLOAT = 0;

DECLARE cursor_DataHistory CURSOR FOR
SELECT TOP 2 [値]
FROM [DataHistory]
WHERE [StartDate] <= @StartDate
ORDER BY [StartDate] DESC;
	
open cursor_DataHistory;

FETCH NEXT FROM cursor_DataHistory INTO @現在の値;
FETCH NEXT FROM cursor_DataHistory INTO @1つ前の値;

CLOSE cursor_DataHistory;
DEALLOCATE cursor_DataHistory;

SELECT (ATAN((@現在の値 - @1つ前の値) / 1) * 180 / PI()) AS 上昇角度;

業務システムで使うDBの選択基準の定石

365日24時間止められないシステムならORACLEを選択する。

平日9:00～21:00頃まで使われ、それ以外の時間帯をシステムメンテナンスに使えるなら、ライセンス料がOracleの半分で済み、開発難易度も低いSQLServerを選択する。

CPU使用率が100%になるようなシステム異常が発生した場合、SQLServerだと、SQLクエリアナライザなど、DB分析ツールの負荷が高い為、DBへ接続している全ての処理を一旦停止し、DB負荷を下げてからじゃないと、DBサーバーの改善に着手できない。

しかし、ORACLEではCPU使用率が100%になっていても、ADDMなどのDB分析ツールはサクサク動くので、リアルタイムにシステム異常を解消していける。この耐障害性では、まだまだ、Oracleに一日の長がある。

ただ、SQLServerでも、夜間や休日にメンテナンス処理をきちんと実施していれば、業務時間中にCPU使用率が100%になるようなシステム異常が発生する事はまず無い。

SQLServer、Oracle、どちらのEnterprise版にもテーブルのpage圧縮機能があり、100TBクラスのDBなら、データベースサイズが、どのDBにするかの判断基準にはならない。

SQLServer で統計情報を固定する

DB処理で最適なインデックスが使われないという、Oracle DBでよく問題になるDBチューニングテーマ。

統計情報を固定することで解決する手法は、SQLServer ではできないと思い込んでいましたが、SQLServer にもありました、統計情報を固定する機能。

「データベースのプロパティ ⇒ オプション」画面にある、「統計情報の自動更新」をFalse（デフォルト=True）に設定すれば、統計情報が固定されます。

不足する統計情報は、必要に応じて、メンテナンスプランの統計情報更新を行う事で補えます。インデックス再構築前には実施した方が良さそうです。

以下、「データベースのプロパティ ⇒ オプション」画面。

SQLServer のトランザクションログは肥大化する

SQLServerのトランザクションログは、使われていない領域をうまく再利用してくれないのか、SQLServer 2008、SQLServer 2012 とバージョンを問わず、徐々に、どこまでも拡張していきます。

レポート⇒標準レポート⇒ディスク使用料を普段から見るようにして、トランザクションログの使用済み領域がどの程度なのか把握し、定期的にそのサイズまでトランザクションログを縮小するのは、必須なようです。

 

【トランザクションログを20GBまで縮小するSQLサンプル】

USE データベース名;

GO

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (トランザクションログ論理名, 20000);

GO

SQLServerは、断片化率が高くなると、SQLが実行される際、パラレル処理されなくなり、極端に処理性能が低下します

ストレージを増設する前、12TBのストレージで、10TBのDBを運用していたので、1ヵ月に1回程度、SQLServerのDB Shrinkを実行していました。
その影響で、メインのテーブルとインデックスを中心に、断片化率が99%に達していました。

 

各テーブルとインデックスの断片化率は、こちらのSQLでチェックできます。

use （データベース名）
SELECT DB_NAME(s.database_id), OBJECT_NAME(s.object_id), s.index_type_desc, i.name, s.fragment_count, s.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('（データベース名）'), 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

 

ビッグデータな上に、頻繁に機能に変更が加えられるシステムなので、処理コストの高いSQLが無いかは、定期的にチェックしていたのですが、断片化率はチェックしていませんでした。

断片化を解消すると、チューニングアドバイザによるインデックスと統計情報の抽出率が向上し、今まで抽出さていなかった、インデックスと統計情報が抽出されるようになりました。

また、週次処理が実行された後、特定の機能が極端に遅くなる現象が毎週発生し、その度に、チューニングアドバイザを実行し直したり、ヒント文で使用されるインデックスを固定したりしていましたが、断片化を解消してからは、週次処理後に特定のSQLが極端に遅くなるといった現象は、発生しなくなりました。

断片化を解消するには、テーブルとインデックスのリビルドが必要です。

 

・テーブルをリビルドするサンプルSQL
ALTER TABLE （スキーマ名）.（テーブル名） REBUILD PARTITION = ALL

・テーブルをPage圧縮でリビルドするサンプルSQL
ALTER TABLE （スキーマ名）.（テーブル名） REBUILD PARTITION = ALL WITH ( DATA_COMPRESSION = PAGE )

・インデックスをリビルドするサンプルSQL
ALTER INDEX all ON （スキーマ名）.（テーブル名） REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF)

・インデックスをPage圧縮でリビルドするサンプルSQL
ALTER INDEX all ON （スキーマ名）.（テーブル名） REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION  = PAGE )

 

Page圧縮は、Developer Edition と Enterprise Edition の限定機能ですが、I/O性能の低下はみられないのに、データ量が10分1になるので、ビッグデータを扱うDBでは必須です。

リビルドを実行しても、3割程度は断片化が解消されませんでした。
一旦、断片化してしまうと、断片化を完全に解消することは出来ない為、断片化させない工夫が必要なようです。

断片化が進んでいても、毎週1回、以下のSQLで断片化率3%以上のインデックスを抽出し、12時間ほどかけてリビルドするようになってからは、データベースのパフォーマンスは安定するようになりました。

use （対象のデータベース名）
select DB_NAME, ShemaName, TableName, name
from (
  SELECT DB_NAME(s.database_id) as DB_NAME,
  OBJECT_SCHEMA_NAME(i.[object_id]) as [ShemaName],
  t.name as TableName,
  OBJECT_NAME(s.object_id) as [IndexName], 
  i.name as name,
  s.avg_fragmentation_in_percent as avg_fragmentation_in_percent
  FROM sys.dm_db_index_physical_stats(DB_ID('（対象のデータベース名）'),
 NULL, NULL, NULL, 'LIMITED') s
  INNER JOIN sys.indexes i ON i.object_id = s.object_id
    and i.index_id = s.index_id
  INNER JOIN sys.tables t ON i.object_id = t.object_id
  WHERE s.avg_fragmentation_in_percent > 3
) as t
where name is not null
group by DB_NAME, ShemaName, TableName, name
order by DB_NAME, ShemaName, TableName, name

 

【参考URL】
http://msdn.microsoft.com/ja-jp/library/ms190273(v=sql.105).aspx
http://msdn.microsoft.com/ja-jp/library/ms188388.aspx
http://msdn.microsoft.com/ja-jp/library/ms178065(v=sql.105).aspx
http://msdn.microsoft.com/ja-jp/library/ms175097(v=sql.105).aspx
http://msdn.microsoft.com/ja-jp/library/ms191528(v=sql.105).aspx

SQLServerで、SQLがパラレル処理されない場合は、実行プランを丸ごと固定する

SQLServerは、SQL処理時間がcost threshold for parallelismの値（デフォルト5秒）を超えるような場合、1つのSelect文であっても、シリアルではなくパラレルで処理します。
パラレル処理されれば1分程度で終わるSQLも、シリアルで実行されると数時間かかるという事も珍しくありません。

今回、サーバーの状態によって実行プランが変化し、パラレルで実行されてほしいSQLが、シリアルで処理される現象が発生しました。
開発環境のサーバーだと、必ずパラレル処理されるのに、本番系のサーバーではシリアルで処理され、極端に処理時間がかかるようになりました。
当初は、本番系のサーバーでもパラレル処理されていました。

チューニングアドバイザーを実行し直し、インデックスと統計情報を追加しても、ヒント文でインデックスを固定しても、cost threshold for parallelismの値を1に変更しても、パラレル処理されるようにはなりませんでした。
最終的には、問題のSQL限定で、開発環境のサーバーで使われている実行プランを丸ごと使い、実行プランを完全に固定してしまう事で、パラレル処理されるようになりました。

 

実行プランを完全固定したサンプルSQLはこちら。

Sample.sql

 

SQLがシリアル実行されていた時の実行プラン。

 

SQLがパラレル実行されていた時の実行プラン。

SQLServerで、IISからのDB処理だけが遅い現象が、どうしても解決しない場合は、ヒント文を使用する

SQLServerで時々発生する、IISや関連サービスからのDB処理だけが、極端に遅くなる現象に対応する為、４つのサイクルを回して来たのですが、運用時間帯に、一部の処理で、IISや関連サービスからのDB処理だけ、極端に遅くなる現象が再発してしまいました。

【４つのサイクル】
1、チューニングアドバイザーによる、インデックスと統計情報の抽出と追加。
2、テーブルの統計情報を、週次で更新。
3、ストアドとファンクションを、日次のジョブで毎朝リコンパイル。
4、IISと関連サービスを、日次で毎朝再起動。

【環境】
WEBサーバー：Windows Server 2008 R2 SP1
DBサーバー：Windows Server 2008 R2 SP1、SQLServer 2008 R2 SP2 CU5
WEBサイトは.Net4.0で構築

IISや関連サービスからだと数時間かかっている処理でも、SQLServer Management Studio から実行すると数秒で終わり、関連するストアドとファンクションをリコンパイルすると、IISや関連サービスからの処理も数秒で終わるようになるので、ストアドとファンクションがキャッシュしている実行プランに、問題があるのは確実なのですが、４つのサイクルでは解消しませんでした。

最終手段として、ストアドとファンクション内のSelect文が使用するインデックスを、ヒント文で固定する事で、解消することが出来ました。

具体的には、Management Studioから実行すると速い環境で使用されているインデックスを調査し、ヒント文でそのインデックスを固定する事で、どの環境でも必ず同じインデックスが使われるようにします。

【具体的な手順】
1、SQLServer Management Studio でSQLを右クリックし「推定実行プランの表示」を選択する。
2、表示された実行プランを右クリックし「実行プランのXMLの表示」を選択する。
3、表示されたXMLの中から「Index="」が有る行を検索する。
　XMLからだと、どのSelect 文のどのテーブルが、どのインデックスを使っているか、簡単に
　見つける事ができます。
　プライマリキーは無視して構いません。
4、見つかったインデックスは、ヒント文（ WITH (index( ）として、FROM句のテーブルの後に
　宣言し、そのSQLで使用されるインデックスを固定します。

ストアドやファンクションそれぞれの中で、各テーブルの別名を、ユニークになる名称にしていると、より簡単に対象のインデックスを見つける事ができます。

【ヒント文の例】
・1つのテーブルに1つのインデックスを指定する場合。
FROM （テーブル名） as （テーブル別名）
WITH (index(（インデックス名１）))

・1つのテーブルに複数のインデックスを指定する場合。
FROM （テーブル名） as （テーブル別名）
WITH (index(（インデックス名１）,（インデックス名2）))

※ヒント文に指定するインデックスは、NONCLUSTURED INDEX のみでよく、プライマリキーを含む CLUSTURED INDEX  を指定する必要はありません。

SQLServerのインデックスと統計情報については、５つのサイクルを回す事で、パフォーマンスの問題を解決できそうです。

【５つのサイクル】

1、チューニングアドバイザーによる、インデックスと統計情報の抽出と追加。
2、テーブルの統計情報を、週次で更新。
3、ストアドとファンクションを、日次のジョブで毎朝リコンパイル。
4、IISと関連サービスを、日次で毎朝再起動。
5、IISと関連サービスからのDB処理が、極端に遅くなるストアド、ファンクションは、使用するインデックスをヒント文で固定する。

 

【関連記事】
チューニングアドバイザーによる、インデックスと統計情報の抽出と追加
テーブルの統計情報を、週次で更新
ストアドとファンクションを、日次のジョブで毎朝リコンパイル
IISと関連サービスを、日次で毎朝再起動。
IISと関連サービスからのDB処理が、極端に遅くなるストアド、ファンクションは、使用するインデックスをヒント文で固定する
SQLServerで、IISからのDB処理だけが遅い場合は、ストアドをリコンパイルする
SQLServer 2005 インデックス チューニング ライフサイクル
6ヶ月以上使われていないインデックスを抽出するSQL

SQLServer トレースフラグの有効性

DBサイズがテラバイトを超えて来ると、DB全体の統計情報を更新するだけで、1日以上かかってしまい、定期的な統計情報の更新を諦めていましたが、統計情報が更新されない事で、特定のSQLが極端に遅くなる現象が発生しました。

SQLを分解し、極端に処理が遅くなる原因となっていたテーブルを特定し、UPDATE STATISTICS を個別に実行する事で、実行プランが改善し Index Seek のコストが下がり、問題を解消することができました。

再発防止として、定期的に統計情報を更新したかったのですが、DB全体の統計情報を更新する事は不可能なので、トレースフラグが使えないか、検証環境で試してみましたが、、、
http://azwoo.hatenablog.com/entry/2013/02/14/125848
http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx

統計情報の更新が頻発し、運用時間帯の応答速度が著しく低下してしまったので、使い物になりませんでした。

その後、DBのデータ量の変化を調査し、95%程度のテーブルは、古いデータを定期的に削除するなどの対応で、データ量に大きな変化は発生せず、統計情報の更新は不要だという事が分り、残り5%程度のテーブルに絞れば、統計情報の更新時間を2時間程度に抑えることが出来たので、特定のテーブルを対象に、定期的に統計情報の更新を行うようにしました。

実データと統計情報の乖離が発生し易い、データ量の変化が多いテーブルのみに限定し、日次や週次といった、必要なタイミングで統計情報を更新する方が、トレースフラグよりも有効でした。

 

【関連記事】
チューニングアドバイザーによる、インデックスと統計情報の抽出と追加
テーブルの統計情報を、週次で更新
ストアドとファンクションを、日次のジョブで毎朝リコンパイル
IISと関連サービスを、日次で毎朝再起動。
IISと関連サービスからのDB処理が、極端に遅くなるストアド、ファンクションは、使用するインデックスをヒント文で固定する
SQLServerで、IISからのDB処理だけが遅い場合は、ストアドをリコンパイルする
SQLServer 2005 インデックス チューニング ライフサイクル
6ヶ月以上使われていないインデックスを抽出するSQL

SQLServerで、ジョブ名からジョブを削除するSQL

SQLServer Management Studio から、SQL Server Agent に作成したジョブを右クリックし、「DROP To」でDROP文を生成すると、JOB ID を元にしたDROP文が生成されますが、JOB ID はサーバーによって異なる為、使い勝手は悪いです。

 

デフォルトで生成される、@job_id を指定するSQLがこちら。

USE msdb ;
GO

EXEC sp_delete_job @job_id = N'（ジョブID）' ;
GO 

 

@job_nameを指定することで、ジョブ名からジョブを削除する、SQLを製造できます。

USE msdb ;
GO

EXEC sp_delete_job @job_name = N'（ジョブ名）' ;
GO