とまと あんらいぷ…

エンジニアの活動記録とかつぶやきとか

GitHub

カテゴリ:SQL server の記事一覧

スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

デッドロックの考え方と調査方法指針をまとめてみた

【デッドロック関係の調査の仕方】
SQL Server Management Studio(以下SSMS)の
管理>拡張イベント>セッション>system_health>package0.event_fileを開いて
[name]がxml_deadlock_reportとなっているものがデッドロックの情報。
ここを見て、だいたいのあたりを付ける。

また、以下のコマンドで、ログに出力することができる。実行されているSQLをコピペしたいときは有効にするとよい。
-- SQLServerログにデッドロックのログを出力する
DBCC TRACEON(1204,-1)
-- エラーログにデッドロックのログを出力する
DBCC TRACEON(3605,-1)
-- トレースフラグの設定確認
DBCC TRACESTATUS
SQL Serverログを有効にすると、管理>SQL Serverログ>現在 に、クエリが出力されるようになる。

【調査コマンド・ツール・SQLなど】
以下に、SSMSで叩くコマンドやツールを記載する

■コマンド
sp_who2 =>セッション、およびプロセスに関する情報を提供する。要するにブロッキングを見ることができる。
print @@trancount =>DBのトランザクション数を表示する

■ツール
利用状況モニタ => データベースを右クリック「利用状況モニタ」で起動する。SQLの利用状況が一覧表示される
SQL Server プロファイラ => SSMSのツール>SQL Server プロファイラ より起動 ログインユーザーでフィルタリングして、実行されているSQLを確認するなどに使う。デッドロック検出に関してはDeadlock Graph イベントを使う
  SS2014を使っていればこのイベントはsystem_healthに出力される。
  デッドロックの検出と終了
  https://technet.microsoft.com/ja-jp/library/ms178104(v=sql.105).aspx
設定方法(わかりやすい)
  http://d.hatena.ne.jp/seki-moto/20101117/DeadLock
グラフィカル表示されたサーバー プロセスIDは、「select * from sys.sysprocesses 」のhostname を検索して調べる
    
■SQL
以下のSQLはロック状況を出力する(sys.dm_tran_locksに情報が入ってる)
-----------------------------------------------------------------------
SELECT
resource_type AS type
,resource_associated_entity_id as entity_id
,( CASE WHEN resource_type = 'OBJECT' THEN
OBJECT_NAME( resource_associated_entity_id )
ELSE
( SELECT
OBJECT_NAME( OBJECT_ID )
FROM
sys.partitions
WHERE
hobt_id=resource_associated_entity_id )
END)
AS object_name
,request_mode AS request_mode
,request_type AS request_type
,request_status AS request_status
,request_session_id AS session_id
FROM
sys.dm_tran_locks
WHERE
resource_type <> 'DATABASE'
ORDER BY
request_session_id
-----------------------------------------------------------------------

【トランザクション・デッドロックに係る知識】
この辺りがざっくりまとまって良い
https://blogs.msdn.microsoft.com/jpsql/2012/06/27/609/

変換デッドロック防止のため、DBは、READ_COMMITTED_SNAPSHOT 設定をONにしている
http://higus70.seesaa.net/article/145227121.html

デッドロックが発生した場合、第三者の介入がなければそれを解消することはできない。
SQL Serverでは、Lock Managerというリソースがデッドロックの検出を行い、強制的に片方の要求をロールバックさせる。
デッドロック発生の検出を決める既定時間は5秒。
スポンサーサイト

テーマ:データベース - ジャンル:コンピュータ

DROP TABLE のお作法

いつもDROP TABLE する時にテーブルが存在してエラーとなってしまうので
テンプレを貼っておきます。(更新対策・・・ではないよ 多分)

IF OBJECT_ID('dbo.TEST_DB') IS NOT NULL BEGIN
DROP TABLE TEST_DB
END

ほい。

基本情報処理試験のためのデータベース概念習得

基本情報処理の試験を受けて
SQLに関しては常に100点だったので
私の持っているデータベースへの理解について
とてもファンキーに乱暴にお伝えします。
本職のくせに実務の点数が悪いのはスルーしてくださいね。

基本情報処理試験結果

固定観念を捨て去る


このブログには「SQL 勉強」とかいうキーワードで
飛び込んでくる人が多いようです。
当然のごとくデータベースの概念がふわふわしてる方が多いでしょう。

SQL、クエリの学習方法でおすすめのサイト紹介(SQL攻略 - 実行すれば理解できる!) でも書いてますが
一般的なプログラム言語とはやや毛色が違うため
なかなかとっつきにくいものだと思います。

通常のプログラミングは
命令1行につき、「1処理」を行うように記述するのに対して
データベースの操作は「集合」を操作するように実行します。
(後述のイメージ参照)

文化が違うために、通常のプログラミングを勉強し始めた方は
既に「プログラムとはこういうもの」という固定観念ができあがっているため
混乱するのだと思います。

例えば・・・
データ集合の一部を変更するようなプログラムをイメージしてみましょう。
データの集合として、10.20,30,40,50 というひとくくりのデータのまとまりがあるとします。

一般的なプログラム言語では、複数のデータを処理する場合
プログラムコードで、1行ずつ命令します。

---プログラムコード---
// 10~50の配列
int[] arr = { 10, 20, 30, 40, 50};

// 20と40を0に更新する
arr[1] = 0;
arr[3] = 0;

//結果 arr { 10, 0, 30, 0, 50};
----------------------
イメージ
一般的なプログラミング言語
「データ」を1行ずつ処理する。
20140501_01_database.png

対してデータベースの操作は「ひとまとめ」で処理します。

20140501_02_database.png

この概念の違いを理解しないと、固定観念によって混乱してしまいます。


データベースのイメージを掴もう


データベースといっても、今はxmlデータベースとかリレーショナル・データベースとか
種類はあります。
今回は基本情報処理で出題される
リレーショナルデータベースシステム(RMBS)-関係データベースの概念をまとめてしまいます。

ぶっちゃけた話、データベースはExcel(エクセル)です。
本当は違いますけど、基本情報処理試験に合格するためや、
仕事を始める前のとっかかりとしては
Excelだと思ってよいです。うん。間違いない。

だってさウィキペディアにも
データは表に似た構造で管理され~

って書いてるもん。
表でしょ? Excelなんですよ。
20140501_03_database.png

基本情報処理の勉強をしていると
「選択」「射影」「結合」とかよくわからない言葉がでてきます。
もうね、いちいち小難しい漢字を当てはめちゃった感が出まくりなんですよ。

こんな言葉は試験が終わったら使いません。(使ったこと無い)
ですが、試験で出てくるので仕方なく覚えるしか無いので
覚えるしか無いですね・・・

というわけで、Excelに例えましょう。

・選択:行の選択
SELECT [*](全部の意味) FROM [表の名前]
20140501_04_database.png

・射影:列の選択
列の選択⇒SELECT [列の名前] FROM [表の名前]
20140501_05_database.png

ね、簡単でしょ。

・結合:
結合については、Excelで、別の表をコピペしたりするでしょ?それです。
他の表と、元の表をガッチャンコ。
それが、結合です。何の捻りもありませんね。

結合はコピペ
INNER JOIN
20140501_06_database.png

それぞれデータベース用語に置き換えると

・選択:行の選択⇒SELECT [*](全部の意味) FROM [表の名前]
・射影:列の選択⇒SELECT [列の名前] FROM [表の名前]
・結合:INNER JOIN だったり LEFT JOIN だったり・・・基本情報処理ではLEFTかINNERを覚えておけばOK!

です。
はい、これだけ覚えたら基本情報処理の
データベースに関連する免疫がつきます。

あとは小手先の技、、というか出題が圧倒的に多い
GROUP BY とやらを覚えたら6割ぐらいは取れるんじゃないですかね。多分。
続きを読む

テーマ:プログラミング - ジャンル:コンピュータ

ストアド プロシージャの自動実行と解除方法

SQL Server でストアドの自動化と解除


自作のストアドを、SQL Server サービスの起動と同時に実行する手順
及び、解除方法をを記します。

SQL ServerはWindowsのログオンと同時にサービスの起動が行われるため
データベースの起動に合わせてストアドを実行させるには
データベース起動トリガに自作のストアドをセットしてやれば良い。

利用する関数は
sp_procoption (Transact-SQL)

使い方は以下のとおり
--ストアド プロシージャ自動実行の設定

exec sp_procoption [USER_STORED_PROCEDURE], startup, true;
で、データベース開始時に実行するストアドをセット
[USER_STORED_PROCEDURE]には、実行したいストアド名を指定します。

即時実行したい場合は
exec [USER_STORED_PROCEDURE];
も実行。

念の為に有効になっているかを確認します。
select * from ::fn_trace_getinfo(default)
まっさらな状態だとID2が増えているはず。

ID1はSQLのデフォルトトレースなので気にしなくてOKです。

ストアドの自動起動を解除する時は
exec sp_procoption [USER_STORED_PROCEDURE], startup, false;
で解除。

何でもかんでもアプリケーション実装側のプログラミングで対処するのではなくて、データベースのトリガも念頭においた設計をすることで、開発効率アップや保守性にすぐれたシステムを実装できるかもしれませんね。

気をつけることは、アーキテクチャの機能を手探りで進めすぎると、どこに何を設定したんだか分からなくなるので軌跡は残しましょう。

テーマ:プログラミング - ジャンル:コンピュータ

【SQL Server】ユーザー <user> はログインできませんでした。 理由: 明示的に指定されたデータベースを開けませんでした。

遅いだけじゃなかった


以前 【SQL Server】Expressが遅い理由【アタッチで設定が変わる】 なんて記事を書いたけど、
遅いだけじゃなかった。

どうやらSQL Serverが自動終了するタイミングによっては、ログオンできないというエラーが発生するようです。

自動終了オプション(AUTO_CLOSE)がONの時、SQL Server内部では以下のシーケンスで
DBのクローズ処理が行われます。

1.DBのアクセスが無くなる
2.自動終了オプションにより、DBのクローズ処理が発生
3.DBのクローズ完了

この、2のタイミングの時に再びデータベースへのログオンを試みると

エラー 18456 「ユーザー 'XXX' はログインできませんでした。 理由: 明示的に指定されたデータベースを開けませんでした。」

が発生します。

Microsoft SQL Server Japan Support Team Blogによると


AUTO_CLOSE オプションが有効になっているデータベースは、通常はクローズされた状態になっています。誰かがそのデータベースにアクセスしようとすると、その時点でデータベースはオープンされます。誰もアクセスしていない状態になると、データベースは自動的にクローズされますが、クローズ処理中はデータベースにはアクセスできません。そのため、あるユーザーがデータベースへのアクセスを終えてデータベースのクローズ処理が実行されている最中に、別のユーザーがそのデータベースを指定してログインしようとすると、その「別のユーザー」は、エラー 18456 「ユーザー 'XXX' はログインできませんでした。 理由: 明示的に指定されたデータベースを開けませんでした。」となり、ログインに失敗します。


とのこと・・・


しかもエラーログにはこのエラーメッセージそのままで表示されるため
理由を知らないとSQL実行ログをトレースして追跡調査を行うハメになります。

回避するには自動終了オプション(AUTO_CLOSE) オプションを OFFにすること。

テーマ:プログラミング - ジャンル:コンピュータ

次のページ

FC2Ad

上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。