読者です 読者をやめる 読者になる 読者になる

小物SEのメモ帳

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

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