Re: limit sql agent job resource with resource governor

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thanks for the reply. The way I find out its using the right user/login is I
run below sql query during the run. What I see is, when its running from the
job, its actually using the right user (the user that I defined in
classifier function) but its group name says "default" rather than the one I
defined. I also double checked with perfmon counter and it shows cpu usage
only from default group and not the user defined group. I have included my
classfier function code below as well, its very simple (as I am still in
testing mode).
I have created a sql authenticated login/user called "compressionuser" and
thats what I used in sql agent job as a job owner and and also in "run as"
user in the advanced table of job step. So, by running following DMV query,
I can tell that the job is logged in as right user but for some reason not
going to right workload group bucket.
USE master;

SELECT sess.session_id, sess.login_name, sess.group_id, grps.name FROM
sys.dm_exec_sessions AS sess JOIN sys.dm_resource_governor_workload_groups
AS grps ON sess.group_id = grps.group_id WHERE session_id > 50;

GO

CREATE FUNCTION [dbo].[rgclassifier_BackupAndDataCompression]() RETURNS
sysname

WITH SCHEMABINDING

AS

BEGIN

DECLARE @workload_group_name AS sysname

IF (SUSER_NAME() = 'compressionuser')

SET @workload_group_name = 'BackupAndDataCompression_Group'

RETURN @workload_group_name

END;

GO



"Tibor Karaszi" <tibor_please.no.email_karaszi@xxxxxxxxxxxxxxxxxx> wrote in
message news:OOfzUBw3JHA.5816@xxxxxxxxxxxxxxxxxxxxxxx
My guess is that the problem is that Agent login as itself, regardless of
whoever one set as owner etc; and *then* use EXECUTE AS LOGIN = 'x'.
I.e., you can't use login name to differentiate. Use something else in the
classifier function.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Aaron Bertrand [SQL Server MVP]" <ten.xoc@xxxxxxxxxxxxxx> wrote in
message news:C642FAAC.1A7FD%ten.xoc@xxxxxxxxxxxxxxxxx
How are you identifying the job? Can you show your classifier function?
Or
at least that portion of it? Also did you verify via profiler or some
other
method that when the job kicks off it is running under the same login
that
you expect? For this reason (and also because all of my jobs are owned
by
the same login) I prefer to identify the job by name when I want to
constrain only one job (or even only a specific step, though I haven't
gotten around to testing that scenario yet). This can be a little
trickier
via classification, but certainly not impossible.






On 5/27/09 1:21 PM, in article #vIKe#u3JHA.5204@xxxxxxxxxxxxxxxxxxxx,
"James" <kush@xxxxxxxxxxx> wrote:

Hi! I am trying to limit resource used by SQL agent job and its not
working
for me. When the job kicks off, for some reason the process its not
going to
the workload group that I have defined and it goes to the default
workload
group. The only step job has is to run a backup via TSQL which does
compression as well. If I login via Query analyzer using the same login
that
is used to run the job then it goes to right Workload group. Its just
that
when its running from the sql agent job, for some reason, classifier
function is not placing that user on right group.
Has anyone run into this issue? I am running SQL 2008 Developer edition.







.

Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- table structure?
    ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... I have one report complete, ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)