Resource governor in SQL Server can be used to limit resource consumption by specific application / user. Long running queries / tasks in SQL PowerShell might consume more SQL server resources. If you would like to set limits, you can do by enabling resource governor, define classifier function to assign sessions initiated from SQL PowerShell to specific workload group. This blog entry has a sample classifier function that looks for APP_NAME() like ‘sqlps’ and assigns that specific session to a workload group that has GROUP_MAX_REQUESTS = 1.
More details about Resource governor is available in http://msdn.microsoft.com/en-us/library/bb934084.aspx
1: --Note: You can also use SSMS UI to create resource pool and workload groups
2: -- Object explorer -> Management -> Resource Governor -> Properties
3: USE master;
4: -- create a resource pool to use max cpu 75% and max memory 75%
5: IF NOT EXISTS ( SELECT name FROM sys.resource_governor_resource_pools WHERE name = N'sqlagent_Powershell')
6: BEGIN
7: CREATE RESOURCE POOL [sqlagent_Powershell] WITH(min_cpu_percent=0,
8: max_cpu_percent=75,
9: min_memory_percent=0,
10: max_memory_percent=75)
11:
12: END
13:
14: GO
15: -- create workload group with max requests as 1 and importance as medium
16: IF NOT EXISTS ( SELECT name FROM sys.resource_governor_workload_groups WHERE name = N'sqlps')
17: BEGIN
18: CREATE WORKLOAD GROUP [sqlps] WITH(group_max_requests=1,
19: importance=Medium,
20: request_max_cpu_time_sec=0,
21: request_max_memory_grant_percent=25,
22: request_memory_grant_timeout_sec=0,
23: max_dop=0) USING [sqlagent_Powershell]
24: END
25:
26: GO
27:
28:
29: -- Create a classification function that assigns all sessions with app name like 'sqlps' to sqlps workloadgroup
30: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[rgclassifier_sqlagentPowershell]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
31: DROP FUNCTION [dbo].[rgclassifier_sqlagentPowershell]
32: GO
33: CREATE FUNCTION dbo.rgclassifier_sqlagentPowershell() RETURNS sysname
34: WITH SCHEMABINDING
35: AS
36: BEGIN
37: DECLARE @workload_group_name AS sysname
38: IF (APP_NAME() LIKE 'SQLPS%')
39: BEGIN
40: SET @workload_group_name = 'sqlps'
41: END
42: RETURN @workload_group_name
43: END;
44: GO
45:
46: -- Register the classifier function with Resource Governor.
47: ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_sqlagentPowershell);
48: GO
49:
50: -- reconfigfure Resource Governor
51: ALTER RESOURCE GOVERNOR RECONFIGURE;
52: GO
Test Jobs:
1) Create a power shell job to issue a query to block execution for 20 seconds
1: USE [msdb]
2: GO
3:
4:
5: BEGIN TRANSACTION
6: DECLARE @ReturnCode INT
7: SELECT @ReturnCode = 0
8:
9: IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
10: BEGIN
11: EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
12: IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
14: END
15:
16: DECLARE @jobId BINARY(16)
17: select @jobId = job_id from msdb.dbo.sysjobs where (name = N'waitfordelay20seconds')
18: if (@jobId is NULL)
19: BEGIN
20: EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'waitfordelay20seconds',
21: @enabled=1,
22: @notify_level_eventlog=0,
23: @notify_level_email=0,
24: @notify_level_netsend=0,
25: @notify_level_page=0,
26: @delete_level=0,
27: @description=N'No description available.',
28: @category_name=N'[Uncategorized (Local)]',
29: @job_id = @jobId OUTPUT
30: IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
31:
32: END
33: /****** Object: Step [waitfor 20 seconds delay] Script Date: 1/19/2011 11:33:56 AM ******/
34: IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 1)
35: EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'waitfor 20 seconds delay',
36: @step_id=1,
37: @cmdexec_success_code=0,
38: @on_success_action=1,
39: @on_success_step_id=0,
40: @on_fail_action=2,
41: @on_fail_step_id=0,
42: @retry_attempts=0,
43: @retry_interval=0,
44: @os_run_priority=0, @subsystem=N'PowerShell',
45: @command=N'if (''$(ESCAPE_SQUOTE(INST))'' -eq ''MSSQLSERVER'') {$a = ''\DEFAULT''} ELSE {$a = ''''};
46: (Get-Item SQLSERVER:\SQL\$(ESCAPE_NONE(SRVR))$a\Databases\master).ExecuteNonQuery("waitfor delay ''00:00:20''")',
47: @database_name=N'master',
48: @flags=0
49: IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
50: EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
51: IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
52: EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
53: IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
54: COMMIT TRANSACTION
55: GOTO EndSave
56: QuitWithRollback:
57: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
58: EndSave:
59:
60: GO
61:
62:
2) Create powershell job to list all databases
17: select @jobId = job_id from msdb.dbo.sysjobs where (name = N'list all databases')
20: EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'list all databases',
33: /****** Object: Step [list all databases step] Script Date: 1/19/2011 11:35:25 AM ******/
35: EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'list all databases step',
46: (dir SQLSERVER:\SQL\$(ESCAPE_NONE(SRVR))$a\Databases)',
48: @flags=32
Start both jobs. Check job history for both jobs You would note that Job #2 execution is delayed until Job #1 completes.