SQLStarters

Core responsibility of a DBA is to make sure that the databases they are responsible for are maintained properly. In a nutshell this usually means that you are taking backups of the data, checking the consistency of the databases and maintaining statistics and indexes. Performing these tasks gives the DBA the ability to provide the agreed SLAs for the system.

However the trade-off for doing all these tasks is that it requires some computing resources and it can sometimes impact the performance of the connecting applications, and through that the SLAs in a negative way.

In this post we’ll look at how to implement low impact database maintenance routines for environments that have large databases and are accessed 24/7 by the users.

The cost of maintenance tasks

Performing database maintenance will always require an amount of computing resources. Sometimes the need for resources can be significant enough to have an impact on SQL Server and its ability to service requests from applications. If you’re lucky enough you have a set maintenance window, and if you’re even luckier you have it often enough to satisfy all the maintenance needs.

If you’re looking after a growing data estate, then eventually the maintenance window can become too short. When that happens, you’ll need to find a way to perform the database maintenance without impacting the end users. Fortunately for us, there is a build-in mechanism in SQL Server that allows us to have some control over the CPU, memory and IOPS to setup a low impact database maintenance.

This mechanism is called the Resource Governor.

Ingredients for the low impact database maintenance

To make use of the Resource Governor there are few things we need to do first.

• Create dedicated login, preferably an AD account
• Create SQL Server Credential
• Create SQL Agent Proxy
• Resource Governor configuration
  • Create Resource Pool
  • Create Resource Group
  • Create Classifier Function
• Modify existing or creating new Maintenance Routines

Creating Login and Credentials

Once you have a AD account and you have successfully created a login out of it, you need to give it some permissions. Whilst it’s ideal to keep logins as low privileged as possible, things like CHECKDB do require you to be either db_owner or sysadmin. To accomplish login creation with the sysadmin privileges run the following two commands.

CREATE LOGIN [DOMAIN\User_Account] FROM WINDOWS;
ALTER SERVER ROLE [sysadmin] ADD MEMBER [DOMAIN\User_Account]

Replace the [DOMAIN\User_Account] with the actual domain and account information. The next step is to create the credential, this can be done using the following command.

CREATE CREDENTIAL [MaintenanceUser] WITH IDENTITY = 'DOMAIN\User_Account', SECRET = N'Account_Password_Here'

Note that the SECRET refers to the actual password of the user account you created for this purpose. We’re now ready to move to next step.

Creating the SQL Agent Proxy

Creating the SQL Agent Proxy is actually one of those things I prefer to do using SQL Server Management Studio GUI, rather than the Stored Procedure. To do this expand SQL Server Agent, right click Proxies folder and select New Proxy.

Give the Proxy a descriptive name and use the CREDENTIAL you created previously. The subsystem you want to use is Operating system (CmdExec).

Now that the Proxy has been created, we’re ready to move to next step.

Configuring Resource Governor

If you haven’t used Resource Governor previously, the first step is to enable it. This is done with the following command.

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Next we create the Resource Pool and then we create a Workload Group using that freshly created pool. This is done by running the following commands.

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
CREATE RESOURCE POOL db_maintenance_pool WITH (
	CAP_CPU_PERCENT = 20,
	MAX_CPU_PERCENT= 10);
GO
CREATE WORKLOAD GROUP db_maintenance_wg USING db_maintenance_pool;
GO

I am only limiting CPU, but you can also use Resource Governor to limit the amount of used memory and even the IOPS. Latter can be useful if you have some I/O heavy operations you wan’t to throttle. Running this creates a resource pool where the CPU is capped at 20% and when the system is under CPU pressure the requests using this pool are limited to 10%

After this, we still need to create a function to assign the user to workload group.

CREATE FUNCTION dbo.restrict_MaintenanceCPU
()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @workload_group_name AS sysname;
    IF (SUSER_NAME() = 'mytestingvm2\db_maintenance')
        SET @workload_group_name = 'db_maintenance_wg';
    RETURN @workload_group_name;
END;

This is rather simple function, it’ll check the user and if it’s [mytestingvm2\db_maintenance] then it’ll assign it to use the workload group called db_maintenance_wg.

The final thing to set for the Resource Governor is to register the classifier function and run reconfigure to apply all the configuration changes.

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.restrict_MaintenanceCPU);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

And now we have everything we need. You can find the Resource Governor objects from Management Studio under Management and Resource Governor.

The classifier function resides under master database.

We are now ready to create the maintenance routines that will make use of the Resource Governor. For this post, we’ll use Ola Hallengrens Integrity Check stored procedure as an example. The key is to be able to execute it using the Proxy account. The job will look like this, note that you must change the Type to Operating system (CmdExec).

The sqlcmd command we’re using is running the DatabaseIntegrityCheck stored procedure with only two parameters: @Databases and @LogToTable. You can of course use any parameter here, supported by the stored procedure.

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'USER_DATABASES', @LogToTable = 'Y'" -b

Make sure that you set the proper proxy to the Run as: field. We’re now ready to do some testing!

Monitoring Resource Governor

There are few different ways to monitor Resource Governor, but my favorite is this nice set of Custom Reports for Management Studio. Besides these reports you can also use PerfMon and few DMVs. However my recommendation is to download the reports.

After firing up the SQL Agent job we created earlier, we can definitely see that the Resource Governor is working.

Looking at the top of the report, a section called CPU Utilization by Resource Pool we can see that the db_maintenance_pool has reached the maximum allowed usage and that there is indeed a session in the db_maintenance_pool when we look at the Resource Pool Summary section.

When we click the link to drill, we’ll move to more detailed report.

Expanding the db_maintenance_wg, that is the workload group we created, we can see the DBCC CHECKDB we fired up earlier.

Wrapping it up

In this post I have shown you one way to create a low impact maintenance routines for large data estates. Personally I am using something similar to this in one 13TB environment and it has been working rather well.

One thing to note is that when you’re throttling your maintenance routines, they’ll be taking much more time than what you might have previously witnessed. When you start using Resource Governor like this, monitor the execution times carefully at first to establish a new baseline on how long they are running.