[Sample] えす！エス！レスキュー SQL Server -どっぷりリファレン中！！- Vol. 1

Article
08/04/2009

◆◇◇◆━━━━━━━━━━━━━━━━━━━━2009.06.10━━━・‥… えす！エス！レスキュー SQL Server -どっぷりリファレン中！！- ・‥…━━━━━━━━━━━━━━━━━━━━━Vol. 1━━━━◆◇◇◆<<目次>>■SQL Server リファレン中！！　その１■お知らせ・・・○ご質問について■編集者より┏─────────────────────────────────┓┃SQL Server リファレン中！！ ┃┗─────────────────────────────────┛インサイトテクノロジーがお送りする、SQL Serverのメルマガ「えす！エス！レスキュー SQL Server -どっぷりリファレン中！！-」第1弾をお届けします!!このメルマガでお送りする情報は以下のエディションをターゲットにし、記載しております。ターゲット： SQL Server 2008 Enterprise Edition x86Enterprise Editionが高い!と思われるかも知れませんが、180日間限定のトライアル版を、以下のサイトからダウンロードできますのでぜひ、メルマガを見つつ、色々と試していただけたらと思います。SQL Server 2008 の評価版ダウンロードページhttps://www.microsoft.com/japan/SQLServer/2008/downloads/default.mspxこのSQLServerのメルマガは、夏椰(かや)がお送りします。最初なので、簡単に自己紹介をさせてください。歳は30歳です。(2009年06月現在)身長は166cm、基礎代謝が1400kcalぐらいです。ソフトテニスと一眼レフが趣味です。データベースも趣味だったのですが、インサイトテクノロジーに入社し、お仕事になりました♪そして、こうして皆様にメルマガという形で私の知っていることや、チャレンジしたことをお届けする機会を得る事が出来、嬉しく思っています。では、本題へ。今回のSQL Server - どっぷりリファレン中では、SQL Serverにおける「データファイル」と「ページ」、「エクステント」について見ていきます。====================================================================== アジェンダ====================================================================== 今回のメルマガでは以下の内容をお伝えします。 1) データファイルとページ 2) データベースとデータファイル・ページ・ページの種類・ページとエクステントの関係・テーブルとページの関係 4) データファイルとエクステント====================================================================== データファイルとページ ======================================================================SQL ServerでもOracleでも、データはデータファイルに格納されていきます。そのデータファイルの中身はどうなっているんでしょうか?まず最初に、接続中のインスタンスが使っているファイルの一覧を取得し、表示します。<< SQL文 >>/********************************************************************/SELECT * FROM sys.master_files;/********************************************************************/# sys.master_filesについては MSDNライブラリ参照。# https://msdn.microsoft.com/ja-jp/library/ms186782.aspx<< 実行結果(抜粋) >>/********************************************************************/database_id file_id type type_desc name----------- ----------- ---- ---------- ------------------------------1 1 0 ROWS master1 2 1 LOG mastlog2 1 0 ROWS tempdev2 2 1 LOG templog3 1 0 ROWS modeldev3 2 1 LOG modellog4 1 0 ROWS MSDBData4 2 1 LOG MSDBLog5 1 0 ROWS ReportServer5 2 1 LOG ReportServer_log6 1 0 ROWS ReportServerTempDB6 2 1 LOG ReportServerTempDB_log7 1 0 ROWS test7 2 1 LOG test_log/********************************************************************/# 実行結果 https://www.insight-tec.com/mailmagazine/SQL_ref/sys.master_files.png/********************************************************************/この結果を見て分るように、・行データが入るファイル・ログデータが入るファイルの2種類のファイルが存在していることが分るかと思います。sys.master_filesが出力する列数は多く、様々な情報が入っていますのでファイルサイズと必要な情報がある程度見えるように、出力列を絞って、SQLを実行していきます。また、4つの列に対し、式を入れていきます。max_size列が-1の場合、ファイルの最大値は可能サイズまで増加するという"無制限"指定なので、max_size列が0以下の場合は、"無制限"と表示されるようにしました。growth列は0の場合、サイズの増減がない固定ファイルサイズになりますのでその場合には、"固定"と表示されるようにしました。そして、growth列の値はis_percent_growthの値が1の場合はパーセント(%)、それ以外はページ数を示していますので、is_percent_growth列の値を見て、growthの増え方がどちらなのか、"%"と"page(s)"という"単位"を付加して表示するようにしました。DB_NAMEという関数を使い、database_idに対応するデータベースの名前が表示されるようにしました。# MSDN https://msdn.microsoft.com/ja-jp/library/ms189753.aspxそれでは、組みあがったSQL文と実行結果を記載します。<< SQL文 >>/********************************************************************/SELECT DB_NAME(database_id) dbname, name, file_id, file_guid, physical_name, size, case when max_size < 0 then N'無制限' else CAST(max_size as nvarchar) end max_size, case when growth = 0 then N'固定' else CAST(growth as nvarchar) end + case when is_percent_growth = 1 then N'%' else N'page(s)' end growth FROM sys.master_files;/********************************************************************/<< 実行結果(抜粋) >>/********************************************************************/dbname physical_name ------ ------------------------------------------------------------master C:\SQL ServerData\MSSQL10.MSSQL Server\MSSQL\DATA\master.mdf master C:\SQL ServerData\MSSQL10.MSSQL Server\MSSQL\DATA\mastlog.ldf tempdb C:\SQL ServerData\MSSQL10.MSSQL Server\MSSQL\DATA\tempdb.mdf tempdb C:\SQL ServerData\MSSQL10.MSSQL Server\MSSQL\DATA\templog.ldf model C:\SQL ServerData\MSSQL10.MSSQL Server\MSSQL\DATA\model.mdf size max_size growth ----- -------- ---------- 512 無制限 10% 160 無制限 10% 1024 無制限 10% 64 無制限 10% 288 無制限 128page(s) /********************************************************************/# 実行結果 https://www.insight-tec.com/mailmagazine/SQL_ref/sys.master_files_change1.png/********************************************************************/ここから、結果の中にある1つのファイルをピックアップして情報を見ていきます。1行目にあるmasterというデータベースのsizeには"512"と出力されています。しかし、physical_nameで指定されているファイルのプロパティをみると、実際のファイルサイズは4,194,304 バイトです。# https://www.insight-tec.com/mailmagazine/SQL_ref/master.mdf.pngでは、SQLの結果で出力された"512"は何を示しているのでしょうか？sys.master_filesの説明を見ると"size列は、現在のファイルサイズ (8 KB ページ単位) "とあります。よってこの512は、「8KBのページ数が512個はいっています。」といっているわけです。試しに、先ほどのsize部分がバイトで出力されるように修正します。<< SQL文 >>/********************************************************************/SELECT DB_NAME(database_id) dbname, name, file_id, file_guid, physical_name, cast(size as decimal) *8*1024 [size], case when max_size < 0 then N'無制限' else CAST(max_size as nvarchar) end max_size, case when growth = 0 then N'固定' else CAST(growth as nvarchar) end + case when is_percent_growth = 1 then N'%' else N'page(s)' end growth FROM sys.master_files;/********************************************************************/<< 実行結果(抜粋) >>/********************************************************************/dbname physical_name ------ ----------------------------------------------------------- master C:\SQL ServerData\MSSQL10.MSSQL Server\MSSQL\DATA\master.mdf master C:\SQL ServerData\MSSQL10.MSSQL Server\MSSQL\DATA\mastlog.ldf tempdb C:\SQL ServerData\MSSQL10.MSSQL Server\MSSQL\DATA\tempdb.mdf tempdb C:\SQL ServerData\MSSQL10.MSSQL Server\MSSQL\DATA\templog.ldf model C:\SQL ServerData\MSSQL10.MSSQL Server\MSSQL\DATA\model.mdf size max_size growth------- -------- ----------4194304 無制限 10%1310720 無制限 10%8388608 無制限 10%524288 無制限 10%2359296 無制限 128page(s)/********************************************************************/# 実行結果 https://www.insight-tec.com/mailmagazine/SQL_ref/sys.master_files_change2.png/********************************************************************/これで、ファイルのプロパティで表示されていたファイルサイズとSQL出力結果のsizeが一致し、SQL Serverのページが8KBである事を実感できたかと思います。====================================================================== データベースとデータファイル・ページ======================================================================先ほどまでの検証にて、データファイルの中には"ページ"というものが存在している事を感じていただけたかと思います。今度はそのページとは何かをみていきます。事前準備として、使用するデータベースとテーブルを作成します。<< SQL文 >>/********************************************************************/USE [master]GOCREATE DATABASE mini ON PRIMARY ( NAME = N'miniPri', FILENAME = N'C:\SQL ServerData\miniPri.mdf' , SIZE = 3MB , FILEGROWTH = 1KB),FILEGROUP miniSec DEFAULT ( NAME = N'miniSec', FILENAME = N'C:\SQL ServerData\miniSec.mdf' , SIZE = 512KB , FILEGROWTH = 1KB)LOG ON ( NAME = N'log', FILENAME = N'C:\SQL ServerData\mini.ldf' )GoUSE [mini]GOCREATE TABLE [tb1]( [id] [decimal](18, 0) NOT NULL, [col1] [nvarchar](4000) NULL, [col2] [nvarchar](max) NULL, [col3] [nvarchar](max) NULL, CONSTRAINT [pktb1] PRIMARY KEY CLUSTERED ( [id] ASC )ON [miniSec]) ON [miniSec]GO/********************************************************************/ここでさらっと"エクステント"について記述します。詳しくは、これ以降に記載しますが、CREATE DATABASEで指定するFILEGROWTHの値は、『指定したサイズを、最も近い 64 KB 単位の値に切り上げた数値』が実際の設定値として適用されます。領域の増加は"エクステント単位"で行われるのですが、8ページ=1エクステント、1ページ=8KBと決まっているからです。よって、増加の最小値が 8KB * 8 = 64KB になる・・・ということを暗に示しています。さて、話を元に戻しまして、作成しただけの状態で、このデータベースのサイズを調べてみます。次のSQLはデータベースに割り当てられたデータ領域を表示するSQLです。<< SQL文 >>/********************************************************************/USE [mini]GOSELECT *FROM sys.data_spaces;/********************************************************************/# MSDN https://msdn.microsoft.com/ja-jp/library/ms190289.aspx<< 実行結果 >>/********************************************************************/name data_space_id type type_desc is_default------- ------------- ---- -------------- ----------PRIMARY 1 FG ROWS_FILEGROUP 0miniSec 2 FG ROWS_FILEGROUP 1/********************************************************************/この結果により、CREATE DATABASEのON以下で指定した"PRIMARY"というファイルグループと"miniSec"というファイルグループが表示されます。次に、このデータベースのアロケーションユニットの一覧を取得してみます。# アロケーションユニットについてはMSDNライブラリ参照# MSDN https://msdn.microsoft.com/ja-jp/library/ms189051.aspx<< SQL文 >>/********************************************************************/USE [mini]GOSELECT *FROM sys.allocation_units;/********************************************************************/# MSDN https://msdn.microsoft.com/ja-jp/library/ms189792.aspx<< 実行結果(抜粋) >>/********************************************************************/allocation_unit_id type type_desc container_id ------------------ ---- ----------------- ------------------72057594039828480 1 IN_ROW_DATA 72057594038779904 72057594039894016 3 ROW_OVERFLOW_DATA 72057594038779904 72057594039959552 2 LOB_DATA 72057594038779904 data_space_id total_pages used_pages data_pages ------------- ----------- ---------- ---------- 2 0 0 0 2 0 0 0 2 0 0 0/********************************************************************/このSQL結果は100件強出力されているかと思います。この出力結果が、ページの集合体であるアロケーションユニットの一覧結果になるのですが、これだけを見ていても、情報がよく見えないと思いますので、SQLを改変し、実行します。<< SQL文 >>/********************************************************************/USE [mini]GOSELECT sys.allocation_units.allocation_unit_id, sys.allocation_units.type_desc, sys.allocation_units.total_pages, sys.allocation_units.used_pages, sys.allocation_units.data_pages, sys.partitions.object_id, OBJECT_NAME(sys.partitions.object_id) object_desc, sys.partitions.index_id, sys.partitions.rowsFROM sys.data_spaces join ( sys.allocation_units left outer join sys.partitions ON container_id = CASE WHEN sys.allocation_units.type % 2 = 1 THEN sys.partitions.hobt_id WHEN sys.allocation_units.type = 2 THEN sys.partitions.partition_id END ) ON sys.data_spaces.data_space_id = sys.allocation_units.data_space_idORDER BY object_id;/********************************************************************/<< 実行結果(抜粋) >>/********************************************************************/allocation_unit_id type_desc total_pages used_pages -------------------- ----------------- ----------- ----------- 72057594039828480 IN_ROW_DATA 0 0 72057594039894016 ROW_OVERFLOW_DATA 0 0 72057594039959552 LOB_DATA 0 0 data_pages object_id object_desc index_id rows---------- ----------- ----------- -------- -----0 2105058535 tb1 1 00 2105058535 tb1 1 00 2105058535 tb1 1 0/********************************************************************/これでどのオブジェクトが、どれぐらいの行数があり、どれぐらいページを使用しているかが分るようになりました。さらに突っ込んで、これをファイル/ファイルグループと繋げてみます。<< SQL文 >>/********************************************************************/USE [mini]GOSELECT sys.data_spaces.*, sys.allocation_units.allocation_unit_id, sys.allocation_units.type_desc, sys.allocation_units.total_pages, sys.allocation_units.used_pages, sys.allocation_units.data_pages, sys.partitions.object_id, OBJECT_NAME(sys.partitions.object_id) object_desc, sys.partitions.index_id, sys.partitions.rowsFROM sys.data_spaces join ( sys.allocation_units left outer join sys.partitions ON container_id = CASE WHEN sys.allocation_units.type % 2 = 1 THEN sys.partitions.hobt_id WHEN sys.allocation_units.type = 2 THEN sys.partitions.partition_id END ) ON sys.data_spaces.data_space_id = sys.allocation_units.data_space_idORDER BY object_id;/********************************************************************/<< 実行結果(抜粋) >>/********************************************************************/name data_space_id type type_desc is_default ------- ------------- ---- -------------- ---------- miniSec 2 FG ROWS_FILEGROUP 1 miniSec 2 FG ROWS_FILEGROUP 1 miniSec 2 FG ROWS_FILEGROUP 1 allocation_unit_id type_desc total_pages used_pages data_pages------------------ ----------------- ----------- ---------- ----------72057594039828480 IN_ROW_DATA 0 0 0 72057594039894016 ROW_OVERFLOW_DATA 0 0 0 72057594039959552 LOB_DATA 0 0 0 object_id object_desc index_id rows----------- ----------- -------- ----2105058535 tb1 1 02105058535 tb1 1 02105058535 tb1 1 0/********************************************************************/これで、ファイル/ファイルグループに格納されているページ数がいくつか、格納されているオブジェクトが何で、どれぐらいの行数あるのかが見えるようになりました。ここでテーブルに1行データをいれて、先ほどのSQLを再度実行してみたいと思います。<< SQL文 >>/********************************************************************/insert into tb1 values ( 1, null, null, null);/********************************************************************/<< 実行結果(抜粋) >>/********************************************************************/name data_space_id type type_desc is_default ------- ------------- ---- -------------- ---------- miniSec 2 FG ROWS_FILEGROUP 1 miniSec 2 FG ROWS_FILEGROUP 1 miniSec 2 FG ROWS_FILEGROUP 1 allocation_unit_id type_desc total_pages used_pages data_pages ------------------ ----------------- ----------- ---------- ---------- 72057594039828480 IN_ROW_DATA 2 2 172057594039894016 ROW_OVERFLOW_DATA 0 0 072057594039959552 LOB_DATA 0 0 0object_id object_desc index_id rows----------- ----------- -------- ----2105058535 tb1 1 12105058535 tb1 1 12105058535 tb1 1 1/********************************************************************/データを1行追加した事で、tb1のIN_ROW_DATAが使用しているページの数が2に増えました。行データで1ページ、nvarcharが格納されるページで1ページ使用しているので、計2ページの使用になります。さらに、1行追加して再実行します。<< SQL文 >>/********************************************************************/insert into tb1 values ( 2, null, null, null);/********************************************************************/ << 実行結果(抜粋) >>/********************************************************************/name data_space_id type type_desc is_default ------- ------------- ---- -------------- ---------- miniSec 2 FG ROWS_FILEGROUP 1 miniSec 2 FG ROWS_FILEGROUP 1 miniSec 2 FG ROWS_FILEGROUP 1 allocation_unit_id type_desc total_pages used_pages data_pages------------------ ----------------- ----------- ---------- ----------72057594039828480 IN_ROW_DATA 2 2 172057594039894016 ROW_OVERFLOW_DATA 0 0 072057594039959552 LOB_DATA 0 0 0object_id object_desc index_id rows----------- ----------- -------- ----2105058535 tb1 1 22105058535 tb1 1 22105058535 tb1 1 2/********************************************************************/今度は格納行数が2行に増えましたが、使用ページ数は増えていません。よって、1ページには複数行のデータが格納されていることが分ります。次に、tb1.col1に大きいバイト数のデータをUpdateをして、再実行します。<< SQL文 >>/********************************************************************/USE [mini]GOUPDATE tb1 SET col1 = REPLICATE(N'～', 4000);GOSELECT DATALENGTH(col1) col1_len FROM tb1WHERE id = 1; USE [mini]GOSELECT id, DATALENGTH(col1) col1_len FROM tb1; SELECT sys.data_spaces.*, sys.allocation_units.allocation_unit_id, sys.allocation_units.type_desc, sys.allocation_units.total_pages, sys.allocation_units.used_pages, sys.allocation_units.data_pages, sys.partitions.object_id, OBJECT_NAME(sys.partitions.object_id) object_desc, sys.partitions.index_id, sys.partitions.rowsFROM sys.data_spaces join ( sys.allocation_units left outer join sys.partitions ON container_id = CASE WHEN sys.allocation_units.type % 2 = 1 THEN sys.partitions.hobt_id WHEN sys.allocation_units.type = 2 THEN sys.partitions.partition_id END ) ON sys.data_spaces.data_space_id = sys.allocation_units.data_space_idORDER BY data_space_id;/********************************************************************/<< 実行結果(抜粋) >>/********************************************************************/name data_space_id type type_desc is_default ------- ------------- ---- -------------- ---------- miniSec 2 FG ROWS_FILEGROUP 1 miniSec 2 FG ROWS_FILEGROUP 1 miniSec 2 FG ROWS_FILEGROUP 1 allocation_unit_id type_desc total_pages used_pages data_pages------------------ ----------------- ----------- ---------- ----------72057594039828480 IN_ROW_DATA 4 4 272057594039894016 ROW_OVERFLOW_DATA 0 0 072057594039959552 LOB_DATA 0 0 0object_id object_desc index_id rows----------- ----------- -------- ----2105058535 tb1 1 22105058535 tb1 1 22105058535 tb1 1 2/********************************************************************/今度はtb1.col1のバイト数8,000バイト×2になったので、1ページ(8KB)に格納きしれず、tb1のIN_ROW_DATAが使用しているページ数が増えました。同様にtb1.col2に大きいバイト数のデータをUpdateをして、再実行します。<< SQL文 >>/********************************************************************/UPDATE tb1 SET col2 = REPLICATE(N'～', 4000);USE [mini]GOSELECT id, DATALENGTH(col1) col1_len, DATALENGTH(col2) col2_len FROM tb1; SELECT sys.data_spaces.*, sys.allocation_units.allocation_unit_id, sys.allocation_units.type_desc, sys.allocation_units.total_pages, sys.allocation_units.used_pages, sys.allocation_units.data_pages, sys.partitions.object_id, OBJECT_NAME(sys.partitions.object_id) object_desc, sys.partitions.index_id, sys.partitions.rowsFROM sys.data_spaces join ( sys.allocation_units left outer join sys.partitions ON container_id = CASE WHEN sys.allocation_units.type % 2 = 1 THEN sys.partitions.hobt_id WHEN sys.allocation_units.type = 2 THEN sys.partitions.partition_id END ) ON sys.data_spaces.data_space_id = sys.allocation_units.data_space_idORDER BY data_space_id;/********************************************************************/<< 実行結果(抜粋) >>/********************************************************************/name data_space_id type type_desc is_default ------- ------------- ---- -------------- ---------- miniSec 2 FG ROWS_FILEGROUP 1 miniSec 2 FG ROWS_FILEGROUP 1 miniSec 2 FG ROWS_FILEGROUP 1 allocation_unit_id type_desc total_pages used_pages data_pages------------------ ----------------- ----------- ---------- ----------72057594039828480 IN_ROW_DATA 4 4 272057594039894016 ROW_OVERFLOW_DATA 0 0 072057594039959552 LOB_DATA 3 3 0object_id object_desc index_id rows----------- ----------- -------- ----2105058535 tb1 1 22105058535 tb1 1 22105058535 tb1 1 2/********************************************************************/今度はtb1.col2のバイト数8,000バイト×2になったので、1ページ(8KB)に格納きしれず、tb1のLOB_DATAが使用しているページ数が増えました。これは、tb1.col1 = nvarchar(4000) と定義されているのに対して、tb1.col2 = nverchar(max)と定義されている違いです。max指定をするとLOBと同じ様にデータが扱われ、LOBとしてページが確保されることが分ります。同様にtb1.col3の値も同じ様にUpdateすると、結果が以下のようになります。<< 実行結果(抜粋) >>/********************************************************************/name data_space_id type type_desc is_default ------- ------------- ---- -------------- ---------- miniSec 2 FG ROWS_FILEGROUP 1miniSec 2 FG ROWS_FILEGROUP 1miniSec 2 FG ROWS_FILEGROUP 1allocation_unit_id type_desc total_pages used_pages data_pages------------------ ----------------- ----------- ---------- ----------72057594039828480 IN_ROW_DATA 4 4 272057594039894016 ROW_OVERFLOW_DATA 3 3 072057594039959552 LOB_DATA 5 5 0object_id object_desc index_id rows----------- ----------- -------- ----2105058535 tb1 1 22105058535 tb1 1 22105058535 tb1 1 2/********************************************************************/この様に、ページには複数行格納されているものもあれば、複数ページで1つのデータを格納しているものもあります。このあたりの詳細はMSDNを参照すると、より理解できるかと思います。ページとエクステントについて MSND https://msdn.microsoft.com/ja-jp/library/ms190969.aspx参照：8 KB を超える場合の行オーバーフローデータMSND https://msdn.microsoft.com/ja-jp/library/ms186981.aspx====================================================================== データファイルとエクステント======================================================================ここで話を最初のデータファイルの方に戻します。このminiというデータベースを作成し、tb1というテーブルを作成、レコード2件登録、col1, col2, col3とUpdateしてきました。ここまでの状態で、mini.dbo.tb1がデータを格納するために使用しているファイルのサイズは524,288 バイトになっていました。この状態から、データをこのままで1件ずつ増やしていきたいと思います。<< SQL文 >>/********************************************************************/INSERT INTO tb1SELECT id + 1, col1, col2, col3 FROM tb1 WHERE id = ( SELECT MAX(id) FROM tb1 );/********************************************************************/このSQLを何度も発行し、最初にデータの増減があった17行目でデータファイルのサイズやデータベースのページ数を取得してみます。<< SQL文 >>/********************************************************************/SELECT sys.data_spaces.*, sys.allocation_units.allocation_unit_id, sys.allocation_units.type_desc, sys.allocation_units.total_pages, sys.allocation_units.used_pages, sys.allocation_units.data_pages, sys.partitions.object_id, OBJECT_NAME(sys.partitions.object_id) object_desc, sys.partitions.index_id, sys.partitions.rowsFROM sys.data_spaces join ( sys.allocation_units left outer join sys.partitions ON container_id = CASE WHEN sys.allocation_units.type % 2 = 1 THEN sys.partitions.hobt_id WHEN sys.allocation_units.type = 2 THEN sys.partitions.partition_id END ) ON sys.data_spaces.data_space_id = sys.allocation_units.data_space_idORDER BY data_space_id;SELECT DB_NAME(database_id) dbname, name, file_id, file_guid, physical_name, cast(size as decimal) *8*1024 [size], case when max_size < 0 then N'無制限' else CAST(max_size as nvarchar) end max_size, case when growth = 0 then N'固定' else CAST(growth as nvarchar) end + case when is_percent_growth = 1 then N'%' else N'page(s)' end growth FROM sys.master_files;/********************************************************************/<< 実行結果(抜粋) >>/********************************************************************/name data_space_id type type_desc is_default ------- ------------- ---- -------------- ---------- miniSec 2 FG ROWS_FILEGROUP 1miniSec 2 FG ROWS_FILEGROUP 1miniSec 2 FG ROWS_FILEGROUP 1allocation_unit_id type_desc total_pages used_pages data_pages------------------ ----------------- ----------- ---------- ----------72057594039828480 IN_ROW_DATA 4 4 272057594039894016 ROW_OVERFLOW_DATA 25 18 072057594039959552 LOB_DATA 41 35 0object_id object_desc index_id rows----------- ----------- -------- ----2105058535 tb1 1 172105058535 tb1 1 172105058535 tb1 1 17======================================================================dbname physical_name size max_size growth------ ---------------------------- ------- -------- ----------mini C:\SQL ServerData\miniSec.mdf 655360 無制限 8page(s)/********************************************************************/ファイルサイズが524288バイトから655360バイトへ増加しました。これによって増加したページ数は128ページ、増加したエクステント数は16エクステントだと分ります。(655360 - 524288)/8192 = 16ページ16 / 8 = 2 エクステント次はINSERTのデータを少なくして、増加をみてみます。<< SQL文 >>/********************************************************************/INSERT INTO tb1SELECT id + 1, col1, null, nullFROM tb1 WHERE id = ( SELECT MAX(id) FROM tb1 );/********************************************************************/このSQLを何度も発行し、最初にデータの増減があった状態は以下のようになります。<< 実行結果(抜粋) >>/********************************************************************/name data_space_id type type_desc is_default ------- ------------- ---- -------------- ---------- miniSec 2 FG ROWS_FILEGROUP 1miniSec 2 FG ROWS_FILEGROUP 1miniSec 2 FG ROWS_FILEGROUP 1allocation_unit_id type_desc total_pages used_pages data_pages------------------ ----------------- ----------- ---------- ----------72057594039828480 IN_ROW_DATA 7 7 572057594039894016 ROW_OVERFLOW_DATA 25 18 072057594039959552 LOB_DATA 41 35 0object_id object_desc index_id rows----------- ----------- -------- ----2105058535 tb1 1 202105058535 tb1 1 202105058535 tb1 1 20dbname physical_name size max_size growth------ ---------------------------- ------- -------- ----------mini C:\SQL ServerData\miniSec.mdf 720896 無制限 8page(s)/********************************************************************/ファイルサイズが655360バイトから720896バイトへ増加しました。これによって増加したページ数は8ページ、増加したエクステント数は1エクステントだと分ります。これまでの、データ増加は1ページより大きいデータを追加してきました。では、1ページ未満のデータを数件いれて増加量を見てみようと思います。<< SQL文 >>/********************************************************************/INSERT INTO tb1SELECT ( SELECT MAX(id) FROM tb1 ) + 1, REPLICATE(N'0',1000), null, null;/********************************************************************/このSQLを何度も発行し、最初にデータの増減があった状態は以下のようになります。<< 実行結果(抜粋) >>/********************************************************************/name data_space_id type type_desc is_default ------- ------------- ---- -------------- ---------- miniSec 2 FG ROWS_FILEGROUP 1miniSec 2 FG ROWS_FILEGROUP 1miniSec 2 FG ROWS_FILEGROUP 1allocation_unit_id type_desc total_pages used_pages data_pages------------------ ----------------- ----------- ---------- ----------72057594039828480 IN_ROW_DATA 17 10 872057594039894016 ROW_OVERFLOW_DATA 25 18 072057594039959552 LOB_DATA 41 35 0object_id object_desc index_id rows----------- ----------- -------- ----2105058535 tb1 1 292105058535 tb1 1 292105058535 tb1 1 29dbname physical_name size max_size growth------ ---------------------------- ------- -------- ----------mini C:\SQL ServerData\miniSec.mdf 786432 無制限 8page(s)/********************************************************************/ ファイルサイズが720896バイトから786432バイトへ増加しました。これによって増加したページ数は8ページ、増加したエクステント数は1エクステントだと分ります。なぜ、この8ページで増加するのかというと、CREATE DATABASEで指定したFILEGROWTH=1KBがSQL Serverによって、1KBから最も近い64 KB 単位の値に切り上げられて増加するからです。よって、ファイルサイズの最小増加は8ページ単位になります。====================================================================== まとめと考察====================================================================== ・データベースにはデータが格納されるデータファイルと、ログが格納されるログファイルとを持っている。 -> これによって、SQL Serverはデータベースごとにログファイルを持つ構造になっていることがわかる。よって、インスタンス毎ではなく、データベース毎のバックアップ & 復旧が行われる必要がある。・データベースはページという単位を持っていてページはデータファイルの中に書きこまれる最小単位である。また、ページにはテーブルのデータが格納されていて、 1ページに複数行格納されている。ただし、8KBを超えるデータの場合、ページをまたがってデータが格納される。 -> 1ページは8KBという制限があるので、列長を8KBよりも大きくするときは、性能面での考慮が必要。 (ページ毎にバッファへのデータ入出力が行われるため) ・データが格納できるページが無くなると、ファイルの拡張が発生する。また、データファイルは最小64KB単位で増えていく。よって、8ページ=1エクステント単位で増えていく。 -> SQL Serverのデータファイルを格納するディスクがデータ増加により、知らない間に圧迫されないか気を払う必要がある。特に、無制限拡張をしている場合は使えるだけどんどんファイルサイズが増加するので注意が必要。・データファイルには、ファイルとグループの2種類がある。 -> ファイルグループを使い、実ファイルを複数ディスクに分散させることが出来る。┏─────────────────────────────────┓┃ご質問について ┃┗─────────────────────────────────┛＜皆様からのご質問を受付けております＞皆様のご質問にはできるだけ、お答えしたいと思っています。すべてのご質問にお答えすることはできないかもしれませんが、適宜メルマガ内でとりあげていく予定ですので、是非お気軽に下記アドレスまでお寄せください。ご意見、ご感想などもお待ちしておりますっ！！wlmailhtml:{FFB7FEE3-FD66-4A13-AECC-755EBDF825BF}mid://00000000/!x-usc:mailto:letter\@insight-tec.co.jp┏─────────────────────────────────┓┃編集者より ┃┗─────────────────────────────────┛初めまして！！この度は、どっぷりリファレン中をご購読いただきまして、本当に！本当に！ありがとうございますっ♪初回はいかがでしたでしょうか？？感想＆ご質問等ありましたらご遠慮なくお知らせいただけると、とても、とても、嬉しいですっ！！不定期の配信となりますが、次回の配信を首をながーくしてお待ちくださいね☆ by TI━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━＜えす！エス！レスキュー SQL Server -どっぷりリファレン中！！-＞発行／編集：株式会社インサイトテクノロジーhttps://www.insight-tec.comwlmailhtml:{FFB7FEE3-FD66-4A13-AECC-755EBDF825BF}mid://00000000/!x-usc:mailto:letter\@insight-tec.co.jp本メールマガジンに掲載された記事を許可なく転載することを禁じます。Copyright(c) 2009, Insight Technology, Inc., All Rights Reserved.━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━※本メールマガジンは株式会社インサイトテクノロジーより許諾を受けて、紹介のために掲載しています。

[Sample] えす！エス！レスキュー SQL Server -どっぷりリファレン中！！- Vol. 1

Additional resources