開発のあいまに

データの上昇角度を求める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 上昇角度;

Excel、C#を使い、指数関数的減衰を、最大値が3程度になるように調整した計算式

Excel、C#を使い、特定の値が小さくなるほど、指数関数的に結果が大きくなる計算式　を元に、最大値が3程度になるように計算式を調整してみた。

一番左の「1」と、2つの「7」の値を変更する事で、指数関数的減衰は維持したまま、最大値を変えることができる。

変更した結果、どういう値になるかは、Excelで実際に計算してみると、分かりやすい。

 

【計算式】

y = 1 + (7 * LOG(x + 1) - 7 * LOG(x)) 

 

【Excel関数だと】

=1+(7*LOG(セル+1)-7*LOG(セル))

 

【C#だと】

public static double Get指数関数的減衰_最大値3程度(double x)
{
   return 1 + (7 * System.Math.Log10(x + 1) - 7 * System.Math.Log10(x));
} 

 

以下のようなグラフの値を求めたい時に使える。

Excel、C#を使い、特定の値が小さくなるほど、指数関数的に結果が大きくなる計算式

数学では「指数関数的減衰」というらしい。

 

【計算式】

y = (LOG(x + 1) - LOG(x))

 

【Excel関数だと】

=(LOG(セル+1)-LOG(セル))

 

【C#だと】

public static double Get指数関数的減衰_基本式(double x)
{
   return (System.Math.Log10(x + 1) - System.Math.Log10(x));
}

 

以下のようなグラフの値を求めたい時に使える。

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で、ストアド、ファンクションをリコンパイルしても、IISからのDB処理だけが遅い場合は、IISを再起動する。

SQLServerで、IISからのDB処理だけが遅い場合は、ストアド、ファンクションをリコンパイルするを繰り返しても、「IISからのDB処理だけが遅い」現象が、解消されないことがありました。

その時は、IISを再起動する事で、解消しました。

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

 

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

SQLServerで、インデックスと統計情報を追加しても、ストアドの処理が遅い場合

ストアドをチューニングアドバイザーに掛け、抽出されたインデックスと統計情報を追加すれば、ストアドのパフォーマンスを大幅に改善する事ができます。
それは、テラバイトクラスのDBシステムでも同様です。

しかし、テラバイトクラスのDBだと、統計情報の更新、インデックス再構築に1日以上かかり、夜間ジョブとして実行することができない場合があります。

その場合、実際のデータと、そのデータを持つテーブルの統計情報とで、乖離が大きくなり、ストアドが使用する実行プランが、最適ではなくなり、極端にパフォーマンスが低下します。

そうなると、いくらストアドをリコンパイルしても、パフォーマンスは改善しません。

このケースでは、テーブル自体の統計情報を更新した後、ストアドをリコンパイルする事で、パフォーマンスが改善されます。

具体的には、こちらのSQLを実行します。
UPDATE STATISTICS （テーブル名）;
EXEC sp_recompile N'（テーブル名）';

 

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

脳の情報量とコンピュータの情報量について計算してみた

人の脳細胞は800億個あるらしいので、それがコンピュータでいうと何バイトになるか考えてみると、脳の神経細胞には、基本的に状態は二つしかない、「興奮している／していない」の2通り、なので、一つの細胞が保持できる情報量は1ビット。
そうすると以下の式が成り立つ。

800億個の脳細胞 × 1ビット ＝ 800億ビット ＝ 9.3GB

ずいぶん昔の、ノートパソコンなみの情報量しか、脳は扱えない訳ですね。改めて考えてみると、脳が扱える情報量はそんなもの・・・。
今後はますます、脳で扱う情報と、コンピュータに任せる情報とを、旨く切り分けて行く能力が、必要になって来るのでしょう。