Limit re-indexing IOPS

  • 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

  • no one Really?

  • 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