![[B31,32]SQL Server Internal と パフォーマンスチューニング ｂｙ Yukio Kumazawa](https://cdn.slidesharecdn.com/ss_thumbnails/sqlserverinternalupload-140204185245-phpapp01-thumbnail.jpg?width=560&fit=bounds)
More Related Content
What's hot (20)
Viewers also liked (13)
![[D35] インメモリーデータベース徹底比較 by Komori](https://cdn.slidesharecdn.com/ss_thumbnails/d35hp-140623212142-phpapp01-thumbnail.jpg?width=560&fit=bounds)
Similar to Sql server data store data access internals (20)
![【ウェブ セミナー】AI 時代のクラウド データ ウェアハウス Azure SQL Data Warehouse [実践編]](https://cdn.slidesharecdn.com/ss_thumbnails/webinarsqldw20170726-180220004900-thumbnail.jpg?width=560&fit=bounds)
![[ウェビナー] Build 2018 アップデート ～ データ プラットフォーム/IoT編 ～](https://cdn.slidesharecdn.com/ss_thumbnails/20180614azuredataiotwebinar-180614083401-thumbnail.jpg?width=560&fit=bounds)
![[Japan Tech summit 2017] DAL 003](https://cdn.slidesharecdn.com/ss_thumbnails/techsummit2017pdfdal003-171115033147-thumbnail.jpg?width=560&fit=bounds)
![[OSC 2017 Tokyo/Fall] OSSコンソーシアム DB部会 MySQL 8.0](https://cdn.slidesharecdn.com/ss_thumbnails/20170909osc-osscons-mysql8-170918232718-thumbnail.jpg?width=560&fit=bounds)
![[C14] ソーシャル ゲーム基盤を支える SQL Server by Takashi Inaba](https://cdn.slidesharecdn.com/ss_thumbnails/c14sqlserver-131119194051-phpapp01-thumbnail.jpg?width=560&fit=bounds)
More from Masayuki Ozawa (12)
Recently uploaded (11)
Sql server data store data access internals
- 1. SQL Server Data Store & Data Access Internals Masayuki Ozawa Microsoft MVP for SQL Server (July 2011 - June 2014)
- 2. 自己紹介 2014/03/22MVP Community Camp 20142 ? SQL Server を中心とした案件に携わりたいと思っているフリーランスの エンジニアです。 ? 主に IT Pro 領域の業務に携わっています。 … というより開発ができません。 何か案件のご相談がありましたらお声掛けください。 ? SQL Server の SQLTO / Azure の JAZUG というコミュニティで活動しています。 ? ブログでSQL Server を中心とした Microsoft 製品の情報を発信をしています。 ? SE の雑記 http://engineermemo.wordpress.com/ ? Twitter @Masayuki_Ozawa ? Facebook https://www.facebook.com/masayuki.ozawa
- 3. 本日の Agenda 2014/03/22MVP Community Camp 20143 ? SQL Server Data Store Internals ? SQL Server Data Access Internals Deep Dive / Internals というようなセッションタイトルを聞くと グッとくる方向けの誰得情報になればいいかなと。
- 4. 本日使用するクエリ 2014/03/22MVP Community Camp 20144 ? 本日は以下のクエリの動作を見ていきます ? SELECT Col2 FROM Table_1 WHERE Col1 = 40 ? SELECT COUNT(*) FROM Table_1 ? SELECT Col1 FROM Table_1 WHERE Col2 = 400 ? ぱっと見は単純なクエリ ? しかし、このクエリ、SQL Server の内部動作と 絡めると、とても奥が深いです!!
- 5. SQL Server Data Store Internals 2014/03/22MVP Community Camp 20145
- 6. Question : 内部動作はどうなるでしょう 2014/03/22MVP Community Camp 20146 ? SELECT Col2 FROM Table_1 WHERE Col1 = 40 1. Col1 = 40 と Col2 = 400 (対象の列) が取得される 2. Col1= 40 の行が取得される Col1 (int) Col2 (int) Col3 (nvarchar(100)) Col4 (nchar(450)) 10 100 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 20 200 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 30 300 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 40 400 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 50 500 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 60 600 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 70 700 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 80 800 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 90 900 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL ～ 省略 ～ 8000 80000 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL
- 7. Answer 2014/03/22MVP Community Camp 20147 ? 1. / 2. のどちらでもありません Col1 (int) Col2 (int) Col3 (nvarchar(100)) Col4 (nchar(450)) 10 100 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 20 200 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 30 300 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 40 400 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 50 500 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 60 600 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 70 700 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 80 800 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 90 900 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL ～ 省略 ～ 8000 80000 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL この範囲のデータが 取得されます
- 8. SQL Server のデータストア 2014/03/22MVP Community Camp 20148 ? 行ストア (Row Store) ? 行単位でデータを格納する ? デフォルトのデータストア方式 ? 列ストア (Column Store) ? 行のデータを列単位で格納する ? SQL Server では Column Store Index を使用 ? 今回の内容は一般的な行ストアについて ? 列ストアや In-Memory OLTP だとちょっと違ってきます Col1 Col2 Col3 Col4 10 100 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 20 200 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 30 300 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 40 400 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 50 500 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL
- 9. データ格納単位 2014/03/22MVP Community Camp 20149 ? 行 (レコード) が最小のデータ単位だが内部ではページで格納 ? ページ : 8KB のデータ格納領域 ? 8 ページをまとめたものをエクステントとして管理 (64KB) ? ページレベルでチェックサムを保持しいている 行データ (最大 8,060 バイト) 行オフセット (レコードの位置情報) ページの構造 8KB ページ 行ヘッダー (96 バイト) エクステント (64KB) ページ ページ ページ ページ ページ ページ ページ ページ
- 10. 先ほどのテーブルの実際の格納状態 2014/03/22MVP Community Camp 201410 列 バイト数 Col1 4 Col2 4 Col3 72 Col4 900 合計 980 Col1 (int) Col2 (int) Col3 (nvarchar(100)) Col4 (nchar(450)) 10 100 xxxxxxxx NULL 20 200 xxxxxxxx NULL 30 300 xxxxxxxx NULL 40 400 xxxxxxxx NULL 50 500 xxxxxxxx NULL 60 600 xxxxxxxx NULL 70 700 xxxxxxxx NULL 80 800 xxxxxxxx NULL 90 900 xxxxxxxx NULL ～ 省略 ～ 8000 80000 xxxxxxxx NULL 8060 / 980 = 8 レコード 全レコード数 = 800 レコード ページ #1 ページ #2 ページ #100
- 11. Undocumented Command 2014/03/22MVP Community Camp 201411 ? SQL Server の Books Online (SQL Server のヘルプドキュメント) には記載 されておらず公開されていないコマンド ? 主に内部情報を取得するために使用 ? Undocumented DBCC ? Undocumented Function ? Undocumented Dynamic Management View ? Undocumented Trace Flag
- 12. 2014/03/22MVP Community Camp 201412 レコードの格納状態を確認
- 13. データの密度を考える 2014/03/22MVP Community Camp 201413 ? ポイント ? データの密度が高い = 1 ページに格納されているレコード数が多い ? ディスクとメモリ間の I/O はページ単位で実施される ? レコード単位ではない ? エクステント単位 (64KB = 8 ページ)で実施されることもある メモリ ディスク DB Col1 Col2 Col3 Col4 10 100 xxxxxxxx NULL 20 200 xxxxxxxx NULL 30 300 xxxxxxxx NULL 40 400 xxxxxxxx NULL ～ 省略 ～ 8000 80000 xxxxxxxx NULL ページ ページ ページページ データをキャッシュする = ページをキャッシュする
- 14. データ密度を上げるには 2014/03/22MVP Community Camp 201414 ? 固定長のデータ型が必要かを検討 ? char / nchar で格納する必要があるか?? ? 固定長データ型のメリット ? 格納するデータ長の変更を受けにくい ? 断片化の発生を抑えることができる ? データの圧縮機能を使用 ? SQL Server のデータ圧縮は圧縮されたデータをディスク/メモリ上に格納する ? CDA (Compression Data Array) 形式でデータを格納し圧縮 ? ディスク使用量 / メモリ使用量 / ディスク I/O を抑えることが可能 (ただし CPU 負荷とのトレードオフ) ? Enterprise Edition の機能 ? SQL Database (Windows Azure) では全エディションで使用可能
- 15. 断片化 2014/03/22MVP Community Camp 201415 ? ページ内の格納領域が不足した場合に、新規のページにデータの半分 を移動して空き領域を確保 (50/50分割) Col1 Col2 Col3 Col4 10 100 xxxxxxxx NULL 20 200 xxxxxxxx NULL 30 300 xxxxxxxx NULL 40 400 xxxxxxxx NULL 50 500 xxxxxxxx NULL 60 600 xxxxxxxx NULL 70 700 xxxxxxxx NULL 80 800 xxxxxxxx NULL Col1 Col2 Col3 Col4 10 100 xxxxxxxx NULL 20 200 xxxxxxxx NULL 25 250 xxxxxxxx NULL 30 300 xxxxxxxx NULL 空き領域 Col1 Col2 Col3 Col4 40 400 xxxxxxxx NULL 50 500 xxxxxxxx NULL 60 600 xxxxxxxx NULL 70 700 xxxxxxxx NULL 80 800 xxxxxxxx NULL 空き領域 Col1 Col2 Col3 Col4 25 250 Xxxxxxxx NULL
- 16. 断片化による影響 2014/03/22MVP Community Camp 201416 ? ページ内の密度が下がる ? データの取得に複数のページを読み込む必要がある ? FILLFACTOR を設定することで事前に空き領域を確保しておくことが可能 ? ページ内に空きがあったとしても 8KB のサイズはメモリに確保される (データが書き込まれている領域のみキャッシュされるわけではない) ? ページの連続性がなくなる (Extent Scan Fragmentation) ? 連続したデータを読むのにアクセスコストが高くなる ? 一般的なアクセスコスト : ランダムアクセス > シーケンシャルアクセス ページ #1 ページ #2 ページ #3 ページ #100
- 17. 2014/03/22MVP Community Camp 201417 ディスクとメモリ間のデータアクセス 断片化を確認
- 18. SQL Server Data Access Internals 2014/03/22MVP Community Camp 201418
- 19. Question 2 : 内部動作はどうなるでしょう 2014/03/22MVP Community Camp 201419 ? SELECT COUNT(*) FROM Table_1 1. 内部のメタデータから件数が取得される 2. テーブル全体を検索する Col1 (int) Col2 (int) Col3 (nvarchar(100)) Col4 (nchar(450)) 10 100 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 20 200 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 30 300 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 40 400 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 50 500 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 60 600 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 70 700 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 80 800 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 90 900 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL ～ 省略 ～ 8000 80000 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL
- 20. Answer 2014/03/22MVP Community Camp 201420 ? 2. テーブル全体を検索します。 Col1 (int) Col2 (int) Col3 (nvarchar(100)) Col4 (nchar(450)) 10 100 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 20 200 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 30 300 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 40 400 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 50 500 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 60 600 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 70 700 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 80 800 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 90 900 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL ～ 省略 ～ 8000 80000 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL Full Scan
- 21. Scan と Seek 2014/03/22MVP Community Camp 201421 ? Scan ? データをすべて走査 ? Table Scan ? Clustered Index Scan ? Index Scan ? Seek ? 特定の範囲のデータを走査 ? Clustered Index Seek ? Index Seek ? 一般的な I/O コストとしては Scan > Seek ? 過度な Scan は I/O コストだけでなく、CPU コストにもつながる
- 22. 実行プランを確認 #1 2014/03/22MVP Community Camp 201422
- 23. 実行プランを確認 #2 2014/03/22MVP Community Camp 201423 SET STATISTICS PROFILE ON SELECT COUNT(*) FROM Table_1
- 24. 件数取得の注意点 2014/03/22MVP Community Camp 201424 ? 非クラスター化インデックスが設定されていないテーブルへの COUNT はテーブルスキャンが行われる ? 非クラスター化インデックスのないテーブルへの COUNT はコストが高い ? Non Clustered Index Scan も該当インデックスの全件スキャンではあるが、イン デックス列のスキャンになるので I/O コストは Clustered Index Scan より低い ? 概算の件数取得でよい場合は sys.dm_db_partition_stats を利用 ? オブジェクト単位のデータ格納状況を取得するための動的管理ビュー ? 大量のデータ変更をした後などは実際の件数と差が出ることがあるが、 瞬時に件数を取得することが可能
- 25. 2014/03/22MVP Community Camp 201425 件数取得の動作確認
- 26. Col1 (int) Col2 (int) Col3 (nvarchar(100)) Col4 (nchar(450)) 10 100 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 20 200 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 30 300 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 40 400 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 50 500 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 60 600 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 70 700 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 80 800 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 90 900 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL ～ 省略 ～ 8000 80000 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL Question : 内部動作はどうなるでしょう 2014/03/22MVP Community Camp 201426 ? SELECT Col1 FROM Table_1 WHERE Col2 = 400 1. Col1 (クラスター化インデックス) と Col2 (非クラスターインデックス) のデータを利用 2. Col2 (非クラスターインデックス) だけで完結
- 27. Answer 2014/03/22MVP Community Camp 201427 ? 1. / 2. のどちらも正解と言えなくもない ? クラスター化インデックスが設定されているテーブルの非クラスター 化インデックスにはクラスター化インデックスの列が含まれる インデックス 項目 クラスター化 インデックス項目 100 10 200 20 300 30 400 40 500 50 600 60 700 70 800 80 900 90 実データへの リンク Col1 (int) Col2 (int) Col3 (nvarchar(100)) Col4 (nchar(450)) 10 100 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 20 200 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 30 300 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 40 400 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 50 500 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 60 600 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 70 700 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 80 800 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL 90 900 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL ～ 省略 ～ 8000 80000 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx NULL
- 28. 非クラスター化インデックス 2014/03/22MVP Community Camp 201428 ? 行を高速に特定するための索引 ? インデックスが設定されていない項目に対しての検索は Scan が行われる ? 必要なデータのみを取得するためのデータ格納領域 ? クラスター化インデックス = 行の実体 (キー項目順でデータを格納する) ? 非クラスター化インデックス = 指定項目のみ ? インデックスに格納されている項目のみを取得することでクエリが完結するの がベスト ? カバードインデックス ? 付加列インデックス ? フィルタされたインデックス
- 29. 2014/03/22MVP Community Camp 201429 非クラスター化インデックスの動作確認
- 30. 最後に 2014/03/22MVP Community Camp 201430 ? 今回はシンプルなクエリを例にしていますが、基本的な考えは複雑な クエリでも変わりません。 ? テーブル最適化 / インデックス最適化の目的 →データを効率よく格納し、データを効率よく取得する ? 今回は参照系の処理でお話をしましたが、更新系の処理についても単 純な処理でも奥が深いので興味ある方は是非調べてみてください!! ? 懇親会にも参加していますので、質問等ありましたらお声掛け下さい ? 仕事のご相談も大歓迎です！！