2005年09月17日

ブロッキングロック

1つのプロセスが長時間にわたりロックを保持するために発生
手動で解除する必要あり → アプリケーションにロックタイムアウト設定を組み込む
posted by w@ko at 17:24|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

デッドロックの回避策

同じ順序でオブジェクトへアクセスさせる
トランザクションのステップを最小化、トランザクションを短縮
低い分離レベルの使用
ロック検出およびデッドロック宣言されるまでの待ち時間を設定
  sp_configure 'query wait'
posted by w@ko at 17:24|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

ロックのモード

共有ロック (S)
 
排他ロック (X)
 
更新ロック (U)
  デッドロック防止
 
インテントロック
  ロック階層の設定
   
インテント共有 (IS) 下位の一部リソースに共有ロックを設定、そのリソースを読み取るトランザクションが存在
インテント排他 (IX) 下位の一部リソースに排他ロックを設定、そのリソースを変更するトランザクションが存在
インテント排他の共有 (SIX) リソースの同時読み取りを許可、下位の一部リソースにインテント排他ロックを設定、トランザクションがそのリソースを変更する予定がある
 
スキーマロック
  テーブルのスキーマに依存する処理実行時
   
スキーマ安定度ロック (Sch-S) テーブル、インデックスの削除を禁止
スキーマ修正ロック (Sch-M) 他のトランザクションからのアクセスを完全に禁止
 
一括更新ロック (BU)
  データの一括コピー実行時
  TABLOCK ヒント指定時
 
ロックの互換性
【参照】 SQL Server 2000 Books Online 「ロックの互換性」
posted by w@ko at 17:24|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

ロック動作の制御

トランザクション分離レベル → セッションレベル
  SET TRANSACTION ISOLATION LEVEL 分離レベル名
    → レベル名はスペースあり
 
ロックヒント → テーブルレベル
  SELECT ... WITH (テーブルレベル名
    → レベル名はスペースなし
 
READUNCOMMITTED
NOLOCK
READ UNCOMMITTED
READCOMMITTED READ COMMITTED
REPEATABLEREAD REPEATABLE READ
SERIALIZABLE
HOLDLOCK
SERIALIZABLE
ROWLOCK 行ロック
PAGLOCK ページロック
TABLOCK テーブルロック
TABLOCKX テーブル排他ロック
READPAST ロック行をスキップ
READ COMMITTED レベル + 行ロック + SELECT ステートメント
UPDLOCK テーブル読み出しの間、共有ロックの代わりに更新ロックを使用
XLOCK 排他ロック (ページロックまたはテーブルロック → 自動選択)
  セッションレベルの制御に優先
 
なるべくセッションレベルよりもテーブルレベルでロック制御を行うようにする
 
ロックのタイムアウト
 

 
行ロックをかけた場合でも、テーブルスキャンを行うような更新ロックを使った SELECT ステートメントを実行するとロック待ちが発生する
    ← インデックスがロックされるため
  index (インデックス名) オプションをつけることで目的の条件部分のみにロックをかける
posted by w@ko at 17:24|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

ロックの概要

同時実行性を高める → ロックの使用
  ロックの粒度
  ロックの種類(排他/更新/共有)
 
 
分離レベル
(READ UNCOMMITTED/READ COMMITTED/REPEATABLE READ/SERIALIZABLE)
  データ領域管理の仕組み
  デッドロックの発生に対する対処
posted by w@ko at 17:24|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

パフォーマンスの低いクエリの監視

クエリパフォーマンスの識別 → SQL プロファイラ
  [TSQL]¥[SQL:BatchCompleted]
  [ストアドプロシージャ]¥[RPC:Completed]
 
クエリパフォーマンスが低い原因
  低速なネットワーク通信
  クライアントとサーバー間で大量データ転送をしている Transact-SQL クエリ
  メモリ不足
  有用な統計情報の欠如
  古い統計情報または有用なインデックスの欠如
  有用なデータストライピングの欠如
  ブロッキングロックまたはデッドロック
  同一コンピュータ上でトランザクション処理アプリケーションおよび意思決定支援が実行
posted by w@ko at 17:23|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

パフォーマンス監視とチューニング

○ パフォーマンスチューニングのストラテジ
応答時間の最適化
  特定クエリ、アプリケーションのパフォーマンスを向上
  アプリケーション、環境、およびユーザーに関する知識が必要
スループットの最適化
  システム構成、トランザクション設計、クエリの作成
  SQL Server がデータアクセス、同時実行、および OS との対話を行う方法に関する知識が必要

○ パフォーマンスチューニング方法の選択
アプリケーションの応答時間およびサーバーのスループットを最適化する方法
  クライアントアプリケーションのチューニング
    検索を制限するクエリを記述
    有用なインデックスの作成
    ロックの競合を最低限にし、デッドロックを避ける
    競合を減らし、同時実行を増やすストアドプロシージャを使用
    必要に応じてサーバーからデータおよび処理をオフロード
  データベースのチューニング
    論理設計/物理設計の改良
  SQL Server のチューニング
    ストレージデザインを評価、設定オプションの調整
  ハードウェア構成のチューニング
    メモリ、プロセッサ、ディスク、ネットワーク等

○ パフォーマンスチューニング方法の開発
パフォーマンスのための設計
  アプリケーション設計の段階でパフォーマンスを考慮
  ユーザー要件の確認
  データ内容の確認
  データベースの適切設計
    … 正規化/非正規化の使用
    … リレーショナルスキーマ、スタースキーマ、スノーフレークスキーマの設計
  ストアドプロシージャの作成、テスト
  インデックスストラテジの設計
  メンテナンスおよび監視の実施のスケジュール化
パフォーマンスの計画
  テスト環境からベースラインを決定
  リソース、負荷、パフォーマンスに対するサーバー操作のパラメータ定義
  スループットおよび応答時間の目標
  全アクションの文書化、結果測定
  シミュレートされた実運用環境のベンチマーク
  データベースのトランザクション分析
  パフォーマンス問題の識別
  パフォーマンスベースラインの決定

○ パフォーマンスベースラインの決定
データベースのパフォーマンスに影響を与える要因
 
作業負荷 サーバーアクティビティ量
スループット 一定時間内のクエリ総数
システムリソース ハードウェアの物理容量
最適化 アプリケーションおよびデータベースの設計
競合 データレコードの競合
ベースライン決定のための材料
  データベースアクティビティのピーク時間/オフピーク時間
  クエリおよびバッチの応答時間
  バックアップおよび復旧に要する時間

○ パフォーマンスボトルネックの検出
調査内容の決定
  メモリ、CPU、ディスク I/O、ユーザー接続、ロックの監視
許容範囲の認識
  ベースラインより極度に高い/低い値 → ボトルネック
  システム監視時に SQL Server 上で作業負荷シミュレーションを行うことで実際の限界を検出
posted by w@ko at 17:23|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

SQL Server 7.0 以降で廃止された DBCC ステートメント

MEMUSAGE → メモリ使用量に関する詳しいレポートを表示
SQLPERF の LOGSPACE 以外
posted by w@ko at 14:59|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

SQL プロファイラ

SQL プロファイラで監視できるイベント
  パフォーマンスの低いクエリ
  テーブルスキャンを実行するクエリ
  個々のユーザーおよびアプリケーションのアクティビティ
  tempdb データベースのパフォーマンス
  デッドロック問題
  ログインの試行/失敗/接続/切断
  論理ディスクの読み取り/書き込み
  ステートメントレベルでの CPU の使用
 
システムストアドプロシージャによるトレース条件のフィルタリングが可
 
sp_trace_create トレースするサーバー上のイベントを指定
trace_produce_blackbox 特定サーバーイベントの最後の 5MB のレコードを作成
posted by w@ko at 14:58|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

実行プラン

クエリを実行する代わりにステートメント実行方法の詳細情報を表示
SQL Server 6.5以前では SET SHOWPLAN ON
グラフィカル実行プラン (SQL Server 7.0以降) ⇒ クエリアナライザから起動
SET SHOWPLAN_ALL ON
posted by w@ko at 14:58|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

Transact-SQL 監視ツール

システムストアドプロシージャ
 
グローバル変数
 
Transact-SQL ステートメント
 
SET STATISTICS IO {ON|OFF} ステートメントが生成するディスク操作量に関する情報
SET STATISTICS TIME {ON|OFF} 各ステートメントの解析、コンパイル、実行に必要な時間
SET STATISTICS PROFILE {ON|OFF} クエリの実行プロファイル情報
SET SHOWPLAN_TEXT {ON|OFF} 実行プランの表示
 
DBCC ステートメント
 
DBCC SQLPERF (情報の種類) サーバーが最後に起動してからの統計情報
  (LOGSPACE) データベースのトランザクションログ領域の利用度
(IOSTATS) ディスク I/O
(LRUSTATS) メモリとキャッシュの利用度
(NETSTATS) ネットワークアクティビティ
DBCC OPENTRAN ('データベース名') 特定のデータベース内に存在する最古のアクティブトランザクションおよび最古の分散型/非分散型のレプリケートトランザクション情報
DBCC SHOW_STATISTICS (テーブル名, オブジェクト名) インデックスがクエリオプティマイザで有効かどうかを判断するための基準を示すインデックス選択情報 (分布情報)
DBCC CHECKDB ('データベース名') データベース内の全オブジェクトの割り当てと構造一貫性情報
DBCC CHECKFILEGROUP [({'ファイルグループ名'|ファイルグループID})] ファイルグループ内の全テーブルの割り当てと構造一貫性情報
DBCC CHECKTABLE ('テーブル名') 指定したテーブルのデータ、インデックス、text、ntext、image ページの一貫性情報
 
トレースフラグ
  特定のサーバー特性を設定
  DBCC TRACEONステートメントで設定、DBCC TRACEOFF ステートメントで無効化
posted by w@ko at 14:56|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

Enterprise Manager [現在の利用状況]ウィンドウ

SQL Server プロセス情報
  現在開かれている接続に関する情報
  ユーザーが最後に実行したステートメントを表示
  ロックに関する情報をプロセス ID ごとまたはオブジェクトごとに表示
 
ロック、ブロック、デッドロック
 
ロックおよびプロセスの管理
 
 
システム管理者は選択したプロセスに関する補足情報の参照、ユーザーへのメッセージ送信、選択プロセスの終了が可能
posted by w@ko at 14:56|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

プロファイラテンプレート

SQLProfilerTSQL_Reply  
SQLProfilerTSQL_Duration 実行コストの確認
posted by w@ko at 14:55|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

SQL Server ファイバモード

1スレッド内で複数のファイバを使用
OS のファイバをファイバプールからユーザータスクに割り当て
  ← 通常は OS のスレッドをスレッドプールから割り当て
OS にファイバの切り替えを許可せず、すべて SQL Server が切り替えを行う
OS が消費するリソースは減る
環境設定オプションで lightweight pooling の値を 1 に設定することで有効化
posted by w@ko at 14:54|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

パフォーマンスモニタ監視項目

○ メモリ/ページングファイルの使用状況
オブジェクト カウンタ 説明 ガイドライン
Memory Available Bytes プロセスの実行に使用できるバイト数 常に 5000KB より大
小さい値 → RAM 不足
Pages/sec RAM /ディスク間読み取り/書き込みページ数 0以下
大きい値 → ページングファイル使用
Commit Limit 物理/仮想メモリを合わせた利用可能メモリ最大容量  
Commited Bytes メモリ使用量 物理メモリより大きい値 → ページングが頻繁に発生
Process Page Faults/sec メモリ上の指定されたワーキングセット内でのページフォルト 大きい値(20 以上) → ページングが過度
Working Set SQL Server が使用するメモリ使用量 5000KB より小
小さい値 → SQL Server で使用できる追加メモリが少ない
SQLServer:Buffer Manager Buffer Cache Hit Ratio ディスクから読み取らずにバッファキャッシュ内で見つかったページの割合 90% より大
Total Pages バッファキャッシュ内での総ページ数 小さい値 → ディスク I/O が頻繁に発生
Databases pages データバッファキャッシュに使用しているページ数  
Procedure cache pages プロシージャキャッシュに使用しているページ数  
SQLServer:Memory Manager Total Server Memory サーバーが使用している動的メモリ総量 一貫して物理メモリ値に近い → メモリ増設が必要
Connection Memory ユーザー接続用に使用しているメモリ量  
Lock Memory ロックに使用しているメモリ量  
SQLServer:Cache Manager Cache Hit Ratio キャッシュヒットとキャッシュルックアップの比率  
 
○ スレッド/プロセッサの使用状況
オブジェクト カウンタ 説明 ガイドライン
Processor %Processor Time CPU 使用率 継続して 90% 以上 → CPU のアップグレード/追加が必要
%Privileged Time カーネルコマンド実行のための特権時間に費やす時間の割合 できるだけ小さい値
大きい値 → ディスク I/O サブシステムのパフォーマンスを上げる必要
%User Time プロセッサがユーザープロセス実行に費やす時間の割合 他プロセス/アプリケーションが SQL Server の動作を妨げている可能性
System %Total Processor Time 全プロセッサの平均  
Processor Queue Length プロセッサキューにあるスレッド数 2 以下
2 より大きい値 → CPU のアップグレード/追加が必要
Context Switches/sec プロセッサがスレッドを切り替える1秒あたりの回数 8000 に達している → CPU のアップグレード/追加が必要
Process %Processor Time サービスごとの CPU 使用率  
 
○ ハードディスク I/O
オブジェクト カウンタ 説明 ガイドライン
PhysicalDisk %Disk Time ディスク I/O 処理時間の割合 90% より小
Avg. Disk Queue Length ディスク I/O 待ちキュー長の平均 2 より大きい値 → ディスク I/O 処理要求で待ち発生
Disk Reads/sec 読み取り操作比率 一貫して小
Disk Write/sec 書込み操作比率 一貫して小
 
○ 接続数/トランザクション数
オブジェクト カウンタ 説明 ガイドライン
SQLServer:General Statistics User Connection 接続中ログインユーザー数  
Logins/sec 1秒あたりのログインユーザー数  
SQLServer:Databases Active Transaction 実行中のトランザクション数  
Transaction/sec 1秒あたりの実行トランザクション数  
 
○ ロック
オブジェクト カウンタ 説明 ガイドライン
SQLServer:Locks Lock Waits/sec ロック平均待ち時間  
Lock Timeout/sec 1秒あたりのロックタイムアウト数  
Number of Deadlock/sec 1秒あたりのデッドロック数  
SQLServer:Access Methods Table Lock Escalations/sec 1秒あたりのロックエスカレーション数  
 
○ ページ分割状況
オブジェクト カウンタ 説明 ガイドライン
SQLServer:Access Methods Pages Splits/sec 1秒あたりのページ分割数  
posted by w@ko at 14:54|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

パフォーマンスモニタにおける監視対象カウンタ

・ハードウェア
Processor %Processor Time CPU 利用状況
Process %Processor Time -sqlservr CPU 利用状況
Memory Pages/sec メモリ
Physical Disk %Disk Time ディスク I/O
Avg. Disk Queue Length ディスク I/O
 
・SQL Server 関連 → 自動設定項目
Buffer Manager Buffer Cache Hit Radio バッファキャッシュ利用率
Cache Manager Cache Hit Ratio  
Access Methods Page Splits/sec ページ分割
Locks Number of Dead Locks/sec デッドロック
Lock Waits/sec ロック待ち
General Statistics User Connections  
Memory Manager Toral Server Memory  
Databases Transactions/sec  
posted by w@ko at 14:53|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

Windows パフォーマンスモニタ

SQL Server が追加するカウンタ
  SQL Server の I/O
  SQL Server のメモリ使用状況
  SQL Server のユーザー接続
  SQL Server のロック
  レプリケーションアクティビティ
 
カスタムカウンタを最大 10 個作成可
posted by w@ko at 14:52|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

2005年09月16日

セキュリティ監査

セキュリティログ
 
監査レベルの設定
  ⇒ Enterprise Manager → サーバーのプロパティ → [セキュリティ]タブ
posted by w@ko at 19:26|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

パフォーマンス監視タスク

システムレベルの監視
  ハードウェア、OS、アプリケーションのパフォーマンス評価
  Windows イベントビューア
  Windows パフォーマンスモニタ
 
SQL Server 固有の監視
  SQL Server のアクティビティ、データの一貫性 … ロック、競合、ユーザー接続数
  Enterprise Manager → [管理] → [現在の利用状況]
  Transact-SQL ステートメント、システムストアドプロシージャ
  SQL プロファイラ(SQL Server 7.0 以降)
 
特定クエリのパフォーマンス
  インデックスの使用状況、クエリの CPU 使用時間、I/O
  SQL プロファイラ
  クエリアナライザ
  インデックスチューニングウィザード
  実行プラン
 
パフォーマンス監視ツールの使用によりシステムリソースを消費 → オーバーヘッドを覚悟
posted by w@ko at 19:25|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

master データベース再構築後のユーザーデータベース認識処理

master データベースを有効なバックアップから復元した場合は操作の必要なし
 
master データベース再構築後に有効なバックアップを適用できない場合、次のいずれかの操作を行う
  バックアップからユーザーデータベースを復元
  既存のユーザーデータベースファイルを新しい master データベースにアタッチ
    → アタッチによりユーザーデータベース情報が master データベースに提供
    ※ ユーザーデータベースのアタッチは復元よりも効率的
posted by w@ko at 19:24|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

広告


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

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

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


×

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