【SQL Server】パーティションテーブルを使ってパフォーマンスの差異を確認しよう

ニュース＆ブログ

【SQL Server】パーティションテーブルを使ってパフォーマンスの差異を確認しよう

投稿日：2025/01/27

はじめに

　データベースに格納されるデータが増大するにつれ、効率的なデータ管理とパフォーマンスの維持が重要になります。※1

　特にクエリの応答時間が課題となる中、SQL Serverのパーティショニング機能が有用となるケースもあります。

　本記事では、パーティショニングの概要と実際の検証内容を紹介致します。
　※1 当ブログには大容量のデータを扱う「データ分析基盤」についての記事もございますので、気になる方がいれば御覧ください。

パーティションの概要

　パーティショニング機能は、論理的なテーブル分割を通じてデータに対するアクセス効率を向上させる機能です。

　非パーティションテーブルは、テーブルとデータファイルが1対1の関係になっているので、データ量が増加していくとディスクからスキャンしなければいけないデータも増えてしまうため、パフォーマンス劣化に繋がる可能性があります。

　一方パーティションテーブルは、物理的なファイルやファイルグループに対して一連のデータを分散することでスキャンするデータも抑えることができるため、特定のデータへのアクセスが素早く行うことができ、クエリパフォーマンスが向上します。

パーティションに関連する用語

　パーティショニングを実現するためには、「パーティション関数」と「パーティション構成」が必要です。

　パーティション関数は、行をどのパーティションに属させるかを定義します。

　パーティション構成は、特定のパーティションがどのデータファイルに格納されるかを指定します。

　この構造により、大規模なデータを管理しやすくし、効率的なデータ操作を実現します。

検証内容

　今回は、非パーティションテーブルとパーティションテーブルを比較し、クエリのパフォーマンスを検証します。

　具体的には、同一のデータを持つテーブルを用いて、以下のシナリオを実施します。

　┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓

　　10万件のレコードを持つ非パーティションテーブルに対して、特定の条件でデータを取得するクエリを実行。

　　同様の条件で、月別にパーティションされたテーブルに対して同じクエリを実行し、ディスクへのI/Oを比較する。

　┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛

検証

　まずは今回使用する非パーティションテーブルの状態を確認します。

-- パーティションの効果検証のため、日付カラムを持つ1年分のデータが格納
SELECT 
 LogDate
,COUNT(*) AS Total_Logdate 
FROM [dbo].[NormalLogs]
GROUP BY Logdate
ORDER BY Logdate;

-- 10万件のレコード
SELECT 
 COUNT(*) AS Total_Count
FROM [dbo].[NormalLogs]

　はじめにパーティション関数の作成を行います。

　定義としては、1カ月毎のパーティションを作成しておきます。

-- パーティション関数の作成
CREATE PARTITION FUNCTION LogDatePartitionFunction (DATE)
AS
RANGE RIGHT FOR VALUES 
('2023-01-01', '2023-02-01', '2023-03-01', '2023-04-01', 
 '2023-05-01', '2023-06-01', '2023-07-01', '2023-08-01', 
 '2023-09-01', '2023-10-01', '2023-11-01', '2023-12-01');

-- 作成できているかの確認
SELECT 
 name AS PartitionFunctionName
,type_desc AS PartitionType
FROM sys.partition_functions
ORDER BY name;

　
　次にパーティション構成です。

　本来であれば複数のファイルグループとデータファイルを作成した方が良いかと思いますが、今回はあくまで検証レベルのため全てのパーティションのデータを同一のファイルグループに格納するものとします。

-- パーティション構成の作成
CREATE PARTITION SCHEME LogDatePartitionScheme
AS PARTITION LogDatePartitionFunction
ALL TO ([PRIMARY])

-- 作成できているかの確認
SELECT 
 name AS PartitionSchemeName
FROM sys.partition_schemes
ORDER BY name;

　準備が整ったので、パーティションテーブルを作成します。

-- パーティションテーブルの作成
CREATE TABLE MyPartitionedTable (
    LogID INT,
    LogDate DATE,
    LogData nvarchar(1000),
) ON LogDatePartitionScheme (LogDate); -- LogDateをパーティション分割列として指定

-- 非パーティションテーブルのデータをパーティションテーブルへをコピー
INSERT INTO [dbo].[MyPartitionedTable]
SELECT * FROM [dbo].[NormalLogs]

-- 格納されているデータに差異がないかの確認
SELECT * FROM [dbo].[NormalLogs]
EXCEPT
SELECT * FROM [dbo].[MyPartitionedTable]

SELECT * FROM [dbo].[MyPartitionedTable]
EXCEPT
SELECT * FROM [dbo].[NormalLogs]

　パフォーマンスの確認を行う前に、それぞれのテーブルに関連するパーティション情報を確認してみましょう。

-- パーティション情報の確認
SELECT partition_number,row_count FROM sys.dm_db_partition_stats WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')
SELECT partition_number,row_count FROM sys.dm_db_partition_stats WHERE OBJECT_ID = OBJECT_ID('NormalLogs')

　パーティションテーブルは13のパーティションに分割されており、非パーティションテーブルはパーティション設定がないことがわかりますね。

　ちなみにパーティション分割列のデータを指定して $PARTITION を実行することで、どのパーティションに属しているかも確認できたりもするようです。

SELECT $PARTITION.[LogDatePartitionFunction]('2023-12-28')

　さて、最後にパフォーマンスの確認を行います。

-- パフォーマンスの確認
SELECT * FROM [dbo].[NormalLogs] WHERE LogDate = '2023-07-31'
SELECT * FROM [dbo].[MyPartitionedTable] WHERE LogDate = '2023-07-31'

　どちらもヒープテーブル（インデックスが作成されていないテーブル）なので、実行計画はTable Scanとなります。

　重要なのが物理I/Oになりますが、非パーティションテーブルでは10万行発生しているのに対し、パーティションテーブルでは8494行です。

　つまりは、2023-07-31 が格納されているパーティションのみへのTable Scanを行っていますね。

まとめ

　近年はDX化に伴い、社内・社外のデータを統合するデータ分析基盤を構築する企業様も増えています。

　データを統合することでデータ量も膨大なものになるので、その環境でのクエリパフォーマンスを維持していくためパーティショニング機能を活用することができそうですね。

　次回はパーティションのSPLIT、SWITCH、MERGEについて紹介しようと思います。

中川智文（2022年入社）
株式会社システムサポートフューチャーイノベーション事業本部ソリューションサービス事業部所属
Oracle Database, SQL Serverを中心とした案件に従事