小物SEのメモ帳

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

SQL Server

【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イベントを拡張イベントとして追加すれば確認できます。 実際に当該イベントが発生した場合は以下のようにイベ…

【SQL Server】リレーション図の出力

データベース内の各テーブルのER図の出力方法の備忘録です。 「データベースダイアグラム」を選択して右クリックを押下、「新しいデータベース ダイアグラム(N)」を選択します。 リレーション図に表示したいテーブルを選択します。 下記のように出力されます…

【SQL Server】データコレクションの「サーバーの利用状況の履歴」・「クエリ統計の履歴」レポート出力の仕方

データコレクションのレポートのうちよく参照するであろう「サーバの利用状況の履歴」「クエリ統計の履歴」の出力についてです。「サーバの利用状況の履歴」「クエリ統計の履歴」で出力できるグラフの関係図は以下のとおりです。 「サーバの利用状況の履歴」…

【SQL Server】データコレクションのディスク使用量レポート出力の仕方

システムデータコレクション(MDW)のディスク使用量レポートの出力の仕方を説明します。 ディスク使用量レポートとしては以下のような関係で出力が可能です。 プロパティ情報は以下のようになっています。 ここでは、どのようなクエリで本レポート用の情報収…

【SQL Server】データコレクションのレポート出力の仕方

システムデータコレクション(MDW)を利用するとパフォーマンス情報を定期的に取得しレポートとして出力でき分析時に便利です。レポートの出力の仕方は「管理」-「データコレクション」を右クリック、「レポート(P)」-「管理データウェアハウス」で各種レポー…

【SQL server】ラッチ状況の確認

SQLserver ラッチ待ちの解説と確認SQLserverには3種類のラッチが存在します。1.LACTH_[xx] (非バッファーラッチ) 2.PAGELATCH_[xx] (バッファーラッチ) 3.PAGEIOLATCH_[xx] (バッファーラッチ) ※[xx]にはSH(参照),UP(更新),EX(排他)などが入ります。これらに…

【SQL server】デッドロックの調査方法

デッドロック発生に際し、分析時の確認の仕方について紹介します。エラーログやSQL Plofilerなどで調査可能ですが、個人的には拡張イベントがお勧めです。デフォルト設定であれば、拡張イベントの「system_health」でデッドロック情報は取得しているので、事…

【SQL server】ロックエスカレーションの閾値について

悪者扱いされることの多いロックエスカレーションの発生する閾値と設定の確認方法についてです。・ロック上限が動的な場合(locks オプションが既定値)のロックエスカレーションの閾値 (1)1 つの Transact-SQL ステートメントが 1 つのテーブルまたはイン…

【SQL server】ロックの種類とページ情報

デッドロックを調査するにあたりロックの種類について調べた内容を記しておきます。 ロックの粒度 上から順にロックの粒度は細かくなります。 Lock Type 説明 DB データベース単位のロック TAB テーブル単位のロック PAGE ページ単位のロック ROW 行単位のロ…

【SQL server】トランザクション分離レベルについて

SQL serverで設定できるトランザクション分離レベルについて整理しました。 ※デフォルトはREAD COMMITTEDになります。 トランザクション分離レベル ロックの種類 ダーティリード ノンリピータブルリード ファントムリード READ UNCOMMITTED 悲観的ロック ○ ○…

【SQL server】利用状況モニターの情報取得について

個人的にパフォーマンス障害時によく利用する「利用状況モニター」ですが、いまいち情報取得の方法や周期について理解が不足しておりましたので、ここで整理をしてみます。 (※プロファイラーの取得結果SQLをそのまま記載してありますので、内容問題がある場…

【SQL server】sp_who2について

SQL Serverの性能情報を取得するうえで、簡単にいろいろな情報を収集できるシステムストアドプロシージャであるsp_who2について、改めて内部を確認してみました。 ※sp_who2はundocumentedなシステムストアドプロシージャであるため、変更されている恐れがあ…

ファイルグループへのテーブルの新規追加の手順

ファイルグループへのテーブルを新規で追加する手順の理解があいまいだったため、簡単に確認してみました。 ファイルグループを分けた場合、違うディスクに配置することで、IO負荷の分散が期待できます。 ⓵DBに新規のファイルグループを追加します -- ======…

【SQL server】クエリ統計の履歴レポートについて

データコレクションの機能で、「クエリ統計の履歴」を実行し、レポート表示させる場合の元スクリプトとテーブルについて確認してみました。内容に誤りがある場合は、ご指摘いただけると幸いです。※変数の値はそれぞれの対象となるSQLによって異なります。レ…

【SQL server2012】特定の実行プランのみキャッシュクリアする方法

SQL server では、キャッシュクリアしたい実行プランのプランハンドルがわかれば、特定の実行プランのみをクリアすることができます。 何らかの原因で実行プランが変わり、急激なパフォーマンス低下が発生してしまった際等に、他の処理への影響を減らしつつ…

SQL server 2016の新機能クエリストアについて

◆クエリストアでできること SQL server の特定のクエリの実行プランは時間の経過とともに変化します。プロシージャキャッシュ(プランキャッシュ)には、最新の実行プランのみが格納され、メモリ負荷によるキャッシュアウトで実行プランがプランキャッシュか…

【SQL server】Max=min server memory設定時のバッファープール使用状況の把握について

Buffer Manager Max server memoryで設定できる値は内訳は以下となっているのでしょうか。。。 Max server memory = Memory Manager\Total Server Memory (KB) = Memory Manager\Stolen Server Memory (KB) + Memory Manager\Free Memory (KB) + Memory Mana…

【SQL server】パラメータスニッフィングによる実行プランのパフォーマンス低下

SQL server では、ストアドプロシージャ実行時に実行プランをハードパースする場合に、受け取ったパラメータに最適化する形で実行プランが作成されます。 パラメータスニッフィングにより不適切なパラメータで実行プランがハードパースされると効率の悪い実…