Run SQL Server agent job in a particular resource governor pool

Question

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?

Answer 1

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.