ストアド プロシージャの自動実行
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 で確認ができますので、実行されたかどうかはこちらから確認をするとよいかと思います。