SE の雑記

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

Max Server Memory の設定を自動実行されるストアドプロシージャで設定

leave a comment

ストアド プロシージャの自動実行

Azure の仮想マシン上で実行している SQL Server のような環境で Max Server Memory を設定しておりスケールアップをする際に、搭載されているメモリを元に自動的に Max Server Memory を設定することで、スケールアップ時の Max Server Memory の変更を意識しないでもよいかなと思ってサンプルを作ってみました。

ストアドプロシージャの自動実行については ストアド プロシージャの自動実行 を参考にしていただければと思います。

今回は以下のようなストアドプロシージャを自動実行させたいと思います。

use [master]
GO
CREATE PROCEDURE usp_SetMaxServerMemory
AS
    DECLARE @pmemory int = (SELECT physical_memory_kb / 1024 from sys.dm_os_sys_info)
	DECLARE @maxservermemory int = @pmemory - 2048
	if @maxservermemory < 0
		SET @maxservermemory = 2147483647
	EXEC sp_configure 'show advanced options', 1
	RECONFIGURE WITH OVERRIDE
	DECLARE @sql nvarchar(max) = N'sys.sp_configure N''max server memory (MB)'',' + CONVERT(nvarchar(max), @maxservermemory)
	EXECUTE (@sql)
	RECONFIGURE WITH OVERRIDE
GO

サーバーに搭載されている物理メモリのサイズについては、sys.dm_os_sys_info から取得することができます。

今回のサンプルでは物理メモリ ? 2GB (OS や SQL Server のフットプリントを考慮) を Max Server Memory として設定しています。

このストアドプロシージャを master データベースに作成したら以下の SQL を実行し、自動実行されるストアドプロシージャとして登録します。

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'scan for startup procs', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_procoption 'usp_SetMaxServerMemory', 'startup', 'on'

 

自動実行されるストアドプロシージャは以下のクエリで確認できます。

SELECT * FROM sys.procedures WHERE is_auto_executed = 1

 

ストアドプロシージャが実際に実行されたかは、ERRORLOG で確認ができますので、実行されたかどうかはこちらから確認をするとよいかと思います。

image

Share

Written by Masayuki.Ozawa

8月 15th, 2014 at 8:47 am

Posted in SQL Server

Tagged with

Leave a Reply