Thursday, March 24, 2016

Use SQL Server Resource Governor to throttle IO (slow down the backup)

It's practical to limit backup speed specially in VM environment, which could create IO storms during backup time. (Enterprise edition ONLY feature)

-- Create Windows user DEV-PC\MaintUser with password "Password", and grant full privilege on following folders:
-- \MSSQL\Log
--  Backup Dest Folders

USE [master]
GO
CREATE LOGIN [DEV-PC\MaintUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [DEV-PC\MaintUser]
GO


USE [master]
GO
CREATE CREDENTIAL [MaintUser] WITH IDENTITY = N'DEV-PC\MaintUser', SECRET = N'Password'
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'MaintUser',@credential_name=N'MaintUser', 
@enabled=1, 
@description=N'User to run maintenance job with IO Throttling'
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'MaintUser', @subsystem_id=11
GO


-- Create resource pool & workload groups
CREATE RESOURCE POOL MaintPlanPool
GO
CREATE WORKLOAD GROUP MaintPlanGroup using MaintPlanPool
GO

-- Create classifier function
CREATE FUNCTION fnUserClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
     IF ORIGINAL_LOGIN() = 'DEV-PC\MaintUser'
     BEGIN
          RETURN 'MaintPlanGroup'
     END

     RETURN 'default'
END
GO

-- Set the classifier function and enable RG
ALTER RESOURCE GOVERNOR 
  WITH (CLASSIFIER_FUNCTION = dbo.fnUserClassifier)
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

ALTER RESOURCE POOL MaintPlanPool 
  WITH (MIN_IOPS_PER_VOLUME=0, MAX_IOPS_PER_VOLUME=100)
ALTER RESOURCE GOVERNOR RECONFIGURE
GO