DBServer-AとDBServer-Bにおけるパフォーマンス差異の原因について

Question

はじめて投稿します。

よろしくお願い申し上げます。

現在、下記のような環境でDBServerが構築されています。

****
DBServer-A
・AWS-EC2(m3.large)内にて構築
・CPU Intel(R)Xeon(R) CPU E5-2670 v2 @2.50GHz (仮想プロセッサx2)
・メモリ7.50GB
・SSD
・Windows Server 2012 R2 Standard(x64)
・SQL Server 2014 Web Edition
・System Center Endpoint Protection
****
DBServer-B
・社内物理Server内のHyper-Vにて構築
・CPU Intel(R)Xeon(R) CPU E5-2470 v2 @2.40GHz (仮想プロセッサx4)
・メモリ30.0GB
・SSD
・Windows Server 2012 Standard(x64)
・SQL Server 2014 Standard Edition
・ウイルスバスターコーポレートエディション
****
DBServer-AとDBServer-Bにそれぞれで、全く同じDatabaceをセットしています。

具体的には、DBServer-Aで作成したDBをデタッチし、DBServer-Bにアタッチし
て構築しています。


一見、あらゆる面において、「DBServer-B」の方がパフォーマンスが良いはずですが、
SQL Managiment Stadioで各種クエリを実行すると、全てにおいて、
「DBServer-A」の方が遥かに早い結果を齎せます。

例1)ViewをJoin参照したSELECT分(若干複雑なクエリ)
→DBServer-Aでは、2秒
→DBServer-Bでは、90秒

例2)55万件のDELETE文発行
→DBServer-Aでは、25秒
→DBServer-Bでは、52秒

単純SELECTにおいても、INSERT、UPDATE、DELETE、全てにおいて、
DBServer-Bでのパフォーマンスが著しく悪い状況です。


*********************
これまでに、確認した内容
*********************
(1)tempdbは仮想CPUにあわせて作成済み
　DBServer-Aは2つ
　DBServer-Bは4つ

(2)SQL Server 最大サーバーメモリは仮想メモリの75%で設定済み

(3)CrystalDiskMarkによるSSDのベンチマーク比較
　　【DBServer-A】
　　・Seq R[61.15MB/s] W[86.61MB/s]
　　・512k R[62.48MB/s] W[86.62MB/s]
　　・4k R[12.56MB/s] W[8.102MB/s]
　　・4kQD32 R[12.53MB/s] W[12.53MB/s]
　　【DBServer-B】
　　・Seq R[797.2MB/s] W[330.0MB/s]
　　・512k R[267.4MB/s] W[149.4MB/s]
　　・4k R[19.37MB/s] W[4.546MB/s]
　　・4kQD32 R[152.6MB/s] W[3.974MB/s]

(4)SQL Managiment Stadioで各種クエリを実行のパフォーマンスモニタ監視
　　MemoryAvailable Mbytes
　　MemoryPages/Sec
　　Paging File%Usage
　　Paging File%Usage Peak
　　Processor%Processor Time
　　Processor%User Time
　　Processor%Privileged Time
　　ProcessorInterrupts/Sec
　　SystemProcessor Queue Length
　　Physical Disk%Disk Time
　　Physical DiskCurrent Disk Queue Length
　　Physical DiskDisk Writes/Sec
　　Network InterfaceBytes Total/Sec
　　各種負荷率はDBServer-A,B共に同じような曲線となっており
　　ボトルネックとなる箇所を見つけられず

(5)リクエストに対するレスポンスが1000ミリ秒未満のクエリでは
　 パフォーマンス差を確認出来ず

(6)共にIndexの最適化、統計情報の更新は実行済み


***
ハードウエア環境もリソース環境も異なり、
ウイルスソフトの種類も違い、
OSもWin2012R2とWin2012と違い、
SQLも、WEB Edtionとstandard Edtionとの違いがあるため、
DB構造及びデータ量が同じとはいえ、
単純比較出来ない事は承知していますが、
とはいえ、インフラのリソース差は歴然であり、
DBServer-AよりDBServer-Bが劣るというのは考えにくい症状です。

ただ1点、SSDの4k(4kQD32)Writeパフォーマンスが劣っているのが気になりますが、これが原因でここまでの差が出ますでしょうか？

他に何か考えられる事がありますでしょうか？

何をどう調べるべきか、アドバイス頂けたら幸いです。

Answer 1

あと着目すべきは物理サーバーのメモリ帯域幅かな。

E5-2670のメモリ帯域幅は4ch合わせて51.2GB/s、E5-2470は3ch合わせて38.4GB/sです。

またメモリの種類はDDR3 800/1066/1333/1600のいずれかを使えるわけですが、DDR3 1333とか使ってた場合はもっと差が開くので。

---

甕星

Answer 2

その線で言うと、

（仮想サーバーでなく）物理サーバーの搭載メモリ量によってはメモリのクロックダウンも発生しますね。せっかくDDR3 1600を挿していても800MHz動作もあり得ます。

Answer 3

ご回答ありがとうございます。

メモリ帯域幅のよる差はやはり大きいようですね。

大変参考になりました。

ありがとうございました。

Answer 4

ご回答ありがとうございます。

物理サーバーの搭載メモリ量によるクロックダウン....盲点でした。

大変参考になりました。

ありがとうございました。

Answer 5

>ただ1点、SSDの4k(4kQD32)Writeパフォーマンスが劣っているのが気になりますが、これが原因でここまでの差が出ますでしょうか？
単純なDELETE文であれば、その実行速度はストレージの4k書き込み性能に大きく依存するはずです。
ですから *例2)55万件のDELETE文発行* については妥当な結果だと思います。

それよりも *例1)ViewをJoin参照したSELECT分(若干複雑なクエリ)* が顕著な違いを見せているので、その実行プランとにらめっこしながら地道にボトルネックを探しては如何でしょう。
ボトルネックが見つからなかった時は、ストレージの強化が堅実にパフォーマンスアップにつながると思います。
CrystalDiskMarkの結果をみると今のSSDはエンタープライズ向けではなさそうな感じですので、エンタープライズ向け製品を導入するかハードウェアRAIDボードを導入するなど検討されてはどうでしう。

Answer 6

ご回答ありがとうございます。

やはり、4K書き込みの影響は顕著に出ますね。
ありがとうございます。


View参照のSELECTは、謎のままです。

実行プランとにらめっこしてはみたものの、ボトルネックが見つからず状態です。

サブクエリを多様化してる若干複雑なクエリでありますが、例に記したとおり、

→DBServer-Aでは、2秒
→DBServer-Bでは、90秒

というパフォーマンスです。

しかし、Selectで返す結果のViewのカラムを参照除外すると、DBServer-Aと変わらぬパフォーマンスでレスポンスされます。
SQLを書くと長くなるので、例えば簡易的に示すと

***
select
  a.col as col1
 ,b.col as col2
from 
 TBL_A as a
 left join
 View_B as b /* VIEW */
 on
 a.key = b.key
***
とすると

→DBServer-Aでは、2秒
→DBServer-Bでは、90秒

***
select
  a.col as col1
-- ,b.col as col2
from 
 TBL_A as a
 left join
 View_B as b /* VIEW */
 on
 a.key = b.key
***
とすると

→DBServer-Aでは、2秒
→DBServer-Bでは、2秒

といった具合です。

※Viewはリンクサーバーを使ったOracleのデータだったので、ソコにボトルネックがあると当初は判断していたのですが、リンクサーバーを削除し、Oracleではなく、自身のテーブル(同一インスタンス内の同一データベース内に同じ形式で作成したテーブル)に変えても結果は同じで、リンクサーバーが原因ではないと判断し、SQL Serverに何か設定(チューニングオプション)があるのか？と迷った事が発端となっております。

※Viewの作りとしては簡単に示すと
with SampleView(Col1)
as
(
SELECT Col1 FROM TBL1 WITH (NOLOCK) where 条件

UNION ALL

SELECT Col1 FROM TBL2 
JOIN 
SampleView
ON 条件
where 条件
)
SELECT  * FROM SampleView

といった、with union all の再帰クエリとなっています。

※View単体の呼び出しは一切ストレスありません。

※また、あまり関係はないと思いますが追加情報として。。。
利用しているのは、SQL Server 2014ですが、データベースオプションの互換性レベルをSQL Server 2012(110)に切り替えて使用しています。
マスタ系のデータベースがSQL Server 2012で作成されたデータベース(mdf)で、そのmdfをSQL Server 2014にアタッチして利用しなければならない前提があり、マスタとJOINした際に、Version差異によるパフォーマンス低下が確認できたため、やむを得ず、データベースオプションの互換性レベルをSQL Server 2012(110)に切り替えて使用しています。

このあたりの追加情報で、引き続き、CPU(最大メモリー帯域幅)による性能差、及び、SSD4kWriteによる性能差以外で何か考えられる点があれば、ご教授願えれば幸いです。

Answer 7

View_Bのクエリをそのままストアドプロシージャにして、それと入れ替えてみたらでんな結果になるでしょうか？

追伸：未解決のようなので、回答としてマークは解除されたほうがいいのでは？

Answer 8

実行プランはどちらでも同じだったということですかね？

個人的な感覚では、マシンのパフォーマンスによる直接的な影響による差は、2倍とかその程度までで、それ以上差が出る場合は、単純なパフォーマンス差ではなく何か特別なことが起こっているという感覚です。

※微妙なパフォーマンス差が、偶然大きい結果の差を引き起こすことももちろんありますが

数十倍となると、うーん、どうでしょうね…

ちなみに、サーバBのCPUのハイパースレッディングは有効になっていますか？
無効にすると何か変わったりするでしょうか？

あと、並列クエリの設定がどうなっているかとかですかね…