C13 SQL Server2012知られざるTips集 by 平山理

SQL Server 2012
知られざるTips集
日本マイクロソフト
プレミアフィールドエンジニアリング
シニアプレミアフィールドエンジニア
平山理

Agenda

 Lock 獲得動作の変更
 統計情報自動更新の変更
 FORCESEEK 機能拡張
 クエリプラン収集機能拡張
 ページ割り当て状況の確認方法の改善
 Cardinality Estimate Framework

©2011 Microsoft Corporation. All Rights Reserved.

Lock 獲得動作の変更

3

SQL Server 2012 より前の動作

クライアント A
SELECT * FROM T
- T はパーティションテーブル
- SCH-S ロックが必要

クライアント B
ALTER TABLE T2 SWITCH PARTITION 1 TO T PARTITION 1
- パーティションをスイッチするDDLを実行
- SCH-M ロックが必要

クライアント C
DDL - SELECT * FROM T WITH (NOLOCK)
Starvation - SCH-S ロックが必要(Dirty Read であっても)


SQL Server 2012 より前の動作(続き)

SCH-S (スキーマ共有ロック)
- DDL によるスキーマ変更防止
- Dirty Read にも必要

SCH-M
- スキーマ変更ロック
ロック - スキーマ変更時の排他制御

SCH-S とSCH-Mは互換性なし

- 後続の SCH-S が付与され続ける
問題点 - SCH-M は待機し続ける


SQL Server 2012 の動作

15000 パーティションまで分割可能

パーティション数 DDL Starvation の機会増加

アルゴリズムの変更
- FIFOの採用
- SCH-SはSCH-Mをスキップしない

旧動作への復帰
- トレースフラグ 617
改善策 - 再起動不要


統計情報

自動更新のための閾値変更

7

従来の統計情報更新アルゴリズム

テーブル基数 =< 500 500 回の変更で統計情報を更新

テーブル基数 > 500 500 回+テーブル基数20% の変更で統計情報を更新

テーブル基数が大きい場合は統計情報の更新頻度低
例:
1,000,000,000 件のデータが存在するテーブルの場合

考慮点 1,000,000,000 * 0.2 + 500 = 200,000,500


トレースフラグ 2371 の導入

例:
1,000,000,000 件のデータが存在するテーブルの場合
更新タイミング:
SQRT(データ件数*1000) SQRT(1,000,000,000*1000) = 1,000,000

有効なバージョン:
SQL Server 2012 RTM
SQL Server 2008 R2 SP1 以降

再起動不要


sys.dm_db_stats_properties 動的管理ビュー

統計情報の内容を出力サンプル数、更新日時、更新回数などを含む

有効なバージョン:
SQL Server 2012 SP1 以降
SQL Server 2008 R2 SP2 以降

DBCC SHOW_STATISTICS の代替


FORCESEEK クエリヒント機能拡張

11

FORCESEEK クエリヒント

オプティマイザの決定をオーバライド

SEEK 操作のみを使用してデータにアクセス

FORCESEEK クラスタ化インデックス、非クラスタ化インデックスに有効


FORCESEEK クエリヒント

SEEK 操作のみ指定可能

~SQL Server 2008 R2 select * from t with (forceseek)

SEEK 操作に使用するインデックスと列を指定可能

SQL Server 2012 select * from t with with (forceseek (ix(c1, c2)) )


クエリプラン収集機能拡張

14

SQL Server 2008 R2 までは…

クエリプラン SQL Server
収集方法プロファイラ SQLTrace

収集する

OR

収集条件収集しない自由度低

収集できない
収集時の負荷高い環境が多い


SQL Server 2012 からは…

ツールの追加拡張イベント

Duration

OR
収集条件の収集対象の収集時の収集可能な
柔軟な設定 CPU Time 絞り込み可能負荷軽減環境の増加


サンプルコード

クエリプラン収集イベント: 収集条件:
query_post_execution_showplan CPU使用時間 10 秒以上

CREATE EVENT SESSION test_session
ADD EVENT sqlserver.query_post_execution_showplan(
WHERE ([cpu_time]>=(10000000)))
ADD TARGET package0.event_file(SET filename=N'C:¥Program Files¥Microsoft SQL
Server¥MSSQL11.ENGINE_DEV¥MSSQL¥Log¥test.xel')
WITH (
MAX_MEMORY=4096KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,
MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,
STARTUP_STATE=OFF
)
GO


ページ割り当て状況確認方法の改善

18

ページ割り当て状況確認

DBCC IND
~SQL Server 2008 R2 DBCC コマンドのみ DBCC EXTENTINFO データの加工に難あり

動的管理関数:
SQL Server 2012 sys.dm_db_database_page_allocations フィルタなどが容易


パラメータなど

sys.dm_db_database_page_allocations
( データベースID,
オブジェクトID,
インデックスID,
パーティションID,
出力オプション) – ‘LIMITED’ / ‘DETAILED’
パラメータ

select * from sys.dm_db_database_page_allocations( DB_ID(‘db1') ,
OBJECT_ID(‘tb1') , NULL , NULL , 'DETAILED' )
go

サンプルコード


Cardinality Estimate Framework

21

クエリ実行に時間がかかる(終わらない)……

統計情報不正

不適切なカージナリティ
クエリ実行プラン見積もり不正インデックス不足

クエリ実行プラン収集:
実行プラン内の不正イテレータの特定

調査方法 estimated = × actual = ○


Cardinality estimate framework

SET STATISTICS XML ON

~SQL Server 2008 R2
[Actual]プランの収集 SET STATISTICS PROFILE ON クエリが完了時に取得

Cardinality estimate framework(拡張イベント):
inaccurate_cardinality_estimate イベント

イテレータの実際の出力件数と、見積もり件数との差が閾値を超
SQL Server 2012 えたタイミングでイベント発生


イベント発生のしきい値

見積もり行数が1行の場合
オプティマイザがイテレータの見積もり行数を1行と見積もったケース。
イテレータの実際の処理数が2行以上になるとイベントが発生。

見積もり行数が2から100,000の場合
見積もり行数の5倍以上の行数をイテレータが処理するとイベントが発生。

見積もり行数が100,000より大きい場合
見積もり行数が、100,000より大きい場合。
イベントが発生するときの閾値は次の式で算出。

100,000 * (5-1.2) + 1.2 *[見積もり行数].


注意が必要なポイント

クエリが効率的に実行されていてもイベントが発生す
ることがある
ノイズ (FALSE POSITIVE ケース)

負荷内部的には statistics profile の収集が有効化される

- 過少見積もり
パフォーマンスへの影響大
過剰見積もり - 過剰見積もり
不検知パフォーマンスへの影響小

ノイズ発生率高
- 並列クエリの見積もり
見積もり行数を単純に並列数で均等に割った値
- 実際に各スレッドが処理する行数
並列クエリデータの配置状況などによって必ずしも均等に処理されるとは限らない


©
© 2011 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market
conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation.
MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.
26

C13 SQL Server2012知られざるTips集 by 平山理

More Related Content

What's hot

Similar to C13 SQL Server2012知られざるTips集 by 平山理

More from Insight Technology, Inc.

Recently uploaded

C13 SQL Server2012知られざるTips集 by 平山理