How can I limit CPU usage per user in SQL 2005?

  • Hi all

    We've got a user that needs the ability to write and execute SQL code against our server.

    However, she often writes hefty inefficient queries that cane the machine.

    I know in SQL 2008 Katmai there is a Query Resource Governor that allows you to limit CPU usage by user or role, but how could I implement an equivalent on 2005?

    How about a TSQL wrapper I could put around her code that could restict the number of available CPUS for her SPID to 1?

    The machine is an HP DL 585, 4 x Dual Core AMD Opteron x64, 16GB RAM.

    Running Windows Server 2003 x64 R2 , SQL Server 2005 x64 SP2

    Cheers

    Rich

  • You could insist that she adds OPTION (MAXDOP 1) to the end of each of her queries. Not ideal, but it might work.

    You can use the governor that's in SQL 2005 to prevent expensive queries from running at all.

    As a longer term 'fix' maybe suggest to this person that she gets some training in writing more optimal SQL

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gail

    To clarify your solution would be to run something like this in the same query window?

    (I would have thought the setting would need to be above her TSQL code?

    execute uspHogTheServer

    OPTION (MAXDOP 1)

    So would the SPID she is on will only hog CPU0?

    I have 4 processors with dual cores which show up in SQL as CPu0 to CPU7.

    Would it grab one of the processors with i.e CPU0 and CPU1?

    Or would it just grab one of the processor cores CPU0?

    Cheers

    Rich

  • No. It's a query hint. It must be applied to a query, not a storedProc. It only applies to the query it's part of.

    SELECT Lots, of, columns

    FROM ReallyBigTable

    WHERE BadlyWrittenWhereClause = 1

    OPTION (MAXDOP 1)

    A query written as such will use one processor, whichever one the connection ends up running on. No way to say which one

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • The stored procedures she is writing mostly consist of

    create proc uspHogTheServer as

    select * INTO #tmpHumongous from Massive

    INSERT INTO #tmpHumongous

    select * from Gigantic

    SELECT * FROM #tmpHumongous

    go

    So I would get her to write something like :

    Alter proc uspHogTheServer as

    select * INTO #tmpHumongous from Massive option (maxdop 1)

    INSERT INTO #tmpHumongous

    select * from Gigantic option (maxdop 1)

    SELECT * FROM #tmpHumongous option (maxdop 1)

    go

    ????????

  • Yup. Petty much.

    I'd definitly suggest you see if she can get some SQL training, formally or informally. How long's it going to be before she does something like write an update/delete and forget the where clause? (Or does she just have select rights?)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • To be fair to her she is working with a lot of data and she should have her own datawarehouse environment.

    This is not possible at the moment.

    Even if the queries and procs were well writen SQL server would still allow her to consume a lot of server resources.

    Many thanks for your kind help

    Richard

  • What about creating optimized views for her to query against?

    Just a thought.

    Greg

  • that's a good idea.

    also, trace her queries and see if you can add some indexes to help her out.

    my guess is that this person is querying the same stuff most often.

    long term is to get a reporting db for her and other end users to query against AND to teach her and others how to write efficient queries (get rid of those massive temp tables).

    Greg J

    Gregory A Jackson MBA, CSM

  • You can't give her a local copy of the DB? Disk is very, very cheap now. US$1000 for 1TB.

    If not, there's no good resource governer in 2005 other than Gail's suggestion if the queries need to run.

  • you could install a second instance of sql2005 and use Windows system resource manager (WSRM)

    to govern CPU usage %.

    WSRM is also available for x64 !

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • you'd be better off just launching a new thread.

    but since we're at it.

    afaik you can only sample and guestimate.

    Keep in mind connections come and go...

    fairly common usage of sysprocesses ....

    e.g.

    if (object_id('tempdb..#tmpVerbruik') is null)

    begin

    -- tmptabelleke aanmaken

    select min( @@Servername ) as ServerName

    , A.hostname

    , A.loginame

    , A.program_name

    , min(login_time) as min_login_time, max(last_batch) as max_last_batch , sum(cpu) as sum_cpu, sum(physical_io) as sum_physical_io, sum(memusage) as sum_memusage

    --, login_time, last_batch , cpu, physical_io, memusage

    into #tmpVerbruik

    FROM master..sysprocesses A

    --WHERE loginame = 'theoneandonly'

    group by A.hostname, A.loginame, A.program_name

    end

    select A1.*

    --, T.min_login_time

    --, T.max_last_batch

    --, T.sum_cpu

    --, T.sum_physical_io

    --, T.sum_memusage

    , A1.sum_cpu - T.sum_cpu as Delta_cpu

    , A1.sum_physical_io - T.sum_physical_io as Delta_physical_io

    , A1.sum_memusage - T.sum_memusage as Delta_memusage

    , A1.sum_physical_io - T.sum_physical_io as Delta_physical_io

    , A1.sum_memusage - T.sum_memusage as Delta_memusage

    from

    (select min( @@Servername ) as ServerName

    , A.hostname

    , A.loginame

    , A.program_name

    , min(login_time) as min_login_time, max(last_batch) as max_last_batch , sum(cpu) as sum_cpu, sum(physical_io) as sum_physical_io, sum(memusage) as sum_memusage

    --, login_time, last_batch , cpu, physical_io, memusage

    -- into #tmpVerbruik

    FROM master..sysprocesses A

    --WHERE loginame = 'theoneandonly'

    group by A.hostname, A.loginame, A.program_name

    ) A1

    left join #tmpVerbruik T

    on A1.hostname = T.hostname

    and A1.loginame = T.loginame

    and A1.program_name = T.program_name

    where T.sum_cpu <> A1.sum_cpu

    or T.sum_physical_io <> A1.sum_physical_io

    or T.sum_memusage <> A1.sum_memusage

    or T.sum_physical_io <> A1.sum_physical_io

    or T.sum_memusage <> A1.sum_memusage

    order by Delta_CPU desc, A1.hostname, A1.loginame, A1.program_name

    go

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Hi,

    Just trawling the site looking for another CPU usage issue and came across this thread.

    I have a boss who write very clever but very large queries and then pesters the life out of me to explain why they take so long, 40 minutes plus.

    Not long ago the light dawned on him; build the records up in small inserts and suddenly his query took 3 minutes. Get the programmer to insert in relatively small batches which can be done quite easily if the table has an identity column.

    Also the line select * INTO #tmpHumongous from Massive fills me with dread and could actually lock the tempdb. Much better to do

    select * INTO #tmpHumongous from Massive where 1=0

    NSERT INTO #tmpHumongous

    Select * from Massive preferably in a loop as shown below.

    If you have an identity column with an index then its even better to.

    Create table #ids(id_column int)

    Declare @ id_column int

    Declare @ id_column_offset int

    Select @ id_column = starting id

    Select @ id_column_offset = no of rows to walk

    /* by adjusting @ id_column_offset you should be able to tune both queries to use an index seek and minimal resources */

    While @ id_column < @max_id_value

    Begin

    Truncate table #ids

    Insert into #ids (id_column) select id_column from Massive where Massive between @ id_column and @ id_column + @ id_column_offset

    Insert into #tmpHumongous

    Select * from Massive Mas

    Join #ids id on Mas. id_column = id. id_column

    Select @ id_column = @ id_column + @ id_column_offset

    End

  • Yuckon (2/6/2008)


    The stored procedures she is writing mostly consist of

    create proc uspHogTheServer as

    select * INTO #tmpHumongous from Massive

    INSERT INTO #tmpHumongous

    select * from Gigantic

    SELECT * FROM #tmpHumongous

    go

    Why don't you consider

    SELECT * FROM Massive

    UNION ALL

    SELECT * FROM Gigantic

    No need for any temporary tables in that case. Or am I missing something here?

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • Ronald Cartmale (6/25/2008)


    select * INTO #tmpHumongous from Massive where 1=0

    You don't need to do that... that problem was solved way back in 6.5.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply