In the first part of the capturing current workload of SQL Server blog, I talked about first two steps. Where we created the table “CurrentlyRunningQueries_Snapshot_History” in the first step and wrote the script to capture workload for a live system in the second step. Now, we are going to create and schedule a job which will capture current workload of the system periodically as well as archive the historical data.

We shall be exploring both the options – GUI and script to create the job. To demonstrate, I am using master system database. But, you have to select that database where you want to keep the data.

Step-3:

• Create a new job

• Give the name of the job

• Create Step-1 for populating current workload data into the created table
  1. Go to the Steps tab
     
  2. Click on new option
     
  3. Give the name of the step
  4. Select the database where you have created the table
     
  5. Copy & paste the populating data script from previous post second step and replace the master database name with your database
  6. Click on OK

• Create Step-2 for archiving the historical data
  1. Go to the Steps tab
     
  2. Click on new option
     
  3. Give the name of the step
  4. Select the database where you have created the table
     
  5. Copy and paste the below script and replace the master database name with your database
  6. Click on OK

DELETE FROM master.dbo.CurrentlyRunningQueries_Snapshot_History
WHERE QuerySnapshotRuntime <=DATEADD(WEEK,-2,GETDATE());

Note: The script will be deleting two weeks older data from the table. You can modify it as per requirement.

• Schedule the job as per your requirement
  1. Go to the Schedules tab
     
  2. Click on the new option
  3. Give the name of the schedule
     
  4. Select the frequency of the schedule
     
  5. Select the occurrences of the schedule
     
  6. Select the Start date
  7. Select the End date
     
  8. Click on OK

• Give final “OK” to create the job
  

Here is the script which you can use to create the job in one shot but before you run the script, make sure you have replaced the master database name with your database name.

USE [msdb]
GO

/****** Object:  Job [CurrentlyRunningQueries_Snapshot_History]    Script Date: 14/11/2016 9:41:15 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 14/11/2016 9:41:15 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'CurrentlyRunningQueries_Snapshot_History', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Populating Data]    Script Date: 14/11/2016 9:41:15 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Populating Data', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

INSERT INTO master.dbo.CurrentlyRunningQueries_Snapshot_History -----Replace the master DB name with your DB name

SELECT
      dmes.session_id AS SPID,
	  dmer.start_time,
      UPPER (DB_NAME(dmer.database_id)) DBName,
      dmes.host_name AS HostName,
      UPPER(dmes.login_name) AS LoginName,
      UPPER(dmer.status) AS SPIDState,
      dmer.command as RequestCommand,
      dmer.total_elapsed_time AS ElapsedTime,
      dmer.cpu_time AS ElapsedTime,
      dmer.reads AS RequestRead,
      dmer.writes AS RequestWrites,
      dmer.logical_reads AS RequestLogicalRead,
      SUBSTRING (dmest.text, (dmer.statement_start_offset/2) + 1, 
	  ((CASE WHEN dmer.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), dmest.text)) * 2
      ELSE dmer.statement_end_offset  END - dmer.statement_start_offset)/2) + 1) AS SubQuery,
      UPPER(REPLACE(LTRIM(LTRIM(CONVERT(varchar(2000),REPLACE(REPLACE(REPLACE(dmest.text,''-'',''''),''='',''''),''*'','''')))),CHAR(9),'''')) AS ParentQuery,
      dmeqp.query_plan AS QueryPlan,
      dmer.wait_type AS Current_WaitType,
      dmer.wait_time AS Current_WaitTime,
      dmer.last_wait_type AS Last_WaitType,
      dmer.wait_resource AS RequestWaitResource,
      dmer.blocking_session_id AS RequestBlockingSPID,
      dmer.lock_timeout AS RequestTimeout,
      dmer.open_transaction_count OpenTransactionCount,
      dmer.row_count RowsCount,
	  dmer.percent_complete TaskCompletedPercent,
	  (dmes.memory_usage*8) AS Memory_UsagesInKB,
	  GETDATE()
FROM
      sys.dm_exec_sessions dmes
	  INNER JOIN  sys.dm_exec_requests dmer ON dmer.session_id = dmes.session_id
      CROSS APPLY sys.dm_exec_sql_text(dmer.sql_handle) dmest
      CROSS APPLY sys.dm_exec_query_plan(dmer.plan_handle) dmeqp
      LEFT JOIN   sys.dm_exec_cached_plans dmecp ON dmecp.plan_handle = dmer.plan_handle
      LEFT JOIN   sys.dm_db_task_space_usage dmtsu ON dmtsu.session_id = dmer.session_id and dmtsu.request_id = dmer.request_id
      LEFT JOIN   sys.dm_db_session_space_usage dmssu ON dmssu.session_id = dmes.session_id
WHERE dmes.session_id <> @@SPID
ORDER BY 11 DESC', 
		@database_name=N'master', -----Replace the master DB name with your DB name
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Archiving Historical data]    Script Date: 14/11/2016 9:41:15 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Archiving Historical data', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DELETE 
FROM master.dbo.CurrentlyRunningQueries_Snapshot_History -----Replace the master DB name with your DB name
WHERE QuerySnapshotRuntime <=DATEADD(WEEK,-2,GETDATE());', 
		@database_name=N'master', -----Replace the master DB name with your DB name
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'CurrentlyRunningQueries_Schd', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=4, 
		@freq_subday_interval=5, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20161114, 
		@active_end_date=20161130, 
		@active_start_time=0, 
		@active_end_time=235959, 
		@schedule_uid=N'34fb4096-6503-48a1-a180-05997d723c76'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO