kohera-logo-regular.svg

Scripting Resource Pools and Resource Groups

Unfortunately, SQL Server’s management studio doesn’t provide you with a simple way to script your resource pools and resource groups. If you want to do this you’ll first have to script all the groups, and then the pools one by one in the correct order. To recreate them you have to do the same but in a reverse order.
This SQL script does just that, it generates the scripts needed to recreate your resource groups and pools in the correct order. This is extremely useful in Always-on scenario’s as it provides you with a script that can be run on the primary node, and the result on any secondary node(s) creating a completely identical resource configuration for your resource governors.

The generated scripts can also be used as post scripts for application deployment or for documentation purposes. Please note that it does NOT script the configuration of your resource governor nor will it script your classification function. This is not within the scope of this script.
The difficulty for this script was to make the script generate the statements in the correct order, and to take into consideration the edition changes in the resource governor.

The script has two self-explaining parameters:

  • @Drop = Generates the drop statements
  • @Create = Generates the create statements

 

DECLARE @Drop bit=1
DECLARE @Create bit=1
DECLARE @CMD nvarchar(4000)
 
declare @version int
set @version = (SELECT convert (int,REPLACE (LEFT (CONVERT (varchar, SERVERPROPERTY ('ProductVersion')),2), '.', '')))
 
CREATE TABLE #Command (pk int identity(1,1) PRIMARY KEY,Command nvarchar(4000))
IF @Drop=1 
BEGIN
        IF NOT Exists (select * from tempdb.sys.objects where name like'#DROPS%' and type='U')
        BEGIN
                 CREATE TABLE #DROPS (Group_id int,Pool_id int,IsActive bit, Command nvarchar(4000))
        END
        ELSE BEGIN
                 Truncate table #DROPS
        END
        INSERT INTO #DROPS
        Select Group_id,Pool_id,1,
        'use [master]; DROP WORKLOAD GROUP  '+QUOTENAME(wg.name)+';' 
        FROM sys.resource_governor_workload_groups wg where group_id > 2
 
        INSERT INTO #DROPS
        Select 0,Pool_id,1,
        'use [master]; BEGIN TRY DROP RESOURCE POOL '+QUOTENAME(name)+' END TRY BEGIN CATCH END CATCH;'
        FROM sys.dm_resource_governor_resource_pools where Pool_id > 2
 
        INSERT INTO #Command Select Command From #Drops order by Pool_id asc,Group_id desc
END
 
IF @Create = 1 
BEGIN
        IF NOT Exists (select * from tempdb.sys.objects where name like'#CREATES%' and type='U')
        BEGIN
                 CREATE TABLE #CREATES (Group_id int,Pool_id int, Command nvarchar(4000))
        END
        SET @CMD='Select 0,Pool_id,''use [master]; BEGIN TRY CREATE RESOURCE POOL ''+QUOTENAME(name)+'' WITH (''+ 
                 ''min_cpu_percent=''+CAST(min_cpu_percent as varchar(3))+'', ''+
                 ''max_cpu_percent=''+CAST(max_cpu_percent as varchar(3))+'', ''+
                 ''min_memory_percent=''+CAST(min_memory_percent as varchar(3))+'', ''+
                 ''max_memory_percent=''+CAST(max_memory_percent as varchar(3))+'', ''+ '+
                 CASE WHEN @version > 10 THEN '''cap_cpu_percent=''+CAST(cap_cpu_percent as varchar(3))+'', ''+ 'ELSE '' END+
                 CASE WHEN @version > 11 THEN '''min_iops_per_volume=''+CAST(min_iops_per_volume as varchar(3))+'', ''+ 'ELSE '' END+
                 CASE WHEN @version > 11 THEN '''max_iops_per_volume=''+CAST(max_iops_per_volume as varchar(3))+'', ''+ 'ELSE '' END+
                 '''AFFINITY SCHEDULER = AUTO)''+'+
                 ''' END TRY BEGIN CATCH END CATCH ''' +
                 'FROM sys.dm_resource_governor_resource_pools where Pool_id > 2'
        
        PRINT @cmd
 
        INSERT INTO #CREATES
        exec sp_executesql @cmd
 
        INSERT INTO #CREATES
        Select wg.group_id,wg.pool_id,
        'use [master]; BEGIN TRY CREATE WORKLOAD GROUP '+QUOTENAME(wg.name)+' WITH (' +
        'group_max_requests='+CAST(group_max_requests as varchar(4))+', '+
        'importance='+importance+', '+
        'request_max_cpu_time_sec='+CAST(request_max_cpu_time_sec as varchar(5))+', '+
        'request_max_memory_grant_percent='+CAST(request_max_memory_grant_percent as varchar(5))+', '+
        'request_memory_grant_timeout_sec='+CAST(request_memory_grant_timeout_sec as varchar(5))+', '+
        'max_dop='+CAST(max_dop as char(2))+') USING '+QUOTENAME(rp.name)+' END TRY BEGIN CATCH END CATCH;'
        FROM sys.resource_governor_workload_groups wg 
        inner join  sys.dm_resource_governor_resource_pools  rp on rp.pool_id=wg.pool_id
        where wg.group_id > 2
 
        INSERT INTO #Command Select Command From #CREATES order by Pool_id desc,Group_id asc
END
GO
        Select * From #Command
GO
IF Exists (select * from tempdb.sys.objects where name like '#DROPS%' and type='U')
BEGIN
        DROP TABLE #DROPS
END
GO
IF Exists (select * from tempdb.sys.objects where name like '#CREATES%' and type='U')
BEGIN
        DROP TABLE #CREATES
END
GO
DROP TABLE #Command

Parameter sniffing solved with new Parameter Sensitive Plan Optimization feature

If you’re someone that works a lot with Microsoft SQL Server, there’s no doubt that you’ve had issues with an issue called “Parameter sniffing” before. Up until now, you had...

Creating maps with R and Power BI

The possibilities are infinite when it comes to creating custom visuals in Power BI. As long as you have creativity and knowledge about the right programming language, you can let...

Sending monitoring alerts through Telegram

What if you could get the ease of phone notifications for whatever monitoring alerts you need? Then we have a solution for you with the app Telegram. Some of you...

Send mails with Azure Elastic Database Jobs

The DatabaseMail feature in SQL Server and Managed Instance is widely used by many professionals. But what if you want a similar functionality in Azure SQL Database? There are options,...

Sorting matrices in Power BI

Recently I worked on a Power BI project for a client. They had a SharePoint site where they regularly published articles and wanted to pour view data into a report...

The world of data is evolving

The data landscape has changed dramatically over recent years. In the past, we mainly heard that we needed to do as much as possible “cloud-only”—but this trend has become more...