SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server でメモリが不足するとどのようなことが起きる – データキャッシュ –

2 comments

twitter で SQL Server Express Edition では 1GB のメモリ制限があるが実際どのような影響があるかという話題がでました。

データキャッシュについてになりますがどのような影響が出るかを軽くまとめてみたいと思います。

今回は以下のように 4.6GB 程度のデータを格納したテーブルを用意しています。

image

このテーブルを使用して以下のようなクエリーを実行してみます。

SET STATISTICS IO ON
SET NOCOUNT ON
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS 
DBCC FREEPROCCACHE WITH NO_INFOMSGS
exec sp_configure ‘max server memory (MB)’, 2147483647
reconfigure
GO

DECLARE @ProcTime datetime

SET @ProcTime = GETDATE()
SELECT COUNT(*) FROM Table_1
SELECT CONVERT(time, GETDATE() – @ProcTime, 108) AS 全件取得

DECLARE @tmp uniqueidentifier
SET @ProcTime = GETDATE()
SELECT @tmp = Col1 FROM Table_1 TABLESAMPLE (1000 ROWS)
SELECT CONVERT(time, GETDATE() – @ProcTime, 108) AS サンプリング取得

今回の環境は 32GB のメモリを搭載したサーバーとなります。
その環境に対してバッファプールを最大限まで取得できるように設定しています。

最初に SELECT COUNT(*) を使用して全権取得を実施し、そのあとにサンプリングしてデータの取得を実施し、各処理の時間を取得しています。

実行の結果がこちらになります。
image

初回の全件取得には 17 秒程度かかっていますが、その後のサンプリング取得では 1 秒以下の実行になっています。
この時の I/O の状態の情報が以下になります。
image

初回の件数取得の SELECT は先行読み取りによる物理 I/O が発生していますが、2 回目のサンプリングによる SELECT ではすべてのデータがキャッシュに載っているため論理読み取りのみが発生しています。

クエリを以下のように変更して、Express Edition の上限である 1GB にメモリを設定するようにしてクエリを実行してみます。

SET STATISTICS IO ON
SET NOCOUNT ON
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS 
DBCC FREEPROCCACHE WITH NO_INFOMSGS
exec sp_configure ‘max server memory (MB)’, 1024
reconfigure
GO

DECLARE @ProcTime datetime

SET @ProcTime = GETDATE()
SELECT COUNT(*) FROM Table_1
SELECT CONVERT(time, GETDATE() – @ProcTime, 108) AS 全件取得

DECLARE @tmp uniqueidentifier
SET @ProcTime = GETDATE()
SELECT @tmp = Col1 FROM Table_1 TABLESAMPLE (1000 ROWS)
SELECT CONVERT(time, GETDATE() – @ProcTime, 108) AS サンプリング取得

このクエリを実行した場合は、以下のような実行結果になります。
image

image

先ほどとは異なりサンプリングの SELECT では先行読み取りによる物理 I/O が発生していることが確認できます。

テーブルのデータが 4.6GB に対して、データのキャッシュとしては 1GB しか利用できないため、全件取得した際にすべてのデータをメモリ上にキャッシュしておくことができません。
そのためサンプリングによる SELECT を実行した際には、ディスクからのデータ読み取りが行われ、ディスク I/O が発生しています。

今回のテーブルはヒープ構造のため以下のようなクエリを実行してデータの圧縮をしてみます。

ALTER TABLE Table_1 REBUILD WITH(DATA_COMPRESSION=PAGE)

今回は無駄な空白文字を使用することでデータサイズを増やしていましたのでページ圧縮すると大幅にデータサイズが減少します。

image
この状態でクエリを実行すると 1GB のメモリでもすべてのデータがキャッシュできますので、メモリが 1G でも速度が速く実行することができます。
# Express Edition ではデータの圧縮はできませんが…。
image

ディスクの I/O を早くすると非圧縮でも以下の速度は出せたりしますが。
# 安価な PCI-e のストレージと RAM Drive を使った場合の 4.6GB のデータ取得の速度になります。
imageimage

データベースのサイズ = メモリサイズになっていればすべてのデータがキャッシュできますので、高速にアクセスができますがそうなっていない場合はディスク I/O が発生してきますのでメモリの量によってこのあたりの I/O に差が発生してくるかと。

Written by masayuki.ozawa

1月 15th, 2013 at 12:59 am

Posted in SQL Server

Tagged with

2 Responses to 'SQL Server でメモリが不足するとどのようなことが起きる – データキャッシュ –'

Subscribe to comments with RSS or TrackBack to 'SQL Server でメモリが不足するとどのようなことが起きる – データキャッシュ –'.

  1. SQL Server でメモリが不足するとどのようなことが起きる – データキャッシュ – « SE の雑記…

    素敵なエントリーの登録ありがとうございます – .NET Clipsからのトラックバック…

    .NET Clips

    15 1月 13 at 22:50

  2. […] SQL Server のデータベースを格納した場合にも表れてきます。 以前、SQL Server でメモリが不足するとどのようなことが起きる – データキャッシ… […]

    RocketCache と SQL Server を組み合わせて使ってみました « SE の雑記

    20 1月 13 at 13:43

Leave a Reply

*