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
  13:  
  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

   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
  13:  
  14: END
  15:  
  16: DECLARE @jobId BINARY(16)
  17: select @jobId = job_id from msdb.dbo.sysjobs where (name = N'list all databases')
  18: if (@jobId is NULL)
  19: BEGIN
  20: EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'list all databases', 
  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 [list all databases step]    Script Date: 1/19/2011 11:35:25 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'list all databases step', 
  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: (dir SQLSERVER:\SQL\$(ESCAPE_NONE(SRVR))$a\Databases)', 
  47:         @database_name=N'master', 
  48:         @flags=32
  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:  

 

Start both jobs. Check job history for both jobs You would note that Job #2 execution is delayed until Job #1 completes.