SQL Serverでupdlock,rowlockヒント付きの同じselect文同士で、デッドロックが発生した。 デッドロックエラー時の実行プランをみたところ、4列からなる主キーのうち、シークキーとしているのが1列のみ。 理由は、select文のwhere句に指定があるのは、主キーの第1,3,4番目の列で、主キーの第2番目の列を指定していないため。 別に用意されていた(where句の検索条件で十分絞り込まれる)2次インデックスは使われていなかった。 対応として、(たとえSQL Serverが計算したコストが高くなっても)2次インデックス使うようにするヒントを追加する対応をする。 で、効果確認で、実行後にプランハンドルを検索し実行プランを抽出しようとしたが、 検索に手間取りプランハンドル特定時には実行プランが流れて(消えて)しまっていた。 なので、SQL Profilerを仕込んで、SQLを実行することにしようとしたが、netを見ると、profilerの使用は非推奨となっており、 拡張イベントを使う方法が推奨とのこと。 というわけで、SQL Profilerと拡張イベントでの実行プランの採取方法の備忘録。 まずは、profilerの方法 Microsoft SQL Server Tools→SQL Server Profiler を起動し、新しいトレースを押し、サーバへの接続ダイアログで、該当サーバに接続し、 トレースのプロパティダイアログで、[全般]タブでは、使用するテンプレートでStandard(default)のまま、 [イベントの選択]タブで、すべてのイベントを表示するにチェックボックスをONにした後、Performance→Showplan XML Statistics Profileの行にチェックを入れて実行ボタンを押下。調査するSQL文の実行が終わった後に、赤い四角アイコン(選択したトレースの停止)を押して、トレース採取完了。 EventClassがShowplan XML Statistics Profileの行を選択すると、SQLPlanが図示される。 次に拡張イベントの方法 SQL Server Management Studioでサーバへの接続ダイアログで、該当サーバに接続し、 オブジェクトエクスプローラーウィンドウでSQL Server→管理→拡張イベント→セッションを右クリックし新しいセッションウィザードを選択。ウィザード(ステップ式ダイアログ入力)で [セッションのプロパティの設定]ステップで、セッション名を指定し、[テンプレートの選択]ステップで、テンプレートにStandardを選択し、[キャプチャするイベントの選択]ステップで、query_post_execution_showplanイベントを追加し、[グローバルフィールドのキャプチャ]ステップと、 [セッションイベントフィルターの設定]ステップはデフォルトのまま [セッションデータストレージの指定]ステップで、SQLサーバー上のファイル名を指定して(SQL Serverへのログインユーザの次第では書き込み権限がないかも。)完了ボタンを押下。成功のダイアログを閉じる。オブジェクトエクスプローラーウィンドウでSQL Server→管理→拡張イベント→セッションを展開すると、ウィザードステップ1でしていしたセッション名が赤四角(停止中)で表示される。右クリック→[セッションの開始]すると、赤い四角から、緑の右向き三角となり採取中となる。最後に、今回の対応での実行プランの見方をpubsデータベースsalesテーブルを使って解説。 salesテーブルは、主キーが、stor_id,ord_num,title_idとなっているクラスター化インデックスキーとなっている。 salesテーブルにstor_id,title_idを持つインデックスixを作ってみる。SQL Server Management Studioで[実際の実行プランを含める(Ctrl + M)]をした後、 select * from sales where stor_id='6380' and title_id='BU1032' を実行し、実行プランを見ると、SELECTはクラスター化インデックスシークを行っていることがわかる。クラスター化インデックスシークをポイントすると、シークキーにstor_idのみ使われていることがわかり、SELECTをポイントすると、サブツリーの推定コストが、0.0032842とわかる。次に、select * from sales with(ixndex(ix)) where stor_id='6380' and title_id='BU1032' を実行し、実行プランを見ると、SELECTは[sales].[ix]のインデックスシークと、[sales].UPKCL_salesのキー参照をNested Loops(入れ子ループ)でインナージョインしていることがわかる。インデックスシークでは、シークキーに、stor_idと、title_idが使われており、SELECTでは、サブツリーの推定コストが、0.0065704とわかる。つまり、with(index(ix))のヒントにより、SQL Serverは、推定コストが0.0032842ではなく、それより大きい0.0065704の実行プランを採用していることがわかる。 これで、デッドロックの解消ができればいいんだけれど。
2025年11月24日月曜日
SQL Server Profilerの使用は非推奨？
2025年10月11日土曜日
ローマ字入力からカナ入力への転向
これから生成AIの時代、なんとしても入力速度を上げたくて30年つづけていたローマ字入力からかな入力に転向中。 転向から1年半が経過したが、まだローマ字入力だった時よりも入力が遅い。 ただ、もう無意識の運指がかな入力になっているので、もうローマ字入力には戻れない状態となっている。 転向してみてわかった以下のデメリットがある。もしローマ字入力からカナ入力にしたいと思ったら以下のことを受け入れる覚悟がいることを知ってほしい。 (こんな苦労があるとわかっていたら私は転向はしなかったと思う。) ・過渡期中は、入力スピードが極端に遅くなり、ローマ字入力に戻れないため、ストレスががかる。 ・人のPCやリモートデスクトップしたPCでの日本語入力は壊滅的。(他人のPCをカナ入力にするわけにもいかないし。) ・キー入力に迷いやミスが起こる。(ローマ字入力のキー入力の種類は25個なのに対して、カナ入力の1文字はシフトキーインを別々に考えると62個ある) ・全角の記号や数字を嫌うようになる(数字や記号入力のために入力をカナからローマ字の切り替えと戻しをする必要がある) という愚痴は置いておいて、Windowsのカナ入力設定 1.カナ入力設定 2.キーによるカナ入力とローマ字入力の切り替え ローマ字入力がしたいわけではなく、全角数字(１など)や全角記号(？や①や＠や：など)を入力するため 3.以前のバージョンのMicrosoft IMEを使う Excelマクロする場合に設定。ExcelVBA開発環境で標準のIMEを使うと日本語入力中にShiftを押すと無変換で確定してしまうため 「ぁぃぅぇぉっゃゅょを、。・」を入力するときに支障があるため 1.2.3.ともに同じ設定画面で以下を設定。記号の入力が半角なら[半角/全角]キーか[capslock]でIMEのoffを切り替えてから入力する。 記号の入力が全角なら[alt]+[カタカナひらがなローマ字]で入力モードをローマ字に切り替えてから入力する。 その他の方法では、いったんカナ入力で入力した後、変換候補をF9(全角英数)もしくはF10(半角英数)で切り替える方法もある。 ファンクションキー入力では入力モードの切替戻しが不要なので速いはずなんだけど、キーボードを見ない入力だと押し間違いが酷い。
2025年9月1日月曜日
ExcelマクロでMsgBoxの処理をインターセプト
Excelマクロで以下のMacro1ように、中でMsgBoxを呼び出していると、いちいちメッセージボックスが表示される。これを別マクロ(Macro2)から複数回呼び出すと、複数回メッセージボックスが表示され、何度も応答しなければならなくなる。Macro1を別マクロから複数回呼び出しにも耐えられるように、Macro1に手を加えないで、メッセージボックスにOKボタンを押したことにしたい。 そこで、MsgBoxの呼び出しをインターセプトしてみる。 オブジェクトブラウザによると、MsgBoxはVBAライブラリにあるFunctionで第1引数が必須で、戻り値がVbMsgBoxResultのよう。ということで、VBAライブラリよりも早く呼び出されるように、同プロジェクトの標準モジュールにMsgBox関数を定義する。 まず、標準モジュール追加で、追加されたモジュールのオブジェクト名をわかりやすく「MsgBoxインターセプト」とする。この「MsgBoxインターセプト」標準モジュールにMsgBox関数を追加する。コードは以下のもの。 If文の条件をインターセプトするかどうかの判断で、インターセプトするなら、Then以降の部分に処理を記述し、 インターセプトしないなら、Else以降の部分で、VBAライブラリのMsgBox関数を受け取った引数で呼び出して、結果をMsgBoxに格納する。 Function MsgBox(Prompt As String, Optional Buttons As VbMsgBoxStyle = vbOKOnly, Optional Title, Optional HelpFile, Optional Context) As VbMsgBoxResult If Left(Prompt, 2) = "終了" Then MsgBox = vbOK Exit Function Else MsgBox = VBA.MsgBox(Prompt, Buttons, Title, HelpFile, Context) End If End Function これで、メッセージボックスを表示したくない場合の動きができる。 実用ではメッセージボックスを表示しない代わりに、引数と呼び出し時間をログ出力する方がよいかもです。
2025年8月31日日曜日
Excelセル結合での裏技と注意点
Excelではセル結合という機能があり、これを使うと長方形の複数セルを1まとめで表示させることができる。 この機能を使うことは(他の有識者と同様に)私もお勧めしませんが、使わなければならない時や、 使っても実害がないときで、ダメな理由を説明するのが面倒な時があり、私自身たまに使っています。 実例として、以下のA2～A6セルを結合しようとすると、以下のメッセージのように結合したセルは、左上セル以外のセルが空欄になる。 (A2セルの値はグループAだが、A3セルからA6のセルの値は空)なので、オートフィルターを使ってA列の値が「グループA」の行を抽出しようとすると、A2の行のみ表示される。で、ここからが本題。結合したA2からA5セルと同形のセルをコピーし、「形式を選択して貼り付け」から、 「数式(F)」もしくは「数式と数値の書式(R)」もしくは「値と数値の書式(U)」をすると、結合セルの左上以外のセルにもコピーされる。結果、A7セルで表示している、A2～A6までで値が入っているセルの数え上げが5(貼り付け前は1)、E列で表示しているA列とB列の一致が 2行目から6行目まで一致となっている(貼り付け前は2行目のみ一致)。 これで、オートフィルターも使えるようになる。問題はここから、 集計する予定があるセルを結合した場合、結合時点では、表示されている値のみで集計される。(C7セルが1)ただ、同じように形式:数式で貼り付けをした場合、非表示の値も集計結果に表れる。(C7セルが15)普通に過ごしていたら、こんなことはしないと思うのだけど、 数式で貼り付けすると結合セルの中身まで更新されることが一般的ではない状況なので、悪い考えを持った人が、 自分の数字をごまかすために使ってくるかもしれないと警戒する必要はあるかと思います。 (Excelのごまかし表示方法は、テキストボックスでの覆いかぶせなど、いくらでもあるので結局Excelの作成者の人を見て信じるしかないのですが)
2025年8月11日月曜日
Excelのピボットテーブルでより自由な集計へ
Excelのピボットテーブルでは、単純な集計なら直観的にできます。 ただ複雑な集計となると、少し直感的でない操作をする必要があるので備忘録。 まずは単純な集計 例えば、以下の2015-2020年の県別人口から、ピボットテーブルで、県別最大人口を出すには、[挿入]メニューピボットテーブルで、ピボットテーブルをつくり、ドラッグアンドドロップで、「県名」を「行」の場所に配置し、「人口(千人)」を、「値」の場所に配置し、「合計/人口(千人)」と表示された部分を左クリックし「値フィールドの設定」から、合計を最大に切り替える。で基本的な集計はできた。SQL文で疑似的に書くと以下のような感じ。 SELECT 県名,MAX(人口(千人)) FROM 県別人口 GROUP BY 県名 で本題。以下のような100万人を超えた県には"〇"そうでない県には"×"のようなものを書きたい場合。(SQLでは以下のイメージ) SELECT 県名,CASE WHEN MAX(人口(千人)) > 1000 THEN '〇' ELSE '×' END FROM 県別人口 GROUP BY 県名 どうするかというと、OFFICE2016以降であればDAXでメジャー式が使える、という記事です。 まず、ピボットテーブルを挿入するときに出てくる以下のダイアログで、「このデータをデータモデルに追加する」にチェックを入れておくと、列をまとめたテーブル名が表示されるようになるこのテーブル名を右クリックし、[メジャーの追加]を選ぶと、集計用の式が書けるようになる。適当にメジャーを作ってみて、OKを押すと、メジャーが表示されるようになる。これを値の部分にドラッグアンドドロップすると、IF条件で表示を〇×に切り替えることができる。
登録:
投稿 (Atom)