SQL Server 2000から2012に移行したことによるストアドプロシージャの性能劣化

Question

以下の環境1で稼働していたDBを環境2に移行するよう、システムの改修を行っております。

環境1
Windows Server 2000
SQL Server 2000

環境2
Windows Server 2012
SQL Server 2012R2


2000のDBをエクスポートし、2008R2にインポートした上で機能レベルを2008に上げ、再度エクスポートし、2012にインポートを行い、テストを実施しています。

そこでパフォーマンスの問題が発生いたしました。

以下のようなテーブルに対し、単純な1行のInsertを行うストアドプロシージャがあるのですが、DBが2000から2012に変化したことで、処理速度が低下していると思われます。

CREATE TABLE dbo.TABLE_A(
a INT NOT NULL,
b SMALLINT,
c VARCHAR(32),
d FLOAT,
e DATETIME NOT NULL,
CONSTRAINT PK_TABLEA PRIMARY KEY NONCLUSTERED
(a ASC)
)


CREATE PROCEDURE [dbo].[STORED_A]
(
@a INT,
@b SMALLINT,
@c VARCHAR(32),
@d FLOAT
)
AS
DECLARE @time DATETIME
SELECT @time = GETDATE()
INSERT INTO dbo.TABLE_A (
a,
b,
c,
d,
e
) VALUES
(
@a,
@b,
@c,
@d,
@time
)

SQLプロファイラで動作を確認したところ、2000上ではストアドプロシージャ全体の処理速度がDuration=0ですが、
2012上ではDuration=5～8と5倍～8倍の時間がかかっていることが分かりました。

GETDATE()や、Insertの処理に時間がかかっているのかと思えば、プロファイラを見る限り、両方ともにDuration=0のため、そこが原因でもなく、Insertから、ストアドの完了までの間に時間がかかっているようです（何か裏で処理が走っている？）。

主キーへの書き込みに時間がかかっているのかとも思い、主キーをはがして処理を再実行して試してみたのですが、結果は変わらず、現在手詰まりの状態です。

Duration=5～8ということで、1処理にかかる時間自体は大したことがないのですが、C#のアプリ側から呼び出しているストアドで、1回の処理中に数十万回呼び出されるるため、問題になっています。
（そもそも、単なるInsertを数十万回も呼び出す仕組み自体に問題があるのではないか、というご意見はあるかとは思いますが、現在はあくまでアプリ側には手を入れないという前提で話が動いているため、その点についてはご容赦頂ければと思います。）

SQL Serverの設定変更や、ストアドの中身の変更によって処理性能を改善する情報はございませんでしょうか。
どうぞよろしくおねがいします。

Answer 1

なぜNONCLUSTEREDなのでしょうか？ CLUSTEREDとの違いを認識した上で選択されていますか？

Answer 2

早速のご返信ありがとうございます。

何故Nonclusteredが選択されているのか、という経緯については残念ながら資料が残っておりません。システム開発当時にはそれを選択する意図があったのかもしれませんが、おそらく意図はないと思われます。

Clusteredを選択した場合は、キー順にDB内でデータがソートされるという認識です。そのため、Nonclusteredのほうがソートが発生しない分、あくまでInsertの性能という観点からならば高速なのかな、とは思います。（テストして見た結果は、残念ながらどちらでも変わりませんでしたが……）

Answer 3

そうでしたか。統計情報は適切でしょうか？ 数十万回も実行されるとのことで、統計情報がずれてくると適切でないプランで実行されてしまう可能性もあります。

また、別の観点で、性能劣化はストアドプロシージャが原因と確定していますでしょうか？ C#から数十万回も呼び出しているとのことで、C#側には問題ないのでしょうか？ 例えば、C#からの呼び出しを1/10に減らし、ストアドプロシージャ内で10回実行した場合の速度はどうでしょうか。

Answer 4

統計情報については処理の実行前にsp_updatestatsを利用し、DB全体の統計情報を取得しております。
また、問題がC#側にあるのではないか、とのことですが、同ストアドプロシージャをSQLCMD等のツールから実行しても事象は再現するため、C#側に問題がある可能性は低いと考えています。

同ストアド内でのInsertの数を増やし、テストを実施してみましたが、それぞれのInsert自体はDuration=0で終わるのですが、それぞれのInsertの終了時間と、次のInsert開始時間の間に0.005～0.010秒程度の空白時間がございます。
ここの空白時間が削減できればと思うのですが……。

Answer 5

自己解決致しました。

結論から言うと、「SET IMPLICIT_TRANSACTIONS ON」をストアドの実行前に実行することで、性能改善が図れました。
（処理終了後には当然OFFにする処理を挟みます）

同設定をSQL Server 2000環境上では行っていないため、なぜ2000環境上で処理が早く実行されるのかは不明ですが、
2012環境上でのアプリケーションの処理速度が改善したため、案件Closeとさせていただきたいです。

ご協力ありがとうございました。今後ともよろしくお願いいたします。