Sql Server

SQL ServerでT-SQLを使用してテーブルのディスクスペース使用量を確認する方法

データベースを管理する際、各テーブルがどれだけのディスクスペースを消費しているかを理解することは、最適化とキャパシティプランニングにとって非常に重要です。多くのユーザーがSQL Server Management Studio (SSMS)のグラフィカルインターフェイスを通じてテーブルのプロパティを確認することに慣れているかもしれませんが、同じタスクをT-SQLを使用して実行する方法を知ることは、自動化や高度なデータベース管理タスクにおいて非常に有益です。

このブログ投稿では、T-SQLを通じてSQL Serverのテーブルが占有しているディスクスペースを確認するための簡単な方法を紹介します。このアプローチは、効率を高めるだけでなく、SQLスクリプトに深く掘り下げる機会を提供します。

解決策の概観

sp_spaceusedシステムストアドプロシージャと一時テーブルを併用して、データベース内のさまざまなテーブルに関する情報を収集します。以下は、達成することの概要です:

  1. サイズ情報を保持するための一時テーブルを作成。
  2. sp_spaceusedコマンドを実行して、すべてのテーブルからサイズデータを収集。
  3. 収集されたデータを読みやすいメトリックに解析。
  4. 予約サイズでソートされた出力を表示。

さあ、始めましょう！

ステップ 1: 一時テーブルの作成

最初のステップは、抽出したサイズの詳細を保存するための二つの一時テーブルを定義することです。SQL Serverでは、一時テーブルは#記号でプレフィックスされます。

CREATE TABLE #tmpSizeChar (
     table_name sysname ,
     row_count int,
     reserved_size varchar(50),
     data_size varchar(50),
     index_size varchar(50),
     unused_size varchar(50))                              

CREATE TABLE #tmpSizeInt (
     table_name sysname ,
     row_count int,
     reserved_size_KB int,
     data_size_KB int,
     index_size_KB int,
     unused_size_KB int)

説明:

  • #tmpSizeChar: サイズを単位付きで保持する文字形式の結果を保持します（例：MB、KB）。
  • #tmpSizeInt: 簡単な計算を容易にするため、整数形式の結果を保存します。

ステップ 2: テーブルからサイズデータを収集

sp_msforeachtableストアドプロシージャを使用して、データベース内のすべてのテーブルに対してsp_spaceusedコマンドを実行します。このコマンドは、各テーブルのスペース使用状況に関する詳細情報を提供します。

SET NOCOUNT ON
INSERT #tmpSizeChar
EXEC sp_msforeachtable 'sp_spaceused ''?'''

説明:

  • NOCOUNT ON: T-SQLステートメントによって影響を受けた行数を示すメッセージが返されるのを防ぎ、複数のステートメントを持つスクリプトのパフォーマンスを向上させます。

ステップ 3: 集めたデータの解析

データを収集したので、次に文字データを分析に適した整数値に変換する必要があります。

INSERT INTO #tmpSizeInt (
        table_name,
        row_count,
        reserved_size_KB,
        data_size_KB,
        index_size_KB,
        unused_size_KB
        )
SELECT  [table_name],
        row_count,
        CAST(SUBSTRING(reserved_size, 0, PATINDEX('% %', reserved_size)) AS int) reserved_size,
        CAST(SUBSTRING(data_size, 0, PATINDEX('% %', data_size)) AS int) data_size,
        CAST(SUBSTRING(index_size, 0, PATINDEX('% %', index_size)) AS int) index_size,
        CAST(SUBSTRING(unused_size, 0, PATINDEX('% %', unused_size)) AS int) unused_size
FROM #tmpSizeChar

説明:

  • このステップでは、予約サイズの数値部分を抽出し、信頼性のある計算用に整数に変換します。本質的には、サイズ文字列から文字（KBやMBなど）を取り除きます。

ステップ 4: 結果の表示

最後に、一時的な整数サイズテーブルから要約結果を取得して表示します。

SELECT * FROM #tmpSizeInt
ORDER BY reserved_size_KB DESC

説明:

  • このSQLコマンドは、#tmpSizeIntからすべてのレコードを取得し、reserved_size_KB列によって並べ替え、どのテーブルが最も多くのディスクスペースを消費しているかを確認できます。

結論

上記の手順に従うことで、SQL Serverのテーブルがどのくらいのディスクスペースを使用しているかをT-SQLで構造化された方法で判断できるようになりました。この方法は、最適なパフォーマンスを維持し、SQL Server環境全体でリソース使用状況を追跡したいデータベース管理者や開発者に特に役立ちます。

これで、GUIのクリックを強力なコマンドラインアプローチに置き換え、データベース管理スキルを根本的に向上させることができます！