SQLServerあれこれ
やっぱりというかSQLServerになるんだよなぁ。
そりゃそうだよなぁ。
というわけで調べたことを乗っけとく。
いや、notebookに書いてたヤツの切り貼りなんだけど。
notebookとブログどっちに書いたか分からなくなることがしばしばあるので両方に載せとく。
自分のアカウントで横断的に検索する機能をつけくれませんか？Googleさん。
notebookとブログとgmailをまとめて検索して欲しいなぁ。
TSQL
○変数
declare @変数名 型名
○代入
set 変数名 = 値
○ループ
while 条件
biegin
処理
end
○型変換
cast
ex1.
cast(xxx as 型名)
ex2.
select cast(xxx as 型名) from ....
convertもあるけどたぶんcastでいい
see
データ型の変換 (データベース エンジン)
declare @変数名 型名
○代入
set 変数名 = 値
○ループ
while 条件
biegin
処理
end
○型変換
cast
ex1.
cast(xxx as 型名)
ex2.
select cast(xxx as 型名) from ....
convertもあるけどたぶんcastでいい
see
データ型の変換 (データベース エンジン)
クエリ
http://msdn2.microsoft.com/ja-jp/library/ms187373.aspx
SQLServer2005
with (index(0)) クラスタ化インデックススキャン
with (index(1)) クラスタ化インデックススキャンかクラスタ化インデックスシーク
SQLServer2005
with (index(0)) クラスタ化インデックススキャン
with (index(1)) クラスタ化インデックススキャンかクラスタ化インデックスシーク
・クラスタ化インデックス
例えるなら、本の１ページごとの右下にある、ページ番号のようなもの。
ゆえに、クラスタ化インデックスは１テーブルにつき１つしか作成できない。
クラスタ化インデックスの順に、１行づつデータが格納されている、と思えばいいかも。
（実際はポインタで繋がれているんだが）
・非クラスタ化インデックス
例えるなら、本の最後にある索引みたいなもの。
索引は50音で並べられており、項目ごとにページ番号が書かれてある。
索引で発見→書かれてあるページ番号にジャンプ→内容を参照。
この過程をふまないといけないので、クラスタ化インデックスより参照が遅い。
クリエアナライザの実行プランでの"BookMark"が、この作業にあたる。
テーブルスキャンよりかは断然速度は速いが、返す行が多いと"BookMark"の作業が
多くなるので十分は速度は出ない。こういう場合はWHERE句で行を絞ると良い。
この"BookMark"の作業を無くすために、複数の項目を設定した非クラスタ化インデックスを
作成すると良い。これだけで随分と速度が上がる。
あと、クリエアナライザの実行プランで"Index Scan"というものがあるが、
実は全行を索引しているので作業効率的にはテーブルスキャンと何ら変わりないという。
"Index Seek"や、"Cluster Index Seek"だと、効率よく索引できている、と思えばいいかも。
例えるなら、本の１ページごとの右下にある、ページ番号のようなもの。
ゆえに、クラスタ化インデックスは１テーブルにつき１つしか作成できない。
クラスタ化インデックスの順に、１行づつデータが格納されている、と思えばいいかも。
（実際はポインタで繋がれているんだが）
・非クラスタ化インデックス
例えるなら、本の最後にある索引みたいなもの。
索引は50音で並べられており、項目ごとにページ番号が書かれてある。
索引で発見→書かれてあるページ番号にジャンプ→内容を参照。
この過程をふまないといけないので、クラスタ化インデックスより参照が遅い。
クリエアナライザの実行プランでの"BookMark"が、この作業にあたる。
テーブルスキャンよりかは断然速度は速いが、返す行が多いと"BookMark"の作業が
多くなるので十分は速度は出ない。こういう場合はWHERE句で行を絞ると良い。
この"BookMark"の作業を無くすために、複数の項目を設定した非クラスタ化インデックスを
作成すると良い。これだけで随分と速度が上がる。
あと、クリエアナライザの実行プランで"Index Scan"というものがあるが、
実は全行を索引しているので作業効率的にはテーブルスキャンと何ら変わりないという。
"Index Seek"や、"Cluster Index Seek"だと、効率よく索引できている、と思えばいいかも。
ロックの粒度
RID
行単位（RID）をロック
キー範囲
インデックス内のキー行をロック
ページ
8Kbytesのデータページ/インデックスページをロック
エクステント
連続する8つのデータページ/インデックスページをロック
テーブル
データ/インデックスのテーブル全体をロック
データベース
データベース全体をロック
RID
行単位（RID）をロック
キー範囲
インデックス内のキー行をロック
ページ
8Kbytesのデータページ/インデックスページをロック
エクステント
連続する8つのデータページ/インデックスページをロック
テーブル
データ/インデックスのテーブル全体をロック
データベース
データベース全体をロック
ロックの確認
sys.dm_tran_locks
http://msdn2.microsoft.com/ja-jp/library/ms190345.aspx
で見れる
ex.
select * from sys.dm_tran_locks
sp_lock
http://technet.microsoft.com/ja-jp/library/ms187749.aspx
でも見れるがこれは互換性用
ex.
sp_lock
go
sys.dm_tran_locks
http://msdn2.microsoft.com/ja-jp/library/ms190345.aspx
で見れる
ex.
select * from sys.dm_tran_locks
sp_lock
http://technet.microsoft.com/ja-jp/library/ms187749.aspx
でも見れるがこれは互換性用
ex.
sp_lock
go
プロファイラでロックの確認
[SQL Server イベント クラスの参照]
http://technet.microsoft.com/ja-jp/library/ms175481.aspx
Mode列
ロックが取得された後のモード。
0 = NULL - 他のすべてのロック モードと互換性あり (LCK_M_NL)
1 = スキーマ安定度ロック (LCK_M_SCH_S)
2 = スキーマ変更ロック (LCK_M_SCH_M)
3 = 共有ロック (LCK_M_S)
4 = 更新ロック (LCK_M_U)
5 = 排他ロック (LCK_M_X)
6 = インテント共有ロック (LCK_M_IS)
7 = インテント更新ロック (LCK_M_IU)
8 = インテント排他ロック (LCK_M_IX)
9 = 更新のためのインテント付き共有 (LCK_M_SIU)
10 = インテント排他付き共有 (LCK_M_SIX)
11 = インテント排他付き更新 (LCK_M_UIX)
12 = 一括更新ロック (LCK_M_BU)
13 = 共有キー範囲/共有 (LCK_M_RS_S)
14 = 共有キー範囲/更新 (LCK_M_RS_U)
15 = キー範囲挿入/NULL (LCK_M_RI_NL)
16 = 挿入キー範囲/共有 (LCK_M_RI_S)
17 = 挿入キー範囲/更新 (LCK_M_RI_U)
18 = 挿入キー範囲/排他 (LCK_M_RI_X)
19 = 排他キー範囲/共有 (LCK_M_RX_S)
20 = 排他キー範囲/更新 (LCK_M_RX_U)
21 = 排他キー範囲/排他 (LCK_M_RX_X)
OwnerID 列
ロックを保持するオブジェクトの種類
1 = TRANSACTION
2 = CURSOR
3 = SESSION
4 = SHARED_TRANSACTION_WORKSPACE
5 = EXCLUSIVE_TRANSACTION_WORKSPACE
[SQL Server イベント クラスの参照]
http://technet.microsoft.com/ja-jp/library/ms175481.aspx
Mode列
ロックが取得された後のモード。
0 = NULL - 他のすべてのロック モードと互換性あり (LCK_M_NL)
1 = スキーマ安定度ロック (LCK_M_SCH_S)
2 = スキーマ変更ロック (LCK_M_SCH_M)
3 = 共有ロック (LCK_M_S)
4 = 更新ロック (LCK_M_U)
5 = 排他ロック (LCK_M_X)
6 = インテント共有ロック (LCK_M_IS)
7 = インテント更新ロック (LCK_M_IU)
8 = インテント排他ロック (LCK_M_IX)
9 = 更新のためのインテント付き共有 (LCK_M_SIU)
10 = インテント排他付き共有 (LCK_M_SIX)
11 = インテント排他付き更新 (LCK_M_UIX)
12 = 一括更新ロック (LCK_M_BU)
13 = 共有キー範囲/共有 (LCK_M_RS_S)
14 = 共有キー範囲/更新 (LCK_M_RS_U)
15 = キー範囲挿入/NULL (LCK_M_RI_NL)
16 = 挿入キー範囲/共有 (LCK_M_RI_S)
17 = 挿入キー範囲/更新 (LCK_M_RI_U)
18 = 挿入キー範囲/排他 (LCK_M_RI_X)
19 = 排他キー範囲/共有 (LCK_M_RX_S)
20 = 排他キー範囲/更新 (LCK_M_RX_U)
21 = 排他キー範囲/排他 (LCK_M_RX_X)
OwnerID 列
ロックを保持するオブジェクトの種類
1 = TRANSACTION
2 = CURSOR
3 = SESSION
4 = SHARED_TRANSACTION_WORKSPACE
5 = EXCLUSIVE_TRANSACTION_WORKSPACE
テーブルスキーマ取得
SELECT sys.columns.name AS 列名
, sys.columns.precision AS 有効桁数
, sys.columns.scale AS 小数点以下桁数
FROM sys.columns
LEFT OUTER JOIN sys.tables
ON sys.columns.object_id = sys.tables.object_id
WHERE sys.tables.name = N'テーブル名'
SELECT sys.columns.name AS 列名
, sys.columns.precision AS 有効桁数
, sys.columns.scale AS 小数点以下桁数
FROM sys.columns
LEFT OUTER JOIN sys.tables
ON sys.columns.object_id = sys.tables.object_id
WHERE sys.tables.name = N'テーブル名'
ロックヒント
ロック ヒント名 ヒントの説明
http://msdn2.microsoft.com/ja-jp/library/ms172398.aspx
○ロックの粒度
ROWLOCK
行ロック
selectでのデフォルトロックモードはS
PAGLOCK
ページロック
selectでのデフォルトロックモードはS
TABLOCK
テーブルロック
selectでのデフォルトロックモードはS
DBLOCK
データベースロック
selectでのデフォルトロックモードはS
○ロックモード
UPDLOCK
更新ロック（Uロック）
UPDLOCKでselectした時のデフォルトのロック粒度はROWLOCK
XLOCK
排他ロック（Xロック）
XLOCKでselectした時のデフォルトのロック粒度はROWLOCK
○継続期間
HOLDLOCK
トランザクションコミットまでロックを保持
粒度の指定がない場合はデフォルトROWLOCK
NOLOCK
ロックしない
read committed snapshot /snapshot isolation level
「READ_COMMITTED_SNAPSHOT」
Oracleの読み取り一貫性相当
select発行時点でのコミット済みデータを読み込む
READ_COMMITTED_SNAPSHOTを有効にするとREAD_COMMITTEDの意味が変わる感じ。
↑あやふや
有効にする
ALTER DATABASE データベース名
SET READ_COMMITTED_SNAPSHOT ON
「スナップショット分離レベル」
OracleのREAD_ONLY トランザクション、Serializable トランザクションとほぼ同じ
トランザクション開始時点でのコミット済みデータを読み込む
他の人にUpdateされててもトランザクション開始時点のデータを読み込む
有効にする
ALTER DATABASE データベース名
SET ALLOW_SNAPSHOT_ISOLATION ON
see
http://msdn2.microsoft.com/ja-jp/library/tcbchxcb(VS.80).aspx
スナップショット分離の使用
http://msdn2.microsoft.com/ja-jp/library/tcbchxcb(VS.80).aspx
Oracleの読み取り一貫性相当
select発行時点でのコミット済みデータを読み込む
READ_COMMITTED_SNAPSHOTを有効にするとREAD_COMMITTEDの意味が変わる感じ。
↑あやふや
有効にする
ALTER DATABASE データベース名
SET READ_COMMITTED_SNAPSHOT ON
「スナップショット分離レベル」
OracleのREAD_ONLY トランザクション、Serializable トランザクションとほぼ同じ
トランザクション開始時点でのコミット済みデータを読み込む
他の人にUpdateされててもトランザクション開始時点のデータを読み込む
有効にする
ALTER DATABASE データベース名
SET ALLOW_SNAPSHOT_ISOLATION ON
see
http://msdn2.microsoft.com/ja-jp/library/tcbchxcb(VS.80).aspx
スナップショット分離の使用
http://msdn2.microsoft.com/ja-jp/library/tcbchxcb(VS.80).aspx
SET TRANSACTION ISOLATION LEVEL
SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
http://msdn2.microsoft.com/ja-jp/library/ms173763.aspx
READ UNCOMMITTED
Sロック-ロックしない
Uロック-コミットまで保持
Xロック-コミットまで保持
READ COMMITTED
Sロック-SQL文実行後解法
Uロック-コミットまで保持
Xロック-コミットまで保持
READ COMMITTED SNAPSHOT
Sロック-ロックしない（tempdbから読み込み)
Uロック-コミットまで保持
Xロック-コミットまで保持
REPEATABLE READ
Sロック-コミットまで保持
Uロック-コミットまで保持
Xロック-コミットまで保持
SNAPSHOT
Sロック-ロックしない（tempdbから読み込み)
Uロック-コミットまで保持
Xロック-コミットまで保持
SERIALIZABLE
Sロック-コミットまで保持
Uロック-コミットまで保持
Xロック-コミットまで保持
スナップショット分離の用使
http://msdn2.microsoft.com/ja-jp/library/tcbchxcb(VS.80).aspx
http://msdn2.microsoft.com/ja-jp/library/ms173763.aspx
READ UNCOMMITTED
Sロック-ロックしない
Uロック-コミットまで保持
Xロック-コミットまで保持
READ COMMITTED
Sロック-SQL文実行後解法
Uロック-コミットまで保持
Xロック-コミットまで保持
READ COMMITTED SNAPSHOT
Sロック-ロックしない（tempdbから読み込み)
Uロック-コミットまで保持
Xロック-コミットまで保持
REPEATABLE READ
Sロック-コミットまで保持
Uロック-コミットまで保持
Xロック-コミットまで保持
SNAPSHOT
Sロック-ロックしない（tempdbから読み込み)
Uロック-コミットまで保持
Xロック-コミットまで保持
SERIALIZABLE
Sロック-コミットまで保持
Uロック-コミットまで保持
Xロック-コミットまで保持
スナップショット分離の用使
http://msdn2.microsoft.com/ja-jp/library/tcbchxcb(VS.80).aspx
チューニング
SQLServer
一番大切な資源はメモリ
運用開始後にチューニングポイントを見つけるとき
一番重要なのはバッファプール。
ディスクから取り出したデータとインデックスが入っている。
・system data structures
・lock
・log cache
・procedure cache
・buffer cashe
・users connection context
↑
これをまとめてメモリプールと呼ぶ
8kbytesが1ページ？？
SQLServerはメモリを6つに分けてる＊＊このヘンあやふや
* Sort／Hash／Index
* User Connections
* Lock Area
* Optimizer Code
* Procedure Cache
* Buffer Cache
4Gを超えるメモリ空間を使う
awe enabledを有効にする（address windowing extensions)
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure
GO
で現在の設定がでる。
awe enabledが0ならaweが無効になっている。
SQLServer2005ではManagementStudioのサーバのプロパティにAWEを利用してメモリを割り当てる、というオプションがある。
コマンドで有効にするならコレ
sp_configure 'awe enabled', 1
RECONFIGURE
GO
aweで拡張された領域のメモリを使うのは6つのうちのbuffer cacheだけ。
SQLServerのメモリプールのサイズはsp_configureのmax server memoryで確認できる。
これが4g以上に設定されていてもメモリを使用していないようならawe enabledを疑う。
sp_configureで大切なパラメータ
awe enabled
4G以上のメモリを使うなら1にする
max server memory
メモリプールのサイズ
max worker threads
ワーカースレッド数
32bitであればスレッドスタック1つに512kb、64bitでは4M消費
規定値は255
設定値を超えると待たされる
4CPU以下では規定値を推奨
5CPU以上では4CPU増加ごとに32を加えた設定値を推奨
user connection
同時に接続するユーザの最大値
規定値は0で動的にいけるだけいっちゃう
一番大切な資源はメモリ
運用開始後にチューニングポイントを見つけるとき
一番重要なのはバッファプール。
ディスクから取り出したデータとインデックスが入っている。
・system data structures
・lock
・log cache
・procedure cache
・buffer cashe
・users connection context
↑
これをまとめてメモリプールと呼ぶ
8kbytesが1ページ？？
SQLServerはメモリを6つに分けてる＊＊このヘンあやふや
* Sort／Hash／Index
* User Connections
* Lock Area
* Optimizer Code
* Procedure Cache
* Buffer Cache
4Gを超えるメモリ空間を使う
awe enabledを有効にする（address windowing extensions)
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure
GO
で現在の設定がでる。
awe enabledが0ならaweが無効になっている。
SQLServer2005ではManagementStudioのサーバのプロパティにAWEを利用してメモリを割り当てる、というオプションがある。
コマンドで有効にするならコレ
sp_configure 'awe enabled', 1
RECONFIGURE
GO
aweで拡張された領域のメモリを使うのは6つのうちのbuffer cacheだけ。
SQLServerのメモリプールのサイズはsp_configureのmax server memoryで確認できる。
これが4g以上に設定されていてもメモリを使用していないようならawe enabledを疑う。
sp_configureで大切なパラメータ
awe enabled
4G以上のメモリを使うなら1にする
max server memory
メモリプールのサイズ
max worker threads
ワーカースレッド数
32bitであればスレッドスタック1つに512kb、64bitでは4M消費
規定値は255
設定値を超えると待たされる
4CPU以下では規定値を推奨
5CPU以上では4CPU増加ごとに32を加えた設定値を推奨
user connection
同時に接続するユーザの最大値
規定値は0で動的にいけるだけいっちゃう
sysperfinfoビューの情報
select * from sysperfinfo where object_name like '%Locks%'
object_name
SQLServer:Locks
counter_name
Lock Requests/sec
Lock Timeouts/sec
Number of Deadlocks/sec
Lock Wait Time(ms)
Lock Wait Time Base
Average Wait Time(ms)
Average Wait Time Base
instance_name
Extent
Key
Page
Table
RID
Database
MSDN
http://msdn2.microsoft.com/ja-jp/library/ms190382.aspx
ボトルネックを特定するカウンタ
http://www.microsoft.com/japan/msdn/enterprise/pag/scalenetchapt17.aspx#scalenetchapt17_topic19
データアクセス ADO.NET
http://www.microsoft.com/japan/msdn/enterprise/pag/scalenetchapt15.aspx#scalenetchapt15_topic17
select * from sysperfinfo where object_name like '%Locks%'
object_name
SQLServer:Locks
counter_name
Lock Requests/sec
Lock Timeouts/sec
Number of Deadlocks/sec
Lock Wait Time(ms)
Lock Wait Time Base
Average Wait Time(ms)
Average Wait Time Base
instance_name
Extent
Key
Page
Table
RID
Database
MSDN
http://msdn2.microsoft.com/ja-jp/library/ms190382.aspx
ボトルネックを特定するカウンタ
http://www.microsoft.com/japan/msdn/enterprise/pag/scalenetchapt17.aspx#scalenetchapt17_topic19
データアクセス ADO.NET
http://www.microsoft.com/japan/msdn/enterprise/pag/scalenetchapt15.aspx#scalenetchapt15_topic17
