Resource governor: How to write a classifier function?

Question

Dear colleagues,

Our management wants a start screen with tiles for certain tasks. Each tile must show a number. The user (or an application manager or super user) must have the option of writing a query that produces a number to be shown on the tile. The number can represent the sales offers that need to be completed by the user who is logged in, or the average price of all purchase orders that are still not paid, or whatever. Because these numbers can change, they must be refreshed every now and then. Users can define up to 16 tiles.

My first worry is that when you have 16 tiles per user and, say 150 users, there are 2,400 queries that are fired rather often. And since everything needs to be flexible, a super user can think up any performance wise catastrophic query he or she likes. I would like to make use of the resource governor to make sure these queries cannot clog the server. Since I never worked with the resource governor, I do not yet now what I can do in a classifier function to make a distinction between different sessions. I am busy reading the blog posts http://kevine323.blogspot.com/2011/11/resource-governor-practical-example.html and https://blog.sqlauthority.com/2012/06/04/sql-server-simple-example-to-configure-resource-governor-introduction-to-resource-governor/ so I already saw that you can use the application name. Could anyone point me in the direction of a list of possibilities? As I understand it, the classifier function decides which resource pool to assign on the basis of a session.

My current idea is to create some kind of "cache" table with the queries. The table will have columns for the number returned by the query, the time a refresh of this number is requested and the time the calculation of the number was finished. In this way, users will not directly issue the queries. I see a number of possible ways to initiate the calculation of tile numbers that need to be refreshed.

1. A SQL Server Agent job that runs periodically, but I do not know if I can pinpoint a specific job in the classifier function.
   You can pinpoint the SQL Server Agent user, but in that case all agent jobs will run in the context of the "low priority" resource pool.
2. A broker service with its own queue and queue reader proc, but I do not know if I can pinpoint a specific stored procedure execution in the classifier function.
3. We could write an application (say in C#) that periodically initiates the recalculation of the tile numbers that need to be refreshed.
   We can give this program its own SQL Server user. I am sure there are ways of tracking down the program name and/or the user of a session in the classifier function.

Maybe there are other possibilities. Please feel free to share them with me.

Kind regards,
Chris Sijtsma

Answer 1

In case you want to investigate Resource Governor further... I do agree with Erland's statement, though!

The classification is done at connect time. So whatever you want to use need to be available at this stage. Here's a copy&paste from an RS demo I have, where I listed functions that you can call from the classifier functions:

--ORIGINAL_DB_NAME(), HOST_NAME(), APP_NAME(), SUSER_NAME(), 
--SUSER_SNAME(), IS_SRVROLEMEMBER(), IS_MEMBER(),  LOGINPROPERTY()
--CONNECTIONPROPERTY()

Answer 2

Hm, unless you have changed jobs recently, I believe that you work with an application that you sell to customers.

Resource Governor is something you set up locally on a server. It seems odd to me that a vendor would configure Resource Governor.

Now, if you are providing the application as SaaS, so that you host the application on your server (or a server you have control over), it's a different matter. But I wanted to check what your situation is before you go too far.

I have never used Resource Governor myself, so I cannot really answer that part.

Answer 3

Hi Erland,

You are right, our application suite runs at local servers at the customer site. It is an ERP solution for companies that produce "one of a kind" products specifically engineered for their customers. I thought I could script the whole thing. And if we use a pool with minimal resources, I thought these queries would be slow, but normal queries would hardly have less resources. But as I understand you, this isn't the way to go.

Another idea is to kill these "number queries" if they run to long (say over a second) and/or to let them run with MAX_DOP = 1 (but in that case they still can grab all cache/tempdb/IO band width). And also, you need a mechanism to no which spid to KILL and make sure this spid is still used for a "number query". If the "number query" is ready just after you look up the spid, and this spid is given to another process, you kill the wrong process.

I also thought about using "SET LOCK_TIMEOUT <x>" and using "<WITH NOLOCK>" for the number queries.

What I really would like to do (and already did) is telling the management this isn't a good idea. But they didn't take no for an answer.

What would you recommend?

By the way, thank you for coming to the rescue again.

Answer 4

I forgot to mention that we support the Standard and Enterprise Editions of SQL Server 2014, 2016, 2017 and 2019.
Since we support the Standard edition, the resource governor solution is not possible.
Also, you both convinced me that apart from not being possible, it is not a good idea, also.

Should I close this question and post a new one?

Answer 5

I looked a little more closer on what you want to achieve, and it is a bit similar to what we do in the system I work with, there there is a dashboard shown on the home page for the unit you are looking at - and there are quite a few of those. Then again, that is a fairly simple case, since we only import transactions once a night, so we can do one single refresh during the day.

I don't see what SET LOCK_TIMEOUT or NOLOCK would buy you. Your concern rather seems to be that users writes queries from hell which drains the server of resources.

But if you control the execution of these from a C# layer, you could run them with a query timeout and give up some time. The default query timeout is 30 seconds, but you may want to set that lower.

An idea is also is that if a query times out, you may simple opt to disable it. Now, that could be unfair if they experience blocking, but if you run with some sort of snapshot isolation, blocking is less likely.