September 15, 2009 at 3:00 am
Hi,
I try to classify SQL Server Agent workloads in Resource Governor, but I am not able to figure out how to do it. The step type is "Transact-SQL script (T-SQL)":
Alt 1:
Is there any way to control the app name of an SQL job in order to retrive it in the classifier function by the use of APP_NAME()?
Alt 2:
I have tried to run the actual agent job under a dedicated user, but the SUSER_NAME() always returns the user name of the SQL agent user at the time of classification. My Agent runs under user "A" and the job step runs under user "B", but at time of classification the user is still "A". If a check the user by T-SQL in the job step the user is "B". This is strange, the user context must change somehow.
Any other ideas?
Can anyone please help me out here?
September 15, 2009 at 3:43 pm
You will need to classify based on the job name, and no you cannot customize what appears under APP_NAME() - I thought I filed a Connect item for this but could not find it. You can hardcode the job name into the classifier function, or you can use a lookup table. I wrote some code to help with this in the whitepaper:
September 16, 2009 at 7:47 am
Thanks,
Page 23 of your whitepaper solves my problem I think. I have to use a lookup table since I cannot hardcode the 0x binary values since I am in an OEM product in which those can vary on different installations. I will try to use a lookuptable and update the lookup table in master frequently to catch new jobs that is to be classified.
Again, thank you!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply