MS SQL Server 2000について、 2002年頃に書いたメモ
ロックアーキティクチャ
ロックの自動エスカレーション
SQL Server 2000のロックは、動的にロック方法を自動判断して行われる。
行ロック・ページロック・DBロック
上記のチューニングとしてMS SQLでは”分離レベル”の設定 が4つある。
下に行くほどデータの整合性が上がるが、デッドロックが起こりやすくなる。
OLTPの場合、余程大勢でInsert、Updateをしない限り、デフォルトでいいんじゃないかと思う。
トランザクションを使用する場合は、明示的に 3) を選択 (後述) する場合もあるかも知れない。
但し、トランザクションは短めに。
1) READ UNCOMMIITTED
ユルユル。ダーティリード可能
2) READ COMMITTED
コミット済み読み取り (SQL Server の既定の分離レベル)
3) REPEATABLE READ
4) SERIALIZABLE
直列化 (各トランザクションが完全に分離される最高の分離レベル)
明示的なロック
ロックは明示的にもできる。ロックヒントと呼ばれるモノをSQLステートメントに挿入ればいい。
SELECT * FROM TABLE_NAME WITH (ROWLOCK) WHERE FIELD_NAME = 'hoge'
WITHのとこのロック種類はこんなにある。
- FASTFIRSTROW
- HOLDLOCK
- NOLOCK
- PAGLOCK
- READCOMMITTED
- READPAST
- READUNCOMMITTED
- REPEATABLEREAD
- ROWLOCKSERIALIZABLE
- TABLOCK
- TABLOCKX
- UPDLOCK
ロック競合
ロックが競合した時のタイムアウト設定があるらしい。
SET LOCK_TIMEOUT [timeout_period(ミリ秒単位)]デフォルト0ミリ秒
と言う事は、トランザクションが失敗した時の処理をキチンと実装しなければ(ロールバック->コネクトクローズ)、タイムアウトまで待ちっぱなしになると言う事になると言う事だろうか。
ロックの状況は、エンタープライズマネージャで確認できる。ストアドプロシージャ(sp_lock)、master.syslockinfoテーブルでも確認できる。
参考サイト
ZDNet SQL Serverのデットロックを防ぎ同時実行性を向上させよう
トランザクション分離レベルの選択とデッドロックの問題～ SQL Server 2000 における Web アプリケーション開発 ～
テーブル増時のパフォーマンスは？インデックス vs DB分割
RDBMSをスケールさせる為に、データを垂直分割したいときがある。
それに備えて、DB設計時から垂直分割するのは是か非か。
SQL Serverは1つのインスタンスに複数のデータベースが作れる。
貧乏なウチは、1つの物理サーバかつインスタンスで、複数のデータベースを作って、データを垂直分割し、アプリケーション側で何の工夫もせずに、SQLだけで検索できなくもない。
例えば、2億レコードの単一DB と、 10万レコードの200個のデータベースとで、どれ位のパフォーマンスの差がでるだろう。
一般的に、テーブルが分かれると発行するSQL文が増える為、パフォーマンスが低下するのだけど、設計とSQLの作りにもよるし、実際に検証しなければわからない。
時間があれば一度検証してみたい。
物理ディスク構成
最低でも、データベースファイルとトランザクションログファイルは物理的に分けたい。
SANにデータベースファイル、ローカルHDDにトランザクションログファイルとか。
APIはあるの
ある。BOL参照。インストール時にカスタムを選択し、開発ツールを選択すると、スクリプトのサンプルもインストールされる。
phpからデータベースサーバへの接続方法(ミドルウェア)
MS SQL Serverの接続方法には色々あるが、PHPでの使用となると、ODBC関数の使用もしくはMS SQL関数の使用の2つが用意されている。
下記2つのパフォーマンスの善し悪しは、比較検証して見ないと分からない。
1)MS SQL関数
MS SQL関数は、MS SQLクライアントツール”ntwdblib.dll”を必要とする。
このモジュールは、DB-Libraryと呼ばれる呼び出しインターフェースで、MS SQL Server6.5の頃の古いAPIである。下位互換の為に用意されているようで、MS SQL Server2000で用意された新機能を使う事が出来ないらしい。Unicodeにも非対応である(非対応の意味が良く分からないが)。
2)ODBC関数
ODBC3.7以上では、Unicodeをサポートするようだ。(要検証)
正確には、MDAC 2.1以上 または SQL Server ODBC ドライバ (バージョン 3.70.0623 以降) または OLEDB プロバイダ (バージョン 7.01.0623 以降(MS KB JP234748)。
MS SQL Serverをインストールすると、MDAC2.6がインストールされるようだ。