データベースの肥大化を防ぎ、パフォーマンスを向上させる!SQL Server 2005で眠っているオブジェクトを有効活用する方法
Microsoft SQL Server 2005 で使用されていないオブジェクトを識別する
Microsoft SQL Server 2005 には、データベース内の使用されていないオブジェクトを識別するのに役立ついくつかのツールと手法があります。使用されていないオブジェクトを特定することで、データベースのパフォーマンスと管理を向上させることができます。
ツールと手法
- システムテーブル:
- sys.objects - データベース内のすべてのオブジェクトに関する情報を提供します。
- sys.dm_db_index_usage_stats - インデックスの使用状況に関する情報を提供します。
- sys.dm_exec_query_stats - 実行されたクエリに関する情報を提供します。
- DMV (Dynamic Management Views):
- サードパーティ製ツール:
- ApexSQL Unused Objects - 使用されていないオブジェクトをスキャンして識別するツール。
- Redgate SQL Dependency Tracker - オブジェクト間の依存関係を分析するツール。
使用されていないオブジェクトを特定する手順
- 使用していないオブジェクトの候補となるオブジェクトを特定する
- 長期間使用されていないオブジェクト
- テストや開発用に作成されたオブジェクト
- 不要になったオブジェクト
- システムテーブルや DMV を使用して、オブジェクトの使用状況に関する情報を収集する
- オブジェクトが最後に使用された日時
- オブジェクトによって消費されるリソース
- サードパーティ製ツールを使用して、使用されていないオブジェクトをスキャンする
- 収集した情報に基づいて、削除するオブジェクトを決定する
注意事項
- オブジェクトを削除する前に、そのオブジェクトが本当に使用されていないことを確認する必要があります。
- オブジェクトを削除すると、そのオブジェクトに依存する他のオブジェクトも影響を受ける可能性があります。
- オブジェクトを削除する前に、データベースのバックアップを取ることをお勧めします。
補足
- SQL Server 2005 では、sp_spaceused というストアドプロシージャを使用して、データベース内のオブジェクトの使用状況に関する情報を取得することができます。
- SQL Server Management Studio (SSMS) を使用して、データベース内のオブジェクトの使用状況を視覚化することができます。
-- 使用されていないオブジェクトの候補となるテーブルをリストアップする
SELECT
s.name AS SchemaName,
t.name AS TableName
FROM
sys.tables t
INNER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.is_ms_shipped = 0
AND t.last_used_date < DATEADD(dd, -30, GETDATE())
AND t.object_id NOT IN (
SELECT object_id
FROM sys.indexes
WHERE is_primary_key = 1
)
-- 使用されていないオブジェクトの候補となるストアドプロシージャをリストアップする
SELECT
s.name AS SchemaName,
p.name AS ProcedureName
FROM
sys.procedures p
INNER JOIN
sys.schemas s ON p.schema_id = s.schema_id
WHERE
p.is_ms_shipped = 0
AND p.last_used_date < DATEADD(dd, -30, GETDATE())
-- 使用されていないオブジェクトの候補となるトリガーをリストアップする
SELECT
s.name AS SchemaName,
t.name AS TableName,
tr.name AS TriggerName
FROM
sys.triggers tr
INNER JOIN
sys.tables t ON tr.parent_id = t.object_id
INNER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
tr.is_ms_shipped = 0
AND tr.last_used_date < DATEADD(dd, -30, GETDATE())
上記のコードは、以下の情報を取得します。
- 使用されていない可能性のあるテーブル
このコードは、使用されていないオブジェクトを特定するための出発点として使用できます。オブジェクトを削除する前に、そのオブジェクトが本当に使用されていないことを確認する必要があります。
- このコードは、SQL Server 2005 でのみ使用できます。
- このコードは、すべての使用されていないオブジェクトを特定するわけではないことに注意してください。
使用されていないオブジェクトを識別するその他の方法
SQL Server Profiler は、データベースに対するすべての活動をトレースするツールです。SQL Server Profiler を使用して、特定のオブジェクトがアクセスされているかどうかを監視することができます。
データベース監査は、データベースに対する特定の操作を追跡する機能です。データベース監査を使用して、特定のオブジェクトが変更されているかどうかを監視することができます。
スクリプトを使用する
サードパーティ製ツールを使用する
ApexSQL Unused Objects や Redgate SQL Dependency Tracker などのサードパーティ製ツールを使用して、使用されていないオブジェクトを識別することができます。
各方法の利点と欠点
方法 | 利点 | 欠点 |
---|---|---|
システムテーブルと DMV を使用する | 標準機能であり、追加コストがかからない | 詳細な情報が得られない |
サードパーティ製ツールを使用する | 詳細な情報が得られる | 追加コストがかかる |
SQL Server Profiler を使用する | オブジェクトへのアクセスをリアルタイムで監視できる | 多くのリソースを消費する |
データベース監査を使用する | オブジェクトへの変更を監査できる | 設定が複雑 |
スクリプトを使用する | 柔軟性が高い | スクリプト作成スキルが必要 |
- 使用可能なリソース
- 必要な情報レベル
- スクリプト作成スキル
sql-server