小物SEのメモ帳

IT関係の試したことや調べたことの備忘録

【SQL Server】SSMS17.0「実際の実行プランの分析」機能について

SQL Server Management Studio(SSMS) 17.0で、保存済みの"実際の実行プラン"の分析が可能になったようなので使ってみました。まだ現状では、「不正確な基数推定」というシナリオしか分析できませんが、あまりSQLServerに精通していない技術者でも、どのよう…

【SQLServer】【java】jdbcからSQLを実行した場合のSQLステートメントと実行プラン

jdbcからSQLを実行した場合、SQLserver側でどのようなステートメント・実行プランとして現れるのかまとめてみました。 接続プロパティSelectMethodが未指定の場合、もしくはdirectの場合 まずはSelectMethodが未指定の場合で確かめてみます。 デフォルトがdi…

【SQL Server】tempdbのCWT_PrimaryKeyとは

実行プランでCWT_PrimaryKeyというテーブルを参照していることがありますが、どのようなものか気になり調べてみました。これは静的カーソルの結果セットを保存するtempdbのワークテーブルのようです。静的カーソルは、カーソルを開いた時点の結果をtempdbへ…

【SQL Server】T-SQLのtry catch について

ストアドプロシージャを書く際にエラー処理を書くかと思いますが、例外をスローするためのサンプルを掲載します。Googleで検索するとやたらRAISERRORを使ったサンプルが検索にヒットしますが、SQLserver2012以降を使っている場合なら単純にTHROWすればよいか…

【SQL Server2016】dm_exec_input_bufferについて

SQL server2016以降で使用できるdm_exec_input_bufferについて。これまではDBCC INPUTBUFFERコマンドを使用して各セッションで実行された最後のSQL文の確認等を行っていたかと思いますが、DBCCコマンドゆえに他の動的管理ビューとのJOINができず、複数セッシ…

【SQL server】インデックス間のデッドロック

一つのテーブルにクラスタ化インデックスと非クラスタ化インデックスが貼られている場合に、同一テーブル内のインデックス間で発生するデッドロックについて検証してみました。 テストデータとテーブルを準備 以下のようなテーブルにテストデータを用意しま…

【SQL Server】クエリ実行時の待機イベントを確認する

クエリチューニングを実施する際に実行プランやIO、実行時間を確認することが多いかと思いますが、実行時間のうち待機イベントと待機時間を確認する方法の説明です。以下を使用してCPU時間と実行時間を確認することが多いのではないでしょうか。 SET STATIST…

【SQL Server】処理の進捗がわかるライブクエリ統計

SQLServer2014以降で使用することができるライブクエリ統計の機能がクエリのパフォーマンスチューニングに役立ちそうだったため使い方を記します。 ライブクエリ統計を使うことで実行に長時間かかるクエリが、どの処理で時間を要しているのかわかるため、改…

【SQL Server】OPTIMIZE FOR UNKNOWNを使用した場合の統計について

ストアドプロシージャのパラメータスニッフィングによるパフォーマンス低下対策としてRECOPILEやOPTIMIZE FOR UNKNOWNを使うことが多いかと思いますが、OPTIMIZE FOR UNKNOWNで使われる統計がどのように算出されているのか検証してみました。 パラメータスニ…

【SQL Server】テーブルのデータをINSERT文付きでエクスポート

テーブル内のデータをINSERT文付きでエクスポートする手順です。 データ移行用のデータ抽出などで利用できるかと。SSMSの左ペインのオブジェクトエクスプローラのデータベース名で右クリック、「タスク」を選択し、「スクリプト生成」を選びます。 今回は特…

【SQL Server】よく忘れる構文備忘録

たまに使用する際に毎回調べて書いていたので、備忘録&サンプルとして。 ストアドの実行結果をINSERT ストアドの結果セットが一つであれば、INSERT INTO Table_name SELECT…と同じ要領で記述できます。 INSERT INTO Table_name EXEC sp_lock 他のテーブルの…

【SQL Server】並列処理時のパフォーマンスについて

前回は、並列処理の設定確認や変更方法を記しましたが、今回は並列実行されていることの確認や、その効果検証についてです。 memorandom-nishi.hatenablog.jp 並列処理時の確認 動的管理ビューのdm_exec_query_statsにSQL Server2016以降から並列度の次数(do…

【SQL Server】並列処理MaxDOPの設定確認と変更

SQL Serverでの並列クエリ処理 複数のCPUでクエリを処理して並列処理される場合、実行時間を短縮することができます。 並列クエリの実行では、取得するデータセットを小さく分割して、分割されたデータセットをそれぞれのワーカー・スレッドが並列処理するこ…

【PowerShell】【SQL Server】SQLの結果を条件にイベントログに書き込む

完全に自分用の備忘録です。 SQL Serverに対しストアドを作ることなく、SQLの戻り値を使ってイベントログに書き込みをするスクリプトです。 イベントログへの書き込み 事前にイベントログにソースを登録したことがなければNew-Eventlogにて作成しておきます…

【SQL Server】【Windows】セキュリティイベントログにログイン失敗のログを記録する

イベントログの「セキュリティ」にSQL Serverログインの失敗を記録する際の手順です。以下参考のURLを実際に実行してみたものになります。 https://msdn.microsoft.com/ja-jp/library/cc645889.aspx まず、auditpol.exeで成功・失敗の監査を有効にします。 …

【SQL Server】bcpを使用したインポートとエクスポート

テストする際にテーブルのデータを一括でインポート・エクスポートする場合の方法の一つとしてbcpを使用することがありますが、オプションやフォーマットファイル出力のやり方をよく忘れるので覚書。 フォーマットファイルについては、bcpだけでなくBULK INS…

【SQL Server】クエリの実行プランとパフォーマンス情報の取得方法Part2

前回に引き続き実行プランとパフォーマンス情報の取得について説明します。今回はトレースとデータ蓄積による情報取得です。 前回記事はこちらを参照して下さい。 memorandom-nishi.hatenablog.jp 3.トレースして取得する トレースを仕掛けて詳細情報を取得…

【SQL Server】クエリの実行プランとパフォーマンス情報の取得方法Part1

クエリのパフォーマンス状態の分析において、必ずといってよいほど必要となる実行プランやCPU時間や実行時間、実行回数などの取得方法について、いくつか方法がありますのでまとめます。大きく分けて以下のような方法で取得できます。 1.動的管理ビューで取…

【SQL Server】CPU使用率、IO負荷の高いクエリレポート

クエリのパフォーマンス状況の確認方法として、DMVを組み合わせた「CPU使用率の高いクエリ」や「IO負荷の高いクエリ」を取得するクエリを事前に準備しているシステム管理者も多いかと思いますが、グラフィカルなレポートという形で取得するのも視覚的に確認…

【SQL Server】WHERE句で正規表現的な検索をする

検索をする際に、SQLServerでは正規表現を使うことはできません。 使い勝手は正規表現ほどではありませんが、似たようなものがありますので検証してみました。まずは、テーブルを作成します。 CREATE TABLE TEXT_TEST ( [id] INT, [text] NVARCHAR(255) ) 今…

【SQL Server】データの格納ページ位置の確認方法

テーブルに格納されているレコードがどのページにあるのか確認する方法です。 デッドロックの調査や以前説明したラッチ状況を確認する場合などに使えるかと思います。 sys.fn_PhysLocFormatter 今回確認に使うundocumentedなファンクションです。 どのような…

【SQL Server】ロックとラッチのトレース

SQL Serverの待機イベントの一つであるロックとラッチがどのような順番で獲得され、解放されるのかトレースしてみました。 拡張イベントの設定 トレースするにあたって拡張イベントを使います。 今回試すSQLはセッション63にて実行するので、フィルタしてあ…

【SQL Server】統計情報のヒストグラムと実行プランの予測行数

SQL Serverでは実行プランの決定における情報のうちの一つとして統計情報があります。 その統計情報のうちデータの分布を表すヒストグラムについて説明します。 統計情報は下記クエリで取得できます。 -- 引数は1つ目がテーブル名、2つ目が統計名 -- 結果セ…

【SQL Server】【Python】pyodbcでSQL Serverのテーブルからデータを取得する

CSVなどをデータ分析や可視化する際に個人的によくPythonのPandasを利用するのですが、SQLServer2016内に格納してあるデータを直接参照し、集計やグラフ作成する必要がありましたのでやってみました。 ※Python Anacondaを事前にインストール済みです。 事前…

【SQL Server】【Bat】コマンドラインからのSQL呼び出しサンプル

SQLCMDを用いて、batファイルからSQLを呼び出し、結果ファイルに書き出すサンプルです。 スペースによる固定長ではなく、スペースを除いたカンマ区切りで出力したいことが多いかと思います。システムの運用において定常業務の自動化などでちょっとしたbatか…

【SQL Server】クラスター化インデックスと非クラスター化インデックス

SQLserverのインデックスのうちよく使われる基本的なインデックスであるクラスター化インデックス、非クラスター化インデックス、付加列について解説します。これらはSQLserverのクエリをチューニングするうえでの基礎となるので押さえておくとよいでしょう…

【SQL Server】Plan Cacheのサイズと内訳

SQLServerでは実行プランに関する情報は、プランキャッシュ(Plan Cache)に格納してあります。 プランキャッシュはPerfmonやDMVにて以下の情報を取得することが可能です。 カウンター 説明 Object Plans ストアドプロシージャ、ファンクション、トリガーのク…

【SQL Server】テーブルごとのレコード件数およびディスク使用量を出力する

前回に引き続き、こちらもよく使うので備忘録です。 レコード件数はクエリの性能に影響しますので、取得の仕方を把握しておくとよいかと思います。左ペインのオブジェクトエクスプローラのデータベースを選択して、右クリックを押下し、「レポート(P)」-「標…

【SQL Server】接続しているDB内のすべてのテーブルの定義情報を抽出する

SQLServerにおいて、すべてのテーブルの定義情報をまとめて取得する際にお手軽に取得できるクエリです。 sp_Msforeachtableはundocumentedなストアドプロシージャですが、すべてのテーブルに対し、処理を実行してくれるため何かと便利です。 sp_MSforeachtab…

【SQL Server】再コンパイル時の原因調査

バッチでステートメントレベルの再コンパイルが発生した際の原因調査として、拡張イベントが利用できます。下記のようにsql_statement_recompileイベントを拡張イベントとして追加すれば確認できます。 実際に当該イベントが発生した場合は以下のようにイベ…