インデックスチューニングウィザード

・	実行方法
	1.	SQL プロファイラでステータスを実行、名前をつけて保存
	2.	Enterprise Manager → ［ツール］ → ［ウィザード］（またはクエリアナライザ → ［クエリ］） → ［インデックスチューニングウィザード］を起動
	3.	手順 1. で保存したワークフローファイルを開き、分析
・	推奨されるインデックスを表示
	−	インデックスの追加、削除または変更を推奨
・	特定クエリに対するチューニング
	−	SQL プロファイラによるワークロード（サンプル）を分析、データベースのパフォーマンスを向上させられる最適なインデックスセットを推奨
※	チューニングウィザードを使用しないほうがよい場合
	−	システムテーブル
	−	現在選択されているデータベースに存在しない、複数のデータベースにまたがったクエリによって参照されるテーブル
	−	PRIMARY KEY 制約と一意なインデックス

統計情報 （ヒストグラム）

・	クエリオプティマイザが最適な判断を行うためのデータ分布情報
・	クエリオプティマイザは統計情報を使用してクエリに対してインデックスを使用する場合のコストを予測
・	パフォーマンスに影響を及ぼす
	→ 統計情報が古いとパフォーマンス低下
	→ ［推定実行プラン］で警告表示
・	列値のサンプリングと分布に関する情報を格納：検索条件に該当するデータが何件あるか
・	データの変更に伴い統計情報の更新が必要
・	作成された統計情報は sysindexes システムテーブルの statblob 列に格納

○ 統計の作成

・	自動作成 （既定）
	−	インデックス作成時にインデックス列内の値の分布情報を自動的に作成
	−	結合述語または WHERE 句で使用されるインデックスが作成されていない列
		※	クエリオプティマイザの判断材料として必要な場合はインデックスがない列に対しても自動作成
		※	自動作成された統計グループの名前は「_WA_Sys_...」となる
・	手動作成
	⇒	CREATE STATISTICS 統計グループ名 ON {テーブル名|ビュー名} (列名 )
		…	与えられた1つの列／列グループに対して統計情報を作成
		FULLSCAN オプション 統計情報収集のためにテーブルの全行を読み取り SAMPLE オプション 統計情報収集のためにテーブルの一部行を無作為抽出 → 行数または割合で指定
	⇒	sp_createstats
		…	カレントデータベース内の全ユーザーテーブル中で条件を満たす全列に統計情報を1列ずつ作成
※	auto create statistics オプションで設定
※	sp_autostats システムストアドプロシージャで対象テーブルの統計情報を表示

○ 統計の保守

・	自動更新 （既定）
	−	変更した行数が「500 + 0.2 * 全行数」を超えると自動更新
	−	sp_autostats で設定変更
・	手動更新 ← パフォーマンス低下時等
	UPDATE STATISTICS {テーブル名|ビュー名} テーブル／ビュー個別の更新 sp_updatestats データベース内の全テーブルの更新
※	auto update statistics オプションで設定
	インデックス作成時の CREATE INDEX ステートメントで NORECOMPUTE オプション指定で統計自動更新無効化
※	統計情報の更新にはオーバーヘッド発生
※	SQL Server 6.5 では手動更新 → 更新しないと最適な実行プランが選択されない
※	データ更新が過剰な場合に統計の自動更新が負荷になるケースがある
【参照】 マイクロソフト 技術情報 195565
		… 6 〜 500 件処理時には 500 回変更ごとに更新
		500 件以上処理時には 500 + テーブルの 20% 変更ごとに更新

インデックスのページ充填率指定

リーフレベル	FILLFACTOR オプション
非リーフレベル	PAD_INDEX オプション → FILLFACTOR と同じ充填率を指定   ※ FILLFACTOR が指定されていないと使用不可

インデックス情報の表示

⇒	sp_helpindex
⇒	Enterprise Manager → テーブル右クリック → ［すべてのタスク］ → ［インデックスの管理］

インデックスの作成方法

⇒	Enterprise Manager → テーブル右クリック → ［テーブルのデザイン］ → ［インデックス/キーの管理］
	→ ［すべてのタスク］ → ［インデックスの管理］
⇒	インデックス作成ウィザード
⇒	CREATE INDEX および DROP INDEX ステートメント
	−	WITH SORT_IN_TEMPDB オプション
		→ インデックス作成時の一時作業領域を tempdb データベースに指定
		※ オプションを指定しない場合は出力先のファイルグループに格納
・	インデックスキー値
	−	一意／一意でない
	−	単一列／複数列 （複合インデックス）
・	実行権限：テーブルの所有者
※	一意性を保証したインデックスの作成
	⇒	CREATE UNIQUE INDEX
	→	キー値に重複値がない場合のみ作成可

インデックス作成のガイドライン

推奨
	−	主キーと UNIQUE キー → 自動作成
	−	頻繁に範囲検索される列
	−	並べ替え順で頻繁にアクセスされる列
	−	集計時に頻繁にグループ化（結合）される列
	−	外部キー制約が設定された列 （デフォルトで作成されない） → 結合効率がよくなる
非推奨
	−	クエリでほとんど参照されない列
	−	一意の値をほとんど含まない列
	−	text、ntext、image データ型で定義されている列
	−	表に含まれるデータが少ない場合
複合インデックス（複数列をキー値として指定）推奨
	−	検索キーとして最適な列が2つ以上ある場合（最大 16 列まで組み合わせ可能）
	−	クエリがインデックス内の列だけを参照する場合
	※	複合インデックスを構成する各列の長さの合計は 900 バイトを超えることは不可
・	インデックスのキーサイズをできるだけ小さくする → 大きいと再構築に時間がかかる
・	行サイズに対するキーサイズの比率を小さくする
☆	無駄なインデックスを削除するだけでパフォーマンス向上が見込める

○ データベースの利用形態とインデックス作成基準

情報（検索）系	推奨	データ検索を高速化
基幹（更新）系	非推奨	更新時にオーバーヘッド発生

行の検索

○ インデックスを使用しない場合

・	indid = 0
・	IAM ページをスキャンしてヒープのページを保持しているエクステントを見つけ、テーブルスキャンを実行
※	ヒープ
	−	データページのコレクション
	−	データはランダムに格納
	−	ページ間で相互リンクなし
※	IAM … 順番が定義されていないページで唯一位置情報を定義している情報

○ クラスタ化インデックスを使用した場合

・	indid = 1
・	root 列 … クラスタ化インデックスのルートレベル
・	ルートレベルよりインデックスをたどって移動、クラスタ化インデックスキーに対応する列を検索
・	連続した範囲のキー検索は範囲内の開始キー値を見つけ、前後のポインタを使用して各データページをスキャン

○ 非クラスタ化インデックスのみを使用した場合

・	indid = 2 〜 250
・	root 列 … 非クラスタ化インデックスのルートレベル
・	ルートレベルよりインデックスをたどって移動、非クラスタ化インデックスキーとともに行ロケータを取得
・	行ロケータによってポイントされた対応する行をヒープより取得

○ クラスタ化インデックスと非クラスタ化インデックスを併用した場合

・	ルートレベルより非クラスタ化インデックスをたどって移動、非クラスタ化インデックスキーとともにクラスタ化キーを取得
・	取得したクラスタ化キーを用いて再度クラスタ化インデックスをルートレベルより検索
・	クラスタ化インデックスキーに対応する行を見つける
・	実データの物理的位置が変更になっても非クラスタ化インデックスに影響なし
※	検索パフォーマンスは多少低下
※	データの修正と保守に高いパフォーマンス ← データ更新によるページ分割の影響を受けない
※	SQL Server 7.0 以降変更？

sysindexes テーブル

・	各データベースでインデックスとテーブルごとに1行のデータを保持
・	テーブルとインデックスに関する情報を格納
		ページポインタ、各テーブルの行数、データページ数、統計情報 等
・	テーブル、インデックス、インデックス付きビューの全ページ集合はページポインタによって固定
	−	indid 列
		0 ヒープ → クラスタ化インデックスなし → first IAM 列を参照して IAM ページの先頭から検索 1 クラスタ化インデックス 2 〜 250 非クラスタ化インデックス 255 text、ntext、image 列
		※ DBCC CHECKTABLE ステートメント等で indid 指定可
	−	first IAM 列 … IAM ページのリーフレベルの先頭ページまたはルートページ
	−	root 列
		indid = 1 〜 254 インデックスを使用した場合のルートページ
	−	name 列
		indid = 0 テーブル名 indid = 1 〜 255 インデックス名
	−	first 列 … リーフレベルの先頭ページまたはルートページ

非クラスタ化インデックスの自動再構築タイミング

・	クラスタ化インデックスが作成または削除された場合
・	クラスタ化インデックスを定義する列が変更された場合
	← リーフレベルのソートが変更されるため

インデックスの最大数

1つのテーブルまたはビューに対して

クラスタ化インデックス 1つのみ作成可 非クラスタ化インデックス 最大 249 個作成可 （単一キーのみで構成した場合。複合キーが含まれると最大数減）

インデックスの特徴

利点
・	データのアクセスを高速化
・	行の一意性を適用
欠点
・	ディスク領域を消費
・	オーバーヘッドの発生

DTS によるデータ変換

○ データ型のマッピング

・	変換元と変換先の間でデータフォーマット方法を指定可
・	変換元と変換先の間でデータ修正方法を指定可
※	可能な限り一致するデータ型を定義、またマッピングを無効にして異なる型への変換も可
	データ型、サイズ、精度、スケール、NULL 値の許容等

○ データ（テーブル／列）の統合および併合

・	データ組み合わせ、分割可
・	データの垂直または水平要約可

○ 変換タスクの定義

・	各タスクは作業単位を定義
・	タスクの実行内容
	−	Transact-SQL ステートメントの実行
		←	定義済みタスクオブジェクト（SQL 実行タスク、一括挿入タスク、データドリブンクエリタスク）の使用
	−	スクリプト（JScript、PerlScript、VBScritp）の実行
	−	外部プログラムの起動
		←	定義済みタスクオブジェクト（プロセス実行タスク、メール送信タスク）の使用
	−	SQL Server オブジェクトの別インスタンスへのコピー
	−	DTS パッケージから実行または結果取得

○ ワークフローの定義

・	優先順位制約により設定
	−	制約順序、並列、組み合わせの条件でステップを実行
・	ステップの優先度を設定可 → 低／通常／高の3段階
・	トランザクション機能
	−	タスクをグループ分け
	−	使用するデータプロバイダがトランザクションをサポートする必要あり
	−	DTS デザイナは一度に1つのトランザクションしかサポートしない
		→	複数使用する場合は直列化、順序に注意

○ DTS パッケージの作成

・	DTS パッケージの作成ツール
	−	DTS インポート／エクスポートウィザード
	−	DTS デザイナ
	−	COM オートメーションサポート言語 （Visual Basic、Visual C++）
・	DTS パッケージの保存方法
	−	COM 構造化ストレージファイル
		→	.dts 拡張子ファイルとして保存
		→	SQL Server の外部からパッケージにアクセスできる唯一のオプション
		→	バージョンをつけて複数のパッケージを1つのファイルに保存可
		→	パッケージの配布、スケジュール化が容易
	−	SQL Server Meta Data Services
		→	オブジェクト情報、オブジェクト間関係情報を格納する共通データベース
		→	DTS パッケージメタデータと変換されたデータの完全な履歴情報（データ系列）を格納
		→	パッケージメタデータが他アプリケーションから再利用可
		→	同一ファイルに複数バージョン保存可
	−	msdb データベース
		→	sysdtspackages システムテーブルに保存
		→	同一ファイルへの複数バージョン保存不可
	−	Visual Basic ファイル
		→	VB アプリケーションにパッケージを組み込み可
・	パッケージセキュリティの実装
	−	COM 構造化ストレージファイルまたは SQL Server に保存する際に暗号化可
	−	所有者パスワード
		→	全情報およびプロパティへの完全アクセス、編集実行可
	−	ユーザーパスワード
		※	所有者パスワードで暗号化された DTS パッケージにのみ利用可
		→	パッケージ実行可、パッケージ定義へのアクセス不可

○ DTS パッケージの実行

・	Enterprise Manager または dtsrun ユーティリティで実行可

○ DTS パッケージのスケジュール

・	DTS パッケージを msdb データベースに保存する場合は DTS インポート／エクスポートウィザードでスケジュール
・	dtsrun ユーティリティで DTS パッケージを実行するジョブを作成する場合は Enterprise Manager でスケジュール

DTS コマンド

・	DTS の実行
	⇒	dtsrun [{/[~]Sサーバー名{/[U]ユーザー名[/[~]パスワード]|/E}|{/[~}Fファイル名/[~]Rリポジトリデータベース名}{/[~]NDTSパッケージ名[/[~]MDTSパッケージパスワード]|[/[~]GパッケージID]|[/[~]VパッケージバージョンID]}[!x][/!d][/!y][/!c]]
	−	各パラメータに「~」をつけることでパラメータ値を暗号化可
・	DTS インポート／エクスポートウィザードの起動
	⇒	dtswiz [{{[/uログインID][/pパスワード]}[{/i|/x}]{/r相手先データソース|[/s相手先サーバー名][/d相手先データベース名]}}]
	/n Windows 認証使用 /i SQL Server インスタンスへのインポートを指定 /x SQL Server インスタンスからのエクスポートを指定
※	¥Program Files¥Microsoft SQL Server¥80¥Tools¥Binn

DTS のツール

・	DTS インポート／エクスポートウィザード
・	DTS デザイナ
・	dtsrun ユーティリティ
☆	DTS ツールの使用に必要な権限
	ソースデータベース SELECT 権限 転送先データベース データベース所有者

DTS の概要

※	OLE DB、ODBC またはテキストファイル形式間でのデータ転送、変換を行う
・	DTS の機能
	−	データソースとデータ転送先間でのデータのインポート／エクスポート
	−	データ変換
	−	SQL Server データベース間でのデータベースオブジェクト転送
・	DTS の用途
	−	サードパーティ製品と統合できるカスタム変換オブジェクトの作成
	−	データウェアハウスおよびデータマートの構築
	−	OLE DB プロバイダを使用するアプリケーションにアクセス
※	異種データソース間ではスキーマとデータのみ移動可
	→	データベースオブジェクトの移動は SQL Server 間のみ
・	DTS がサポートするデータソースおよび転送先
	ネイティブ OLE DB SQL Server、Access、Excel 等各種データベース ODBC Oracle、Access、DB2   ※ Microsoft OLE DB Provider for ODBC 使用 ASCII テキストファイル 固定長または区切り記号フィールドの ASCII テキストファイル カスタマイズ サードパーティ製 OLE DB プロバイダ

SQL Server データエクスポート／インポートツール

・	DTS インポート／エクスポートウイザード
	−	異種データソース間の単純データ転送
・	DTS デザイナ
	−	異種データソース間の複数ソースおよび複雑データ転送
・	DTS オブジェクト転送タスク
	−	上記2つにおけるデータベースオブジェクト転送 （SQL Server 7.0/2000 同士）
・	DTS の一括挿入タスク （DTS BULK INSERT、SQL Server 7.0 以降）
	−	テキストファイルから大量データ一括転送
	→	パフォーマンスが要求されるコピー （データ変換不可）
	→	bcp とほぼ同等機能、ただしデータファイル → SQL Server テーブルへのインポートのみ
・	bcp ユーティリティ （一括コピープログラム）
	−	SQL Server テーブルとデータファイル間またはネイティブデータのデータ転送
・	レプリケーション
	−	複数データベース間でのデータ変更即時コピー維持
・	デタッチ／アタッチ
・	SELECT...INTO／INSERT...SELECT／BULK INSERT、BACKUP／RESTORE 等のステートメント

ODBC データソース名の設定

・	ODBC アプリケーションはデータソースを使用して SQL Server に接続
・	データソースが保持する情報
	−	指定されたデータソースに接続するために使用する ODBC ドライバ
	−	データソースへの接続情報 （データソースの名前、場所、ログインアカウント、パスワード等）
	−	接続に使用するドライバ固有オプション
	−	一意なデータソース名 （DSN）

リンクサーバー

・	リンクサーバーの利点
	−	リモートサーバーにアクセス可
	−	データソースに対して、分散クエリ、更新、分散トランザクション実行可
・	異なるサーバー上の OLE DB データソースに対してコマンド実行を実現
・	リンクサーバー定義時に OLE DB プロバイダと OLE DB データソースを指定
・	OLE DB プロバイダ
	−	特定のデータソースを管理、対話を可能にする DLL
	−	OLE DB データソースは OLE DB を介してアクセス可能な特定のデータベースを識別
	−	データソースとしてデータベースのほかテキストファイル、スプレッドシートデータ、古テキスト検索結果等に対処可
・	リンクサーバーの定義
	−	定義時に OLE DB ソースが SQL Server に接続するための情報を登録
		→	登録完了後、そのデータソースは常に1つの論理名で参照
	−	Enterprise Manager または sp_addlinkedserver システムストアドプロシージャを使用して定義を作成
	※	リンクサーバーの定義には完全修飾名を使用
・	リンクサーバーのセキュリティの確立
	−	リンクサーバー接続時、送信側サーバーは受信側サーバーに接続するためにログイン名とパスワードを自分自身で提供して接続
	−	Enterprise Manager または sp_addlinkedsrvlogin システムストアドプロシージャを使用してリンクサーバー間のログインマッピングを作成
	−	自己マッピング … リンクサーバー設定のデフォルトマッピングはログインアカウントの現在のセキュリティ資格情報をエミュレート
		→	sp_addlinkedserver でリンクサーバーを追加するとすべてのローカルログインアカウントに対して既定の自己マッピングを追加
	−	いずれかの側でセキュリティアカウントの委任ができない場合または Windows 認証モードが認識されない場合
		→	Windows 認証済みログインアカウントに対して自己マッピングが動作しない
			←	Windows 認証済みログインアカウントからリンクサーバー上特定ログインアカウントへログインマッピングの設定が必要
		※	リンクサーバーが SQL Server のインスタンスである場合、リモートログインアカウントが SQL Server 認証済みログインアカウントとなる
	−	セキュリティアカウントの委任が利用可で Windows 認証をサポートする場合
		→	Windows 認証済みログインアカウントの自己マッピングをサポート

分散クエリを使用したデータ変換

・	分散クエリの使用により、ローカルやリモートコンピュータに格納された異種データの処理が可能
・	OLE DB プロバイダや ODBC ドライバによってアクセスできるリレーショナルデータベースまたは非リレーショナルデータベースデータをサポート
・	異種データへのアクセス確立方法
	−	リンクサーバー → 異種データに繰り返しアクセスする場合
	−	OPENROWSET 関数 → 異種データに一時的にアクセスする （繰り返しアクセスする必要のない）場合
		※ 異種データの情報をパラメータで指定
		⇒ OPENROWSET('ソース名','サーバー名';'ユーザー名';'パスワード','ステートメント')
	−	OPENQUERY 関数 → 異種データに繰り返しアクセスする場合
		クエリをリンクサーバー上で処理、結果をローカルの SQL Server に返す
		※ 事前にリンクサーバーの定義が必要
		⇒ OPENQUERY(サーバー名,'ステートメント')

データ変換時に必要となる操作

・	データ形式の変更
・	データの変換およびマップ
・	データ整合性の保持
・	データ妥当性検査
・	作業スケジュール
・	異種環境間でのデータインポート／エクスポート