■ Windwosのパフォーマンスモニタで確認する項目

・SQLSERVERで使用しているドライブの下記の項目
\PhysicalDisk()\Avg. Disk Queue Length
\PhysicalDisk()\Disk Reads/sec
\PhysicalDisk()\Disk Writes/sec

・CPU
\Processor(_Total)\% Processor Time

・SQLのキャッシュヒット率
\SQLServer:Buffer Manager\Buffer cache hit ratio

SQL Statistics
・Batch Requests/sec
  →秒間のコマンド数(問合せのクエリ以外のコマンドもカウントされるので、
   実際のクエリ発行数ではありませんが、利用状況を把握できます)

Buffer Manager
 ・Page life expectancy
  →DB上にデータを保持している時間 300秒を下回っているとメモリが足りていない可能性

Locks
 ・Lock Requests/sec
 ・Lock Waits/sec
  →ロック待ち

Access Methods
 ・Index Searches/sec
 ・Probe Scans/sec
 ・Range Scans/sec
 ・Full Scans/sec
  →Full Scan(テーブルスキャン)が多いと適切なインデックスが無い可能性

■ディスクのパフォーマンス測定ツールとして
 CrystalDiskMark
 可能であれば、ファイルサイズ 1000MBで
 どれくらいのパフォーマンスが出るストレージか確認。

その他参考サイト
http://msdn.microsoft.com/ja-jp/magazine/cc135978.aspx

・不足インデックスのコスト
・I/O に関してコストのかかるクエリ
・CPU に関してコストのかかるクエリ

その他、DBのオブジェクト確認用スクリプト

select
SU.NAME,
(SELECT
 CASE SP.action
          WHEN '26' THEN 'REFERENCES'
          WHEN '178' THEN 'CREATE FUNCTION'
          WHEN '193' THEN 'SELECT'
          WHEN '195' THEN 'INSERT'
          WHEN '196' THEN 'DELETE'
          WHEN '197' THEN 'UPDATE'
          WHEN '198' THEN 'CREATE TABLE'
          WHEN '203' THEN 'CREATE DATABASE'
          WHEN '207' THEN 'CREATE VIEW'
          WHEN '222' THEN 'CREATE PROCEDURE'
          WHEN '224' THEN 'EXECUTE'
          WHEN '228' THEN 'BACKUP DATABASE'
          WHEN '233' THEN 'CREATE DEFAULT'
          WHEN '235' THEN 'BACKUP LOG'
          WHEN '236' THEN 'CREATE RULE'
          ELSE '判別不能'
   END )AS '権限',
SO.NAME,
(select
CASE SO.XTYPE
  WHEN 'U' THEN 'テーブル'
  WHEN 'P' THEN 'ストアドプロシジャー'
  ELSE 'それ以外'
  END ) AS '種類',
SO.CRDATE
from sysusers SU,sysprotects SP, sysobjects SO
where SU.UID = SP.UID AND SO.ID = SP.ID AND SO.XTYPE IN('U','P')
order by SU.NAME,SO.CRDATE desc
--order by SO.CRDATE desc,SU.NAME

db tech showcase 2012
に参加してきました。DBエンジニアとしては、非常に有意義で楽しいセミナーでした。

DB Tech Showcase

前職ではDBのコンサルやDB Teamのマネージャーしていましたが、
現職ではマネージメントに専念していてここ2~3年程きちんと触れていませんでしたが、
久々にDB技術の変化と触れ合ってきました。
DBのパフォーマンスは、CPU、メモリー、ディスク、ネットワークがメインですが、
最近はCPU、64bit化によるメモリー効率改善、SSDなどの影響でI/Oパフォーマンス向上して、
ネットワークに影響が出ているようで、InfinibandがMS SQL,MYSQL,Oracleでも盛り上がっている感じでした。

DB Tech Showcaseメモ

SQL Server

○AlwaysON
━Availability Group
データベース単位
  共有ディスク無し
  複数DBフェイルオーバー
  複数のセカンダリーサーバー(Max:4台―但しACKが遅いのでレスポンス注意)
CheckPoint単位で同期(1分デフォルト) Primary & Secondary同時
   ①ログ→②メモリー→③Checkpoint→④データファイル
   MSベンチマークでは代替63M/秒
━FailOver Cluster
 インスタンス単位
  共有ディスク
  Windows2008R2+SQL 2012 複数サイト(サブネットを跨いだクラスタリング2012から可能)
Windows Server FailOver Cluster

━FailOver Cluster+Availability Groupの構成もあり。
 ━Availability Group  (同期レプリカ、自動フェールオーバー、リーダブルセカンダリー)
━Availability Group DR (複数レプリカー同期(SYNC)は2台まで。
  非同期(A-SYNC)レプリカ (A-SYNCなのでコミットを待たない)
━マルチサブネット対応
 DBCC TRACEON(3499, -1) Availability Groupの書き込みパフォーマンス調整。

 ━パフォーマンス確認(SQL Server: SQL Statistics オブジェクト)
http://msdn.microsoft.com/ja-jp/library/ms190911.aspx
http://msdn.microsoft.com/ja-jp/library/ms190732%28v=sql.90%29.aspx
http://www.confio.com/db-resources/sql-wait-types/writelog/

Batch Requests/sec
Log write waits
Logical Disk Read Bytes/sec (バッファーメモリー、ディスクI/O)

注意: 同期レプリカの場合、2台のDBログに書き込んでからACKをサーバーに返す為パフォーマンス要確認。

○InfiniBand and SSD
 ━Windows2012 SQL2012からSMB3.0に対応
  SMB3.0対応
  SMB Direct (RDMAを持つNetwork Adapterをサポート)→Infinibandディフォルト対応。
  今まで ①L1:NIC→L2→L3:CPU→L7 これから L1->L7へダイレクト(高速)
SMB Multi Channel対応(Auto Failover,Auto Detect)
http://www.atmarkit.co.jp/fnetwork/tokusyuu/61ib02/01.html
http://msdn.microsoft.com/en-us/library/dd425070%28v=sql.100%29.aspx
http://www.mellanox.co.jp/
http://www.atmarkit.co.jp/fnetwork/tokusyuu/61ib02/01.html

デモ検証実測値)
Windows2012 6.5GB/Sec
SQL2012 4.2GB/Sec
IB Switch 3.8GB/Sec
SSD 2.5GB/Sec
  注:PCI Busの制限は要確認。(PCI-E: 4GB/Sec)
○SQL ServerとIndexの進化
 Column Store Index
特定行のカラムだけを取る。→ DistinctやGroup byが高速化
テーブルに対して1つだけ付与する事が出来る。(全ての列を含む1つインデックスを作成する事も可能)
 全てのデータをintに変換してHASHにして圧縮をかける(blobで保存)
 対応しているデータ型は特定されているので要確認。(int, real,datetime,money等) varcharは不可
CREATE COLUMNSTORE INDEX (Transact-SQL)
 http://msdn.microsoft.com/en-us/library/gg492153.aspx

 検証)
  Column Store Indexを利用しない場合
  41秒(Clustered Indexで処理)
(MAXDOP =0)
Column Store Indexを利用した場合
37秒 (MAXDOP =1)
Column Store Indexを利用した場合
7秒 (MAXDOP =0)
 注:ReadOnlyなので必要な場合はバッチで処理する。(tableはReadになる)
   パーティショニングに対応しているので、パーティション毎に作成して直近のデータのみバッチ作成等

○SQL Server Tuning Point
━SQL2012 Lock変更
SQL2012までは…….
(A) select * from T SCH-S
(B) alter table T SCH-M
(C) select * from T with(NOLOCK) SCH-S
(A) -> (C) -> (B) の順になりAとCの処理をBが待ち続ける。(DDL Starvation)
SQL2012以降は ……
FIFOアルゴリズム採用してSCH-SはSCH-Mをスキップしない。
  但しTraceFlag 617を設定する事でNOLOCKの処理を優先する事が出来る。(再起動不要)
━統計情報変更
自動更新の為の閾値
  テーブル基数(データ量)<=500件 500回のデータ変更で統計を更新   テーブル基数(データ量)> 500件 500回のデータ変更+20%の変更で統計更新
  (例) 10億件のデータがある場合
     10億X0.2+500= 200,000,500件の変更発生で統計情報更新
  Changes to automatic update statistics in SQL Server ? traceflag 2371 -> SQRT(データ件数 * 1000)
  http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx
  http://sqlserverpedia.com/blog/sql-server-bloggers/new-sql-2008-r2-sp1-trace-flag-adjusts-autostats-threshold/
  (例) 1,000,000,000件データがある場合は、SQRT(1,000,000,000X1000) = 1,000,000の変更で統計更新
  注:SQL2012, SQL2008R2 SP1から対応
  sys.dm_db_stats_properties を利用してサンプル数、更新日時、更新回数などを確認可能。(次回の統計更新タイミングが予測可能)
  http://msdn.microsoft.com/en-us/library/jj553546%28v=sql.105%29.aspx
DBCC SHOW_STATISTICSでも確認出来たが利便性向上の為に動的VIEWに変更された。
  http://sasuke.main.jp/sqrt.html

━FORCESEEK
  オプティマイザーをオーバーライト
  データへのアクセス パスとしてインデックスのシーク操作のみを使用するようにクエリ オプティマイザを設定可能。
  クラスター化、非クラスター化インデックスに有効。
  http://msdn.microsoft.com/ja-jp/library/bb510478%28v=sql.105%29.aspx
forceseekにインデックスの指定は出来無かったが、2008R2からは指定出来るようになった。
 select * from table with (forceseek) -> select * from table with (forceseek(ix(c1,c2))

━クエリープラン拡張
クエリープラン収集機能拡張
  ~SQL2008 R2 Profiler, SQL Trace, SET STATISTICS PROFILE
条件:収集する or 収集しない
  収集負荷が高い、収集出来ない事が多い
SQL2008 R2 ~
  拡張イベントとして設定可能(条件設定可能:実行時間、CPUtime 2秒以上のクエリーなどのみを取る事が出来る)
  絞り込み可能

 ━ページ割り当て確認
  SQL Server 2012では、DBCC INDやDBCC EXTENTINFOといったアンドキュメンテッドのコマンドにとって代わる
  sys.dm_db_database_page_allocationsという名前の新しい動的管理関数が導入されました。
  基本8K
~ SQL2008 R2までは DBCC EXTENTINFO
SQL2012 ~
  sys.dm_db_database_page_allocation
  http://enterprisezine.jp/dbonline/detail/3950?p=2

 ━Cardinality Estimation
  Estimate Framework
クエリー実行に時間がかかる場合
  → カージナリティ見積もり不正、統計不正、インデックス不正
  推定実行プラン(estimate)は、実際の実行プランと違う場合があるので何が実歳に違うか確認する事が出来る。(1回実行後)
統計情報の不正を確認出来るが、FALSE POSITIVEも拾ってしまうので注意
  実行中は負荷が上る、過剰見積もり、並列クエリーのノイズ(スレッド)などに注意しておく。
  Troubleshooting Poor Query Performance: Cardinality Estimation
  http://msdn.microsoft.com/en-us/library/ms181034%28v=sql.105%29.aspx

━高パフォーマンスの負荷で実行されている SQL Server 2005 および SQL Server 2008 のチューニング オプション
トレース フラグ 834: バッファ プールに対する Microsoft Windows large-page allocations の使用 
http://support.microsoft.com/kb/920093/ja
トレース フラグ 2301: 詳細な意思決定サポートの最適化を有効にする
http://support.microsoft.com/kb/920093/ja

━sys.dm_os_wait_stats (Transact-SQL)
  http://msdn.microsoft.com/ja-jp/library/ms179984.aspx
order by wait_time_ms descで思い処理を調査出来る。
○SQL ServerとNUMA

━MAXDOPは物理コア数に合わせる。(AMDだけ注意 16->8 8->4で設定)
 最近のHYPER-THREADは改善されていて、ONにしておくとOLTPでのパフォーマンスが10%程度上がる。
 OLTPとNUMA
 8Socket + Hyper Thread ON 2400ユーザー/秒
 4Socket + Hyper Thread ON 1400ユーザー/秒
ディスク構成例:Data (SSD),Index(SSD),T-Log(Disk), tempDB(DISK)
 NUMA設定で0~3はNetworksにCPUが利用される。
 sys.dm_os_memory_node_access_stats
* Cross NUMA node memory access statistics
* Trace Flag 842 is needed
 Page life Expectancyは30分程度はあった方が望ましい。(無い場合はメモリー不足を確認)
 http://www.atmarkit.co.jp/fdb/rensai/10_drk/03/drk02.html
 How to Identify Microsoft SQL Server Memory Bottlenecks
 http://www.mssqltips.com/sqlservertip/2304/how-to-identify-microsoft-sql-server-memory-bottlenecks/
 Finding Implicit Column Conversions in the Plan Cache
 http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/08/finding-implicit-column-conversions-in-the-plan-cache.aspx
 Coreinfo v3.1(mapping between logical processors and the physical processor)
 http://technet.microsoft.com/en-us/sysinternals/cc835722.aspx

その他、Always On
https://skydrive.live.com/?cid=DEC1C75BCA0DCEB2&id=DEC1C75BCA0DCEB2!11496

MYSQL

○MariaDB
http://mariadb.org/
http://enterprisezine.jp/dbonline/detail/4220
 better than MYSQL
Full Compatible with MYSQL
Bug-Free? (MYSQLのバグを見てFixしてからリリース)
 GPLv2
XtraDB = InnoDB
MYSQLと比較するとCheckpointの頻度が少ない。(DiskへのFlashが少ない)
OptimizerはMYSQL5.68より優れている。
 MariaDB5.3 GIS support
MyISAMを利用しているユーザーにはKey Cacheが1~64セグメント設定出来るのでお勧め。
 基本的には1つのメジャーバージョンは5年サポートする。
About XtraDB
https://kb.askmonty.org/en/about-xtradb/

○MYSQL5.6新機能
セミナー資料by 奥野さん
━レプリケーション
 GTIDを保持してどのスレーブが最新か確認し自動昇格可能
 change master to master_host=’~~~’, master_auto_position =1;
 mysqlfailoverツール(workbenchに付属→マスターの自動フェイルオーバー実行)
 http://b.l0g.jp/mysql/mysql565-gtid/
 http://dev.mysql.com/doc/workbench/en/mysqlfailover.html
 http://yoku0825.blogspot.jp/2012/10/mysqlfailovermysql-utilitieslinux.html

 クラッシュセーフスレーブ(ファイル→InnoDBにレプリケーション情報を持つ事が可能)
 mysqlbinlogコマンドによるバイナリーログバックアップ
 スレーブ利用するNIC指定
 マルチスレッドスレーブ

━InnoDB
 インデックス追加・削除がオンライン可能(Clustered Indexは別)
 *.idbファイルのImport/Export可能
 http://dev.mysql.com/doc/refman/5.6/en/flush.html
インデックス統計改善
 memcached Interface追加
 ログMAXサイズが512GBまで拡張可能
 FULLTEXTインデックスサポート
 ページサイズ指定(4KB,8KB,16KB)
バッファープールの内容を保存、再起動時の暖機運転不要
http://nippondanji.blogspot.jp/2012/10/mysql-56.html

━Optimizer
 Semi-Joinの最適化
 Explainの改善(select以外のDMLも確認可能)

 Order by ~ Limit句の改善
 オプティマイザートレース
 MRR (RowIDをバッファーに入れてソート処理)

━パーティショニング
 パーティション間でのJOIN可能
 パーティション作成可能数1024→8192へ。
 ロック改善(テーブルロックの対象がパーティション単位へ)

━その他
 セキュリティ (slaveのスタートにパスワード入力可能=パラメーターへの書き込み不要)
 OpenGIS
パフォーマンス確認、小数点以下へ対応
スケーラビリティ(R/W: MYSQL5.5比 → 151%改善 Read: NYSQL5.5比で234%改善)
Kernel_Mutexによる処理の分散
Flush改善(リソース状況により詳細調整可能)
http://www.mysqlperformanceblog.com/2011/03/31/innodb-flushing-a-lot-of-memory-and-slow-disk/


MS SQL2000, 2005, 2008にてデータベースに作成されたインデックスの調査する為のクエリー


SELECT "table" = object_name(i.id), i.name,
isclustered = indexproperty(i.id, i.name, 'IsClustered'),
"column" = col_name(i.id, ik.colid), ik.keyno
FROM sysindexes i
JOIN sysindexkeys ik ON i.id = ik.id
AND i.indid = ik.indid
WHERE i.indid BETWEEN 1 AND 254
AND indexproperty(i.id, name, 'IsHypothetical') = 0
AND indexproperty(i.id, name, 'IsStatistics') = 0
AND indexproperty(i.id, name, 'IsAutoStatistics') = 0
AND objectproperty(i.id, 'IsMsShipped') = 0
ORDER BY "table", "isclustered" DESC, i.name, ik.keyno

インデックス調査
db_index_confirm1


SQL2000, SQL2005, SQL2008でテーブルサイズを調査するクエリー
テーブル名、行数、データサイズ(MB)


SELECT object_name(id)as 'Table Name' ,rowcnt as 'Number of Rows',
dpages as 'Number of Pages',(dpages * 8 )/1024 'サイズ(MB)'
FROM sysindexes
WHERE indid IN (1,0)
AND OBJECTPROPERTY(id, 'IsUserTable') = 1
ORDER BY 'サイズ(MB)' DESC


■SQL2000, SQL2005, SQL2008対応

SQL2000のEnterpriseManagerや2005のManagement Consoleで
現在接続中のユーザーを確認するのと同等のクエリーです。


SELECT
spid,status,hostname,program_name,cmd,cpu
,physical_io,blocked,dbid,convert(sysname, rtrim(loginame)) as loginname
,substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' '
+ substring( convert(varchar,last_batch,113) ,13 ,8 ) as 'last_batch_char'
from master.dbo.sysprocesses (nolock)
where hostname <> ''

———————————————————————————
order by cpu desc,physical_io desc
———————————————————————————

SELECT
spid,status,hostname,convert(sysname, rtrim(loginame)) as loginname,
program_name,cmd,cpu,physical_io,blocked,dbid
,substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' '
+ substring( convert(varchar,last_batch,113) ,13 ,8 ) as 'last_batch_char'
from master.dbo.sysprocesses (nolock)
where hostname <> ''
order by cpu desc,physical_io desc