Technically Impossible

Lets look at the weak link in your statement. Anything "Technically Impossible" basically means we haven't figured out how yet.

トップ > IT > SQL Server備忘メモ

SQL Server備忘メモ

IT Windows


連休最終日の5月6日、朝8時から10時にかけて、旧ブログに30件超のアクセスがあった。参照先投稿は2005年のもので、MCSE (Microsoft Certified System Engineer)受験対策にまとめた投稿だ。MCSE認定までの一環として、MCDBA (Microsoft Certified DataBase Administrator)を経由した。特にMCDBA対策としてのメモを記録していた。

MCSEはWindows server 2000、2003を対象としたもので、SQL serverも、当時メイン・ストリームとして流通していたヴァージョンだった。とはいえ、基本的な部分において、現在のヴァージョンと変わらない部分もあるのだろう。まだ需要があるのであれば、ということで、こちらへ移行することにした。
もともとは３つのメモとして投稿していたのだが、移行に際し１つに統合することにした。

ネットワーク･ライブラリ

デフォルト設定はTCP/IPソケット、名前付きパイプの2つ。

マルチプロトコル

RPC、Windowsソケット、NWLink IPX/SPXを同時にサポート。

DBオブジェクト

トランザクション･ログ ファイルのサイズは、通常データ･ファイルの10-15%で作成する。
クラスタ化インデックス テーブルにある行の物理的な順序。→物理的な順序を連続した順序で並び替える。
1テーブルに1つだけ作成可能。例えるならば辞書。
非クラスタ化インデックス テーブルにある行の論理的な順序。→物理的な順序は並び替えない。
1テーブルに249まで作成可能。例えるならば索引。
クラスタ化インデックスが存在する場合、インデックス行にはクラスタ化キーを格納。
クラスタ化インデックスが存在しない場合、インデックス行には、データ行へのポインタを格納。
グローバル一時テーブル ユーザーがSQL Serverとの接続を解除した時点で削除される。

アカウント管理

ログイン作成 - Windows認証の場合

作成 SP_GRANTLOGIN
削除 SP_REVOKELOGIN
拒否 SP_DENYLOGIN

ログイン作成 - SQL Server認証の場合

作成 SP_ADDLOGIN
削除 SP_DROPLOGIN

アカウント作成

作成 SP_GRANTDBACCESS
削除 SP_REVOKEDBACCESS

サーバー･ロール管理

作成 SP_ADDSVRROLEMEMBER
削除 SP_DROPSVRROLEMEMBER

DBロール管理

作成 SP_ADDROLEMEMBER
削除 SP_DROPROLEMEMBER

パフォーマンス･チューニング

システム･テーブルとデータを分けて格納する。

ファイル･グループ内のすべてのファイルに対して均等の取れたデータ書き込みが可能。
バックアップや復元にかかる時間を短縮可能。

DBCC CHECKIDENT 指定されたテーブルの、現在のID値を確認する。
必要に応じてID値を修復する。
DBCC INDEXDEFRAG インデックスの断片化を解消する。
DBCC DBRIINDEX インデックスの再構築。
制約を強制しているインデックスを再構築できる。
DBCC SHRINKDATABASE データベース単位で圧縮。初期サイズよりも小さくすることはできない。
DBCC SHRINGFILE ファイル単位で圧縮。初期サイズ以下に圧縮可能。
SP_RECOMPILE 次回実行時にSP、トリガの再コンパイルを実施。
テーブルやヴューを指定した場合、参照するすべてのSPが対象となる。
インデックス追加、変更→統計が変化→SP、トリガの効率低下→再コンパイルによりクエリを最適化。

パフォーマンス･ガイドライン

Memory: Available Bytes 常に5MB以上を維持する。
Memory: Page/sec 常に0以下を維持する。
Process: Working Set 常に5MB以上を維持する。
System Processor Queue Length 常に2以下を維持する。

DB移行

1 SP_DETACH_DBを実行。
2 DB、トランザクション･ログをコピー。
3 SP_ATTACH_DBを実行。

データインポート

Bulk-Logged Recovery Model 最高のパフォーマンス、最小のログ使用領域。
TABLOCKオプション 一括コピー中にテーブル･レベルのロックを維持する。

リストア

1 NO_TRUNCATEオプションとともに、トランザクション･ログをバックアップ。
2 最新のフルバックアップを復元。
3 最新の差分バックアップを復元。
4 3以降のトランザクション･ログを復元。
5 最後のトランザクション･ログをSTOPATオプションとともに復元。

IIS仮想ディレクトリ経由の接続

1 IIS仮想ディレクトリ→URLクエリ許可。
2 Windows認証ログインを作成。
3 Windows統合認証を使用する。

障害復旧

TORN_PAGE_DETECTIONが有効の場合。 不完全なI/O操作を検出する。破損ページは、「復旧」時に検出される。
REPAIR_REBUILD REPARI_FASTの修復対象項目とインデックスを再構築する。

データ型

tinyint １バイトの整数データ
0～255の整数
2^8
smallint ２バイトの整数データ
±約3万の整数
-32,768～32,767
-2^16～2^16-1
int ４バイトの整数データ
±約20億の整数
-2,147,483,648～2,147,483,647
-2^32～2^32-1
bigint ８バイトの整数データ
-2^63 - 2^63 - 1
money ±約922兆の金額
-922,337,203,685,477.5808 - 922,337,203,685,477.5807
smallmoney ±約200兆の金額
-214,748.3648 - 214,748.3647
datetime 1753/01/01から9999/12/31までの日付時刻。
smalldatetime 1900/01/01から2079/06/06までの日付時刻。
char 8000文字以内の固定長文字データ
nchar 4000文字以内の固定長UNICODEデータ
varchar 8000文字以内の可変長文字データ
nvarchar 4000文字以内の可変長UNICODEデータ
text 約20億文字以内の可変長データ
ntext 約10億文字以内の可変長UNICODEデータ

主キー、一意制約、外部キー制約

主キー指定列には自動的にインデックスが作成される。 デフォルトはクラスタ化インデックス。
代替候補キー指定列には自動的にインデックスが作成される。 デフォルトは非クラスタ化インデックス。
外部キー指定列には、自動的にインデックスが作成されない。

連鎖参照性合成

1 外部キー制約のCASCADEオプションを指定。
2 主キー制約、一意制約指定列への変更、削除が外部キーへ自動的に反映される。
3 NO ACTIONオプションを指定した場合、変更に対してエラーを発生し、ロールバックする。

インデックス

カバリング･インデックス 検索に必要な全情報を格納している場合、DBアクセスの必要がなくなる。→I/Oの減少
こういったインデックスをカバリング･インデックスと呼ぶ。

DBOの作成、変更

SCHEMABINDING 参照するDBOはALTER、DROPステートメントを使用して削除できない。
ENCRIPTION テキストを暗号化する。
DBO構成を隠蔽する。

レプリケーション競合の解決

対象：マージ・レプリケーション、キュー更新可能なサブスクライバ・オプション
競合が発生する可能性がある。

回避策

パーティション計画

テーブルの水平分散 CHECK制約を使用して、パーティションの整合性を定義し、適用する必要がある。
分散パーティション・ヴュー 更新には、INSTEAD OFトリガを使用する。

トリガ

AFTER すべての実行操作が成功し、連鎖参照操作、制約チェックが成功したときにトリガを起動する。
ビューには指定できない。

トリガ動作の指定

sp_settrigger order 最初と最後時実行するトリガを指定。
OBJECTPROPERTY 現在のトリガ設定を確認。

トランザクション

SET IMPLICIT_TRANSACTIONS 暗黙のトランザクション･モードを設定する。
onの場合 commitを明示しない限り、すべての変更はユーザーの切断時にロールバックされる。
offの場合 自動コミット･モード

データ処理

SET XACT_ABORT ON 実行時エラー発生の場合、現在のトランザクションをロールバックする指定。

ヴュー

WITH CHECK OPTION ヴューの範囲外の修正時を拒否。
集計関数やグルーピングを使用したビューへの設定不可。
SCHEMABINDING 参照対象オブジェクトのスキーマに、関数をバインドする。
その関数によって参照されるオブジェクトは変更、削除できない。

ロック

ペシミスティック同時実行 他のユーザーに影響を与えるようなデータ変更を防止する。
データソースで行ロックする必要が無い。
ロック適用操作中、競合する操作を実行することができない。
オプティミスティック同時実行 レコード･ロックを実施しない。→パフォーマンスの上昇。
sp_lock ロックに関する情報をレポートする。
sp_who ユーザーとプロセスに関する情報をレポートする。

オプティミスティック同時実行制御違反テスト

１．テーブルにタイム･スタンプを含める。

  • テーブルにtimestamp列をが含まれる。
  • レコードが更新されると、timestampに更新日時が反映される。
  • 変更時に対象データのtimestampとDBのタイム･スタンプが比較される。
    • 一致した場合→更新
    • 不一致の場合→制御違反

２．元のすべての列値と、DB内の値が一致するかを確認する。

ロックのヒント

UPDLOCK 他のユーザーがデータを読むことをブロックしない。
読み取り以来のデータは変更されない。

カーソル

SCROLL_ROCK カーソルは読み取り字に、対象行をロックする。
カーソルの位置指定更新、位置指定削除の成功を保証する。
FECTH カーソルから特定行を取得する。
FETCH ABSOLUTE カーソルの先頭から指定行目に位置する行を返す。
FETCH RELATIVE 現在のカーソル行から指定行目に位置する行を返す。

XML

sp_xml_preparedocument xmlを解析し、解析済みドキュメントとして使用できる状態にする。
解析済みドキュメントは内部キャッシュに格納される。
SQL Serverが利用できるメモリ全体の1/8を使用する。
解析済みドキュメントへのハンドルを返す。
sp_xml_removedocument 解析済みドキュメントの内部キャッシュを削除する。
解析済みドキュメントへのハンドルを削除する。

Transact SQL

インライン･ユーザー定義関数 table方を帰すユーザー定義関数のサブセット。
パラメータかされたビューの機能性を高めることが可能。
COALESCE 引数の中から、最初のNULLではない式を返す。
SET SHOWPLAN_TEXT 解析時ではなく、実行時に設定。
ステートメントを実行せずに、実行情報だけを返す。
xp_sendmail メッセージにクエリ結果セットを添付して、指定された受信者へ送信する。

統計

sp_updatestats 全ユーザー定義テーブルに対して、update statisticsを実行する。
UPDATE STATISTICS 指定されたテーブルの統計情報を更新する。