1

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
  • Pool loads will change, though, right? If someone adds 10 databases in 5 minutes, wouldn't they all go into the currently least-loaded pool, and then you'd suddenly have a wild imbalance? Are you sure you're really solving a problem here? – Brent Ozar Sep 25 '17 at 6:49
  • @Volodymyr Kot When someone launches ALL the queries from master like this: select * from db1.dbo.tbl1 join db2.dbo.tbl2 to what pool should it be assigned? To "master"-pool, to "db1"-pool or to "db2"-pool? – sepupic Sep 25 '17 at 7:18
  • @Brent Ozar That is definitely not the case here, it won't happen. Getting over the maximum of 64 pools is what bothers me the most, not the manual setup of this function. #sepupic In my case, I think I don't care because only dbas are launching queries from the master, all those dbs are a backend for web applications. – user134400 Sep 25 '17 at 8:31
  • What does "least loaded pool" mean? The pool with the least number of users assigned to it? – Joe Obbish Sep 25 '17 at 23:17
  • @JoeObbish Yeah, this is exactly the case. I don't see calculating resource consumption in classifier function as a good idea. – user134400 Sep 26 '17 at 4:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy