開発のあいまに

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

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

SQLServerで、IISからのDB処理だけが遅い場合は、ストアド、ファンクションをリコンパイルする

テラバイトクラスのDBシステムを扱っていると、データ量が増えるたびに、特定のSQLが極端に遅くなることがあります。

その時に、遅くなったSQLの実行プランを確認すると、インデックススキャン（Index seek）のコストが跳ね上がり、SQL全体のコストの100%近くを、インデックススキャンが占めるように変化しています。

インデックススキャンのコストは、SQL全体で20%未満になっているのが、妥当な値です。

極端に遅くなっている、もしくは、コストが高くなっているSQLは、SQLServerのチューニングアドバイザーにかけると、インデックスと統計情報が抽出されるので、それを適用する事で解決できます。
※SQLが遅くなっているサーバーで、チューニングアドバイザーを実行せず、別のサーバーでチューングアドバイザーにそのSQLをかけると、必要なインデックスと統計情報が抽出されない事があるので、そこは注意が必要です。

テラバイトクラスのDBシステムを1年以上運用していて、今回初めて、必要なインデックスと統計情報を追加しても、IISからのDB処理だけ処理性能が改善しない、という現象に遭遇しました。
Management Studioからだと、遅くなっているSQLの実行プランは改善され、実行時間も1秒程度なのに対し、WEBサイトからそのSQLを実行すると、5分たっても結果が返って来ませんでした。

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

色々試した結果、WEBサイトから実行した場合のみ遅い現象が発生したら、ストアドのリコンパイルが有効だという事が分かりました。ユーザー定義関数を利用している場合は、関数のリコンパイルも必要です。

ストアドに実行プランをキャッシュする、こちらのアーキテクチャにバグがあって、IIS側からアクセスされた場合に、古い実行プランを使い続けていたのが原因でした。
http://msdn.microsoft.com/ja-jp/library/ms181055(v=sql.105).aspx
http://msdn.microsoft.com/ja-jp/library/ms191007(v=sql.105).aspx

そういえば、SQLServerはIISのキャッシュを、別扱いするという技術資料を、以前どこかで読んだ気がします。

毎日のように、インデックスと統計情報を追加しているシステムなので、再発防止として、インデックスと統計情報を追加する頻度の高いテーブル、ストアド、ユーザー定義関数は全て、日次で夜間に、sp_recompile を実行するようにしました。
http://msdn.microsoft.com/ja-jp/library/ms181647.aspx

 

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

 

SQLServerの最新パッチ

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

SQL Release Services Blog

6ヶ月以上使われていないインデックスを抽出するSQL

SQLServerのユーザーDBで、6ヶ月以上使われていないインデックスを抽出し削除する事で、不要なインデックスを増やさない。定期メンテナンス用のSQL。

 

use (ユーザーDB名)

Declare @create_limit datetime
Select @create_limit = DATEADD(MONTH, -6, GETDATE()) -- 6ヶ月以上前に作成されたインデックスが対象

SELECT OBJECT_SCHEMA_NAME(i.[object_id]) as [Shema Name], OBJECT_NAME(i.[object_id]) AS [Table Name] ,i.name as [Index Name], o.create_date, o.modify_date
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id]
WHERE i.index_id NOT IN (
SELECT s.index_id
FROM sys.dm_db_index_usage_stats AS s
WHERE s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
)
AND o.[type] = 'U'
AND i.is_primary_key != 1 -- Primary Key を除外
AND i.is_unique_constraint != 1 -- Unique Key を除外
AND i.name is not null
AND o.create_date < @create_limit
AND OBJECT_SCHEMA_NAME(i.[object_id]) not in('capsim') -- 対象外のスキーマを除外
ORDER BY OBJECT_SCHEMA_NAME(i.[object_id]), OBJECT_NAME(i.[object_id]) ASC, create_date ;

 

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

SQLServerで、ユーザーＤＢに紐付く、データファイル、ログファイルの一覧を抽出する

use master

declare @DBName                 sysname
set @DBName = '<ユーザーＤＢ名>'

-- Data file
select  name, physical_name
from    sys.master_files
where   database_id = db_id(@DBName)
        and type_desc = 'ROWS'

-- Log file
select  name, physical_name
from    sys.master_files
where   database_id = db_id(@DBName)
        and type_desc = 'LOG'