August 31, 2018 at 11:27 am
I am trying to limit IOPS for my index jobs, however I am unable to verify if it is really working.. how can I verify that.
I am using ola scripts https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
USE [master]
GO
/****** Object: ResourcePool [SQLJob_pool_low_iops] Script Date: 8/30/2018 2:48:02 PM ******/
alter RESOURCE POOL [SQLJob_pool_low_iops] WITH(min_cpu_percent=0,
max_cpu_percent=50,
min_memory_percent=0,
max_memory_percent=50,
cap_cpu_percent=50,
AFFINITY SCHEDULER = AUTO
,
min_iops_per_volume=50,
max_iops_per_volume=75)
GO
/****** Object: WorkloadGroup [WL_SQLJob_pool_low_iops] Script Date: 8/30/2018 2:47:43 PM ******/
CREATE WORKLOAD GROUP [WL_SQLJob_pool_low_iops] WITH(group_max_requests=5,
importance=Medium,
request_max_cpu_time_sec=0,
request_max_memory_grant_percent=25,
request_memory_grant_timeout_sec=0,
max_dop=1) USING [SQLJob_pool_low_iops]
GO
create FUNCTION dbo.sqljob_workload_low_iops_Classifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @GroupName sysname = NULL;
IF SUSER_SNAME() = 'sqljob_workload_low_iops' --and left(app_name(),23) ='SQLAgent - TSQL JobStep'
SET @GroupName = N'WL_SQLJob_pool_low_iops';
IF @GroupName IS NULL SET @GroupName = N'default';
RETURN @GroupName;
END
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.sqljob_workload_low_iops_Classifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
CREATE LOGIN [sqljob_workload_low_iops] WITH PASSWORD=N'whateveryourwant', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
-- can not use sysadmin as it will use sqlagent proxy , use db_owner instead
exec sp_msforeachdb 'use [?];begin try CREATE USER [sqljob_workload_low_iops] FOR LOGIN [sqljob_workload_low_iops] end try begin catch end catch begin try ALTER ROLE [db_owner] ADD MEMBER [sqljob_workload_low_iops] end try begin catch end catch'
go
GRANT VIEW SERVER STATE TO [sqljob_workload_low_iops] --need this so we can identify who is running the job
go
----------- create operator as create job script needs it
USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'oncall',
@enabled=1,
@pager_days=0,
@email_address=N'oncall@somewhere.com'
GO
----------- CREATE REBUILD/REINDEX JOBS SCRIPT
declare @dn varchar(max)
declare @cmd varchar(max)
declare @cmd1 varchar(max)
/* -- set cleanup to 1 if you want to delete jobs that are created by this script */
declare @cleanup int =0
declare @new_job_name varchar(max)='DBA - Reindex Database '
declare mycursor cursor for select cast(name as varchar(max)) from sysdatabases where dbid> 4
open mycursor
fetch next from mycursor into @dn
while @@FETCH_STATUS=0
begin
set @new_job_name = 'DBA - Reindex Database '+ @dn
if @cleanup=1
begin
print 'deleting job ' +@new_job_name
EXEC msdb.dbo.sp_delete_job @job_name=@new_job_name
-- Delete Alerts
end
set @cmd='
exec master.[dbo].[IndexOptimize]
@databases = ''' + @dn + ''',
@FragmentationLow = ''INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationMedium = ''INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationHigh = ''INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = ''ALL'',
@OnlyModifiedStatistics = ''Y'',
@SortInTempdb=''Y'',
@MaxDOP=1'
set @cmd1='SELECT UserName = CONVERT(nvarchar(30), SUSER_SNAME())
, [SYSTEM_USER] = CONVERT(nvarchar(30), SYSTEM_USER)
, [SESSION_USER] = CONVERT(nvarchar(30), SESSION_USER)
, [ORIGINAL_LOGIN] = CONVERT(nvarchar(30), ORIGINAL_LOGIN())
, WorkloadGroup = CONVERT(nvarchar(30), wg.name),app_name() Application_name
FROM sys.dm_exec_requests der
INNER JOIN sys.dm_resource_governor_workload_groups wg ON der.group_id = wg.group_id
WHERE der.session_id = @@SPID'
if @cleanup=0
begin
print 'Creating job ' + @new_job_name
exec msdb.dbo.sp_add_job @job_name=@new_job_name, @owner_login_name=N'sqljob_workload_low_iops', @notify_email_operator_name=N'oncall',@notify_level_email=2
print 'Creating job Step reindex for '+ @new_job_name
exec msdb.dbo.sp_add_jobstep @step_name=N'whoisrunning', @job_name=@new_job_name,
@step_id=1,
@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=@cmd1,
@database_name=N'master',
@flags=4
exec msdb.dbo.sp_add_jobstep @step_name=N'reindex', @job_name=@new_job_name,
@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=@cmd,
@database_name=N'master',
@flags=4
print 'Associaate job with Server '
exec msdb.dbo.sp_add_jobserver @job_name=@new_job_name, @server_name = N'(local)'
EXEC msdb.dbo.sp_update_jobstep @job_name=@new_job_name, @step_id=1 ,
@on_success_action=3
end
fetch next from mycursor into @dn
end
close mycursor
deallocate mycursor
September 5, 2018 at 1:50 pm
no one Really?
September 6, 2018 at 12:47 am
Hi,
what about performance-counter? You should be able to monitor the peak of your re-index task, with and without limitation.
Maybe not the perfekt way to check this, but it is easy so use and simple to configure.
best regards,
Andreas
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply