CREATE PROCEDURE prMemusageRecord
AS
CREATE TABLE #memusagerecord
(
dbid int,
objectid int,
indexid int,
Buffers int,
Dirty int,
)
INSERT INTO #memusagerecord EXEC(‘DBCC MEMUSAGE’)
INSERT INTO MemusageRecord
(dbid, objectid, indexid, Buffers, Dirty)
SELECT dbid, objectid, indexid, Buffers, Dirty
FROM #memusagerecord
DROP TABLE #memusagerecord
GO
This procedure now needs to be scheduled. I’ve included a script to add a SQL Server Agent job to do this, but you could do it another way, or simply run the procedure manually. If you wish to add an agent job to do this, you should adjust the schedule of the job to your own requirements. The job I have been using captures the data every minute during a fixed period during the working day. Here is the script to create it (this script assumes that prMemusageRecord has been created in the pubs sample database):
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N’Server Maintenance’) < 1
EXECUTE msdb.dbo.sp_add_category @name = N’Server Maintenance’
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N’Capture Memory Usage’) > 0
PRINT N’The job “Capture Memory Usage” already exists so will not be replaced.’
ELSE
BEGIN
— Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N’Capture Memory Usage’, @owner_login_name = N’sa’, @description = N’No description available.’, @category_name = N’Server Maintenance’, @enabled = 0, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
— Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N’Capture Memory Usage’, @command = N’exec prMemusageRecord’, @database_name = N’pubs’, @server = N”, @database_user_name = N”, @subsystem = N’TSQL’, @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N”, @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
— Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N’every minute’, @enabled = 1, @freq_type = 4, @active_start_date = 20041115, @active_start_time = 90000, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 170000
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
— Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Once you have captured that data (or possibly while it is still being captured), you will want to analyze it. I have included a simple stored procedure to help with this, which only looks at the most recent values.
This procedure needs to be modified to point to your own database location of the table MemusageReport: in this example, I’ve put it in the sample pubs database. This procedure could be modified to take the database location of the table as a parameter.