2005年10月05日

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

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

統計情報 (ヒストグラム)

クエリオプティマイザが最適な判断を行うためのデータ分布情報
クエリオプティマイザは統計情報を使用してクエリに対してインデックスを使用する場合のコストを予測
パフォーマンスに影響を及ぼす
     → 統計情報が古いとパフォーマンス低下
           → [推定実行プラン]で警告表示
列値のサンプリングと分布に関する情報を格納:検索条件に該当するデータが何件あるか
データの変更に伴い統計情報の更新が必要
作成された統計情報は 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% 変更ごとに更新
posted by w@ko at 18:25|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

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

リーフレベル FILLFACTOR オプション
非リーフレベル PAD_INDEX オプション
→ FILLFACTOR と同じ充填率を指定
  ※ FILLFACTOR が指定されていないと使用不可
posted by w@ko at 18:25|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

インデックス情報の表示

sp_helpindex
Enterprise Manager → テーブル右クリック → [すべてのタスク] → [インデックスの管理]
posted by w@ko at 18:24|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

インデックスの作成方法

Enterprise Manager → テーブル右クリック → [テーブルのデザイン] → [インデックス/キーの管理]
                                                            → [すべてのタスク] → [インデックスの管理]
インデックス作成ウィザード
CREATE INDEX および DROP INDEX ステートメント
  WITH SORT_IN_TEMPDB オプション
    → インデックス作成時の一時作業領域を tempdb データベースに指定
      ※ オプションを指定しない場合は出力先のファイルグループに格納
 
インデックスキー値
  一意/一意でない
  単一列/複数列 (複合インデックス)
 
実行権限:テーブルの所有者
 
一意性を保証したインデックスの作成
  CREATE UNIQUE INDEX
  キー値に重複値がない場合のみ作成可
posted by w@ko at 18:24|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

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

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

○ データベースの利用形態とインデックス作成基準
情報(検索)系 推奨 データ検索を高速化
基幹(更新)系 非推奨 更新時にオーバーヘッド発生
posted by w@ko at 18:23|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

行の検索

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

○ クラスタ化インデックスを使用した場合
indid = 1
root 列 … クラスタ化インデックスのルートレベル
ルートレベルよりインデックスをたどって移動、クラスタ化インデックスキーに対応する列を検索

 
連続した範囲のキー検索は範囲内の開始キー値を見つけ、前後のポインタを使用して各データページをスキャン

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

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

 
ルートレベルより非クラスタ化インデックスをたどって移動、非クラスタ化インデックスキーとともにクラスタ化キーを取得
取得したクラスタ化キーを用いて再度クラスタ化インデックスをルートレベルより検索
クラスタ化インデックスキーに対応する行を見つける
実データの物理的位置が変更になっても非クラスタ化インデックスに影響なし
検索パフォーマンスは多少低下
データの修正と保守に高いパフォーマンス ← データ更新によるページ分割の影響を受けない
SQL Server 7.0 以降変更?
posted by w@ko at 18:22|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

2005年09月28日

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 列 … リーフレベルの先頭ページまたはルートページ
posted by w@ko at 18:16|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

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

クラスタ化インデックスが作成または削除された場合
クラスタ化インデックスを定義する列が変更された場合
  ← リーフレベルのソートが変更されるため
posted by w@ko at 18:16|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

インデックスの最大数

1つのテーブルまたはビューに対して
クラスタ化インデックス 1つのみ作成可
非クラスタ化インデックス 最大 249 個作成可
(単一キーのみで構成した場合。複合キーが含まれると最大数減)
posted by w@ko at 18:15|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

インデックスの特徴

利点
データのアクセスを高速化
行の一意性を適用
欠点
ディスク領域を消費
オーバーヘッドの発生
posted by w@ko at 18:15|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

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 でスケジュール
posted by w@ko at 18:14|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

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
posted by w@ko at 18:14|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

DTS のツール

DTS インポート/エクスポートウィザード
DTS デザイナ
dtsrun ユーティリティ
 
DTS ツールの使用に必要な権限
 
ソースデータベース SELECT 権限
転送先データベース データベース所有者
posted by w@ko at 18:14|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

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 プロバイダ
posted by w@ko at 18:13|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

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 等のステートメント
posted by w@ko at 18:13|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

ODBC データソース名の設定

ODBC アプリケーションはデータソースを使用して SQL Server に接続
 
データソースが保持する情報
  指定されたデータソースに接続するために使用する ODBC ドライバ
  データソースへの接続情報 (データソースの名前、場所、ログインアカウント、パスワード等)
  接続に使用するドライバ固有オプション
  一意なデータソース名 (DSN)
posted by w@ko at 18:12|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

2005年09月17日

リンクサーバー

リンクサーバーの利点
  リモートサーバーにアクセス可
  データソースに対して、分散クエリ、更新、分散トランザクション実行可
 
異なるサーバー上の 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 認証済みログインアカウントの自己マッピングをサポート
posted by w@ko at 17:24|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

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

分散クエリの使用により、ローカルやリモートコンピュータに格納された異種データの処理が可能
 

 
OLE DB プロバイダや ODBC ドライバによってアクセスできるリレーショナルデータベースまたは非リレーショナルデータベースデータをサポート
 
異種データへのアクセス確立方法
  リンクサーバー → 異種データに繰り返しアクセスする場合
  OPENROWSET 関数 → 異種データに一時的にアクセスする (繰り返しアクセスする必要のない)場合
    ※ 異種データの情報をパラメータで指定
    OPENROWSET('ソース名','サーバー名';'ユーザー名';'パスワード','ステートメント')
  OPENQUERY 関数 → 異種データに繰り返しアクセスする場合
    クエリをリンクサーバー上で処理、結果をローカルの SQL Server に返す
    ※ 事前にリンクサーバーの定義が必要
    OPENQUERY(サーバー名,'ステートメント')
posted by w@ko at 17:24|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

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

データ形式の変更
データの変換およびマップ
データ整合性の保持
データ妥当性検査
作業スケジュール
異種環境間でのデータインポート/エクスポート
posted by w@ko at 17:24|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

広告


この広告は60日以上更新がないブログに表示がされております。

以下のいずれかの方法で非表示にすることが可能です。

・記事の投稿、編集をおこなう
・マイブログの【設定】 > 【広告設定】 より、「60日間更新が無い場合」 の 「広告を表示しない」にチェックを入れて保存する。


×

この広告は1年以上新しい記事の投稿がないブログに表示されております。