Re: limit sql agent job resource with resource governor
- From: "Aaron Bertrand [SQL Server MVP]" <ten.xoc@xxxxxxxxxxxxxx>
- Date: Thu, 28 May 2009 12:24:40 -0400
Another thing I'll mention (and it's part of the best practices section of
the forthcoming whitepaper) is that you should try to avoid standalone IF
conditions that assign workload group names. This makes it too easy to meet
one, and then meet another one further down, which you might not anticipate.
I would much rather see:
SELECT @workload_group_name = CASE
WHEN ... THEN ...
WHEN ... THEN ...
ELSE 'default'
END
This way, no matter how many of your conditions are met, only the first one
is ever used - and you can rank them in priority. So imagine you have one
condition that says, all users running Management Studio go into group A.
Before that, you have one that says
IF USER_NAME() = 'ceo'
SET @workload_group_name = 'group A';
IF APP_NAME() LIKE '%studio'
SET @workload_group_name = 'group B';
Now your CEO happens to be using Management Studio, he/she will end up in
group B, which could get you fired if it is not what you expected. If you
had used CASE then the second test would never have been attempted. This is
obviously better for debugging and logic purposes, but also helps
performance slightly. Optimizing the performance of this function is
critical and anything you can do to prevent unnecessary work will be better
for you in the long run.
A
On 5/28/09 11:40 AM, in article #olegq63JHA.3860@xxxxxxxxxxxxxxxxxxxx,
"James" <kush@xxxxxxxxxxx> wrote:
Still doesn't make sense because I could see that the username is correct
throughout the time the job is running. I run the DMV query multiple times
during the job run and still see the same login.
If the job were to connect with one user in the begining and change after it
starts executing, then, I should see different user from the DMV query in
the middle or end of job run right? Well, I am not seeing that behaiour, the
username stays the same throughout the job run.
Anyway, As you guys suggested, I will explore different options like
app_name() etc. If you have a sample classifier function code that uses
app_name() specifically for sql agent job, I will appreciate it if you share
that info. Once again thanks a bunch for your time.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@xxxxxxxxxxxxxx> wrote in message
news:C64402BE.2C7E9%ten.xoc@xxxxxxxxxxxxxxxxx
Definitely, the resource governor classification happens long before the
user/session can execute any commands. It goes:
Authentication -> logon triggers -> classification -> ok now let the
session
do its work (including "execute as")...
As I suggested before, you should use some other way to identify the job
in
the classifier function. Hint: look at APP_NAME(); it has a unique
job_id.
A
On 5/28/09 1:45 AM, in article #EVeUe13JHA.1416@xxxxxxxxxxxxxxxxxxxx,
"Tibor
Karaszi" <tibor_please.no.email_karaszi@xxxxxxxxxxxxxxxxxx> wrote:
I think what is happening is that the classifier function is executed
before Agent executes the EXECUTE AS command. And not until later to
you run your query to see the login name. Again, I doubt you can use
the login name for this scenario - since Agent uses its own login and
not until *later* impersonates the selected login.
.
- References:
- limit sql agent job resource with resource governor
- From: James
- Re: limit sql agent job resource with resource governor
- From: Aaron Bertrand [SQL Server MVP]
- Re: limit sql agent job resource with resource governor
- From: Tibor Karaszi
- Re: limit sql agent job resource with resource governor
- From: James
- Re: limit sql agent job resource with resource governor
- From: Tibor Karaszi
- Re: limit sql agent job resource with resource governor
- From: Aaron Bertrand [SQL Server MVP]
- Re: limit sql agent job resource with resource governor
- From: James
- limit sql agent job resource with resource governor
- Prev by Date: Re: limit sql agent job resource with resource governor
- Next by Date: Re: how to make fieldcombination unique?
- Previous by thread: Re: limit sql agent job resource with resource governor
- Next by thread: SQL 2005 tools didn't upgrade to SP2
- Index(es):
Relevant Pages
|
