開発のあいまに

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

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

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

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

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

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

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

 

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

SQLServerの最新パッチ

SQLServer 2008 R2 SP2 の最新パッチがどれなのかは、このブログで確認できる。2013/2/24時点の最新パッチ（累計的な更新プログラム）はCU#5。

SQL Release Services Blog

SQLServer で TO_CHAR と同じことをするには

SQLServer で TO_CHAR と同じことをするには、CONVERTを使います。

int型の「販売金額」を、小数点付きの文字列で返す場合に、CONVERTを使うと以下のようになります。

　

SELECT CONVERT(VARCHAR, CONVERT(DECIMAL(10,1), 販売金額)) As 販売金額
FROM   H01販売履歴

IDENTITY 設定を一時的に外してINSERTする。

SET IDENTITY_INSERT [INSERT対象のテーブル名] ON;

　各INSERT文

SET IDENTITY_INSERT [INSERT対象のテーブル名] OFF;

SQLメモ：最大日付と最小日付の間にある、月数の差を求める。

MONTHS_BETWEEN()関数がSQLServerにはないので、自力で計算しないといけない。
SQLで作成した場合は以下のようになる。

SELECT ((YEAR(MAX(年月)) - YEAR(MIN(年月))) * 12) + (MONTH(MAX(年月)) - MONTH(MIN(年月)))
FROM   C11FM

SQLServer 2005 インデックス チューニング ライフサイクル

１、コーディングされているSelect文を全て、Management Studio のクエリエディタに貼り付ける。

２、ツールバーの「データベース エンジン チューニング アドバイザでのクエリの分析」アイコンをクリックして、「データベース エンジン チューニング アドバイザ」画面を開く。

 

３、「データベース エンジン チューニング アドバイザ」画面では、クエリエディタに記入したSQLのチューニング設定が初期値として入力済みになっている。

４、必要であれば「チューニング オプション」の内容を変更。

５、ツールバーにある「分析の開始」アイコンをクリックして開始。

６、分析対象のデータベースサイズが小さ過ぎたり、大き過ぎたりすると、「ワークロードを使用しています」タスクでエラーになる。その場合は、「チューニング オプション　⇒　詳細設定オプション　⇒　推奨インデックス用の最大領域を定義する」にチェックを付け、実際のＤＢサイズを入力し、再度「分析の開始」アイコンをクリック。

７、分析結果として登録した方が良いインデックスが、「推奨インデックス」にリストアップされるので、全てのインデックスと統計情報を、クエリエディタ画面で実行する。

８、インデックスと統計情報を追加したら、その対象となったテーブルに、sp_recompile を実行する。

InsertやUpdateが遅くならないようにインデックスを張らない方が良いものはないか気になりますが、実験した結果では、推奨される全てのインデックスを張った場合、InsertやUpdateは少し遅くなりますが、Select文は圧倒的に速くなり、トランザクション全体の処理時間が改善するので、何も考えずに全て適用してしまうのが一番です。

ただ、テラバイトクラスのDBの場合は、1つのインデックスを追加しただけで、100GB消費することは有りますので、ディスク容量は考慮した方が良いです。

また、不要なインデックスが増えないように、6ヶ月以上使われていないインデックスを抽出するSQLを定期的に実施た方が良いです。

それと、インデックスと統計情報を追加しても、ストアドが古い実行プランをキャッシュしたままになっている事がありますので、SQLServerで、IISからのDB処理だけが遅い場合は、ストアドをリコンパイルする こともにも、注意が必要です。

 

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

パラメータ有りSQLのテストソース

DECLARE @顧客ID varchar(100)
DECLARE @氏名 varchar(100)
DECLARE @氏名カナ varchar(100)
DECLARE @性別 int
DECLARE @年齢 int
DECLARE @住所 varchar(100)
DECLARE @郵便番号 varchar(100)
DECLARE @TEL varchar(100)
DECLARE @メールアドレス varchar(100)
DECLARE @購入済み商品No1 int
DECLARE @購入済み商品No2 int
DECLARE @購入済み商品No3 int
DECLARE @購入済み商品No4 int
DECLARE @購入済み商品No5 int

SET @顧客ID = null
SET @氏名 = null
SET @氏名カナ = null
SET @性別 = null
SET @年齢 = null
SET @住所 = null
SET @郵便番号 = null
SET @TEL = null
SET @メールアドレス = null
SET @購入済み商品No1 = 1
SET @購入済み商品No2 = 2
SET @購入済み商品No3 = 3

SELECT DISTINCT M03顧客.顧客No, M03顧客.顧客ID, M03顧客.氏名, M03顧客.氏名カナ,
   M04属性.値 AS 性別, M03顧客.年齢, M03顧客.郵便番号, M03顧客.住所,
   M03顧客.TEL, M03顧客.メールアドレス, M03顧客.備考
FROM   M03顧客 LEFT OUTER JOIN
   H01販売履歴 ON M03顧客.顧客No = H01販売履歴.顧客No LEFT OUTER JOIN
   M04属性 ON M03顧客.性別 = M04属性.属性No
WHERE  (@顧客ID IS NULL OR @顧客ID IS NOT NULL AND M03顧客.顧客ID LIKE '' + @顧客ID + '')
AND    (@氏名 IS NULL OR @氏名 IS NOT NULL AND M03顧客.氏名 LIKE '' + @氏名 + '')
AND    (@氏名カナ IS NULL OR @氏名カナ IS NOT NULL AND M03顧客.氏名カナ LIKE '' + @氏名カナ + '')
AND    (@性別 IS NULL OR @性別 IS NOT NULL AND M03顧客.性別 = @性別)
AND    (@年齢 IS NULL OR @年齢 IS NOT NULL AND M03顧客.年齢 = @年齢)
AND    (@住所 IS NULL OR @住所 IS NOT NULL AND M03顧客.住所 LIKE '' + @住所 + '')
AND    (@郵便番号 IS NULL OR @郵便番号 IS NOT NULL AND M03顧客.郵便番号 LIKE '' + @郵便番号 + '')
AND    (@TEL IS NULL OR @TEL IS NOT NULL AND M03顧客.TEL LIKE '' + @TEL + '')
AND    (@メールアドレス IS NULL OR @メールアドレス IS NOT NULL AND M03顧客.メールアドレス LIKE '' + @メールアドレス + '')
AND    ((@購入済み商品No1 IS NULL AND @購入済み商品No2 IS NULL AND @購入済み商品No3 IS NULL) OR
  ((@購入済み商品No1 IS NOT NULL AND @購入済み商品No2 IS NULL AND @購入済み商品No3 IS NULL) AND H01販売履歴.商品No = @購入済み商品No1) OR
  ((@購入済み商品No1 IS NOT NULL AND @購入済み商品No2 IS NOT NULL AND @購入済み商品No3 IS NULL) AND H01販売履歴.商品No = @購入済み商品No1 OR H01販売履歴.商品No = @購入済み商品No2) OR
  ((@購入済み商品No1 IS NOT NULL AND @購入済み商品No2 IS NOT NULL AND @購入済み商品No3 IS NOT NULL) AND H01販売履歴.商品No = @購入済み商品No1 OR H01販売履歴.商品No = @購入済み商品No2 OR H01販売履歴.商品No = @購入済み商品No3))

Visual Studio 2008 のセットアッププロジェクトで、Microsoft SQL Server 2005 Express Edition SP3 を必須コンポーネントにする方法

Visual Studio 2008 のセットアッププロジェクトで選択できる、SQL Server 2005 の必須コンポーネントは、SQLMicrosoft SQL Server 2005 Express Edition SP2 ですが、以下の変更を行う事で、裏でSP3をインストールする事ができます。

1. %Program Files%\Microsoft SDKs\Windows\6.0A\Bootstrapper\Packages\SqlExpress\ja\package.xml を編集します。

"<String Name="SqlExprExe">http://go.microsoft.com/fwlink/?LinkId=83136amp;clcid=0x411 </String>"
　この文字列を次のように変更します。
"<String Name="SqlExprExe">http://go.microsoft.com/fwlink/?linkid=65212 </String>"

２、http://go.microsoft.com/fwlink/?linkid=65212　からダウンロードしたファイルの名称を、sqlexpr32.exe に変更し、 %Program Files%\Microsoft SDKs\Windows\6.0A\Bootstrapper\Packages\SqlExpress\ja\sqlexpr32.exe と入れ替えます。

　

この後、プロジェクトをリビルドすれば、SQLMicrosoft SQL Server 2005 Express Edition SP3 がインストールされる、インストーラを作成できます。
※実際のインストール時は、SP2と表示されていますが、実際にインストールされているのはSP3です。

ストアドプロシージャから値を取得する。

-- ファイルNoをインクリメントするストアプロシージャ
ALTER PROCEDURE [dbo].[prFileNo_Hatuban]
    @User varchar(20)
AS
BEGIN
    DECLARE @FileNo bigint

    BEGIN TRANSACTION

    SELECT @FileNo = MAX(FileNo) FROM L_FileHistory WITH (TABLOCKX)
    INSERT INTO L_FileHistory WITH (TABLOCKX) (FileNo, RegistUser, UpdateUser) VALUES (@FileNo + 1, @User, @User )

    COMMIT TRANSACTION

    SELECT @FileNo as FileNo
END

　

　

// C#コードでストアドを呼び出す
using System.Data;
using System.Data.OleDb;
　

OleDbConnection objConnection2 = new OleDbConnection(GetDBConnString());
OleDbCommand objCmd2 = new OleDbCommand("prFileNo_Hatuban", objConnection2);
objCmd2.Connection.Open();
objCmd2.CommandType = CommandType.StoredProcedure;
objCmd2.Parameters.AddWithValue("@User", SqlDbType.VarChar);
objCmd2.Parameters["@User"].Value = this.Application.User.LoginName;

//com.ExecuteNonQuery();
OleDbDataReader myReader2 = objCmd2.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
long lngFileNo = 0;
while (myReader2.Read())
{
    lngFileNo = myReader2.GetInt64(0) + 1;
}
myReader2.Close();
objConnection2.Close();