I have a SQL Server 2016 instance with around 30 db's. I want to equally distribute resources between them and I use Resource Governor for it. So, I created ~30 pools with the same settings, and my classifier function now looks like this: case db1: pool1, case db2: pool2 and so on. You can see obvious problems with this setup - it requires manual work while adding, dropping or renaming databases, and I will occasionally bump into 64 max pools restriction. How can I modify my classifier function so it will search for the least loaded pool?
The function currently looks like the sample below. I use
SUSER_NAME because almost all client interaction with databases is done by one user at the database level.
CREATE FUNCTION [dbo].[MyClassifierFunction]() RETURNS SYSNAME WITH SCHEMABINDING AS BEGIN DECLARE @PoolName SYSNAME; SET @PoolName = CASE SUSER_NAME() WHEN N'db1' THEN 'pool1' WHEN N'db2' THEN 'pool2' ... ELSE'default' END RETURN @PoolName; END