I read Considerations for Writing a Classifier Function on MSDN. The only way I see possible is to create a separate user account for each resource governor pool and to classify sessions based on that username.
Is there a better way?
Sql-server – Run SQL Server agent job in a particular resource governor pool
jobsresource-governorsql-server-2012
Related Question
- Sql-server – Resource Governor on 2014 – CPU Not Being Throttled
- Sql-server – Sql Server 2014 IO Resource Governor
- Sql-server – SQL Server 2014, Volumes in Resource Governor
- Sql-server – CheckDB on Partitioned Table – Insufficient Memory in resource Pool default
- Sql-server – SQL Server – SQL Agent – Allow a Group to own a job
- Sql-server – SQL Server Resource governor user-defined resource pools
- Sql-server – Using Resource Governor with SQL Agent Jobs
- Sql-server – How to Locate Resource System Database in MS SQL Server
Best Answer
Yes, your classifier function can identify the job that's running through APP_NAME(), which will actually contain a representation of the job_id. This means your jobs don't have to run as different users, but the classifier function does need to be able to identify jobs by id.
See the white paper I wrote back in 2008 for an example, in the section "Limiting Parallelism for Intensive Background Jobs." In that case I simply created a table with the mapping to the set of job_ids I cared about, and set the workload group in those cases.