開発のあいまに

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はMySQL

世界で最も使われているDBはMySQL。Oracleの3倍。
http://d.hatena.ne.jp/cypher256/20130209/p1

このページはJava屋さんが作ったので、SQLServerは除外されているけど、SQLServerのシェアはOracleより少し低い程度。
http://www.itmedia.co.jp/enterprise/articles/1005/12/news011.html

社内システムのDBシェアはOracleが半分を占めトップ。WEBサービスなどの社外向けシステムはMySQLが大半を占め、世界で最も使われているDBがMySQLなっているのが分かる。

業務システムで使う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）に設定すれば、統計情報が固定されます。

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

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

Windows7 にDBを構築する場合、シャドウコピーを無効にしないと、処理性能が極端に低下する

Windows7 に SQLServer 2008/2012 をインストールして、DBサーバーとして利用していますが、DBサイズが50GBを超えた辺りから、定期的にDBの処理性能が10分の1に低下する現象が、発生するようになりました。

DBサイズが増加するにつれ、その頻度が多くなり、継続時間も長くなってきたので、本格的に調査した結果、Windows7のシャドウコピーが原因だと分りました。

シャドウコピーを無効にする事で、処理性能が極端に低下する事は無くなり、Windows7 でも 社内DBサーバーとして十分活用できています。

シャドウコピーを無効化する方法は、こちらを参考にして下さい。
http://www.se-support.com/clientpc/volumeshadow.html

シャドウコピーが無効になっている状態はこちら。

T-SQLを使い、60分で、値が10上昇した場合、その角度を求める計算式

 

Declare @底辺x_分 float = 60;

Declare @高さy_上昇値 float = 10;

select ATAN(@高さy_上昇値 / @底辺x_分) * 180 / PI() as 上昇角度

 

直角三角形の、θを求めるのと、同じ式です。　下の図だとaに該当します。

 

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

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

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

 

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

USE データベース名;

GO

-- Shrink the truncated log file to 1 MB.

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

GO

SQL Server 2008 R2 SP2 Cumulative update #7 は危険、CU#8がお勧め

SQL Server 2008 R2 SP2 Cumulative update #7 を開発環境にインストールし、特に問題は見つからなかったので、本番環境にインストールしたら、高負荷なジョブが実行された際、ACCESS_METHODS_DATASET_PARENTというエラーが、SQLServerのエラーログに大量に出力され、SQLServer全体がwait状態になり、軽いSQLも全然結果が返って来ない現象が発生しました。

【エラーログ抜粋】
2013-10-11 00:53:26.24 spid75      Timeout occurred while waiting for latch: class 'ACCESS_METHODS_DATASET_PARENT', id 0000001A0F5483E8, type 4, Task 0x0000000000834BC8 : 9, waittime 300, flags 0x1a, owning task 0x0000000013D07048. Continuing to wait.

 

このエラーは、SQL Server 2008 R2 SP2 Cumulative update #8 をインストールする事で解消されます。CU8 はCU5以前に比べて、SQLServer全体の処理コストが10%程度下がるので、お勧めです。

SQL Server 2008 R2 SP2 Cumulative update #8 のダウンロード先はこちら

http://blogs.msdn.com/b/sqlreleaseservices/

http://blogs.msdn.com/b/sqlreleaseservices/archive/2013/08/24/sql-server-2008-r2-sp2-cumulative-update-8.aspx

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