SQL Server Resource Governor

  • Comments posted to this topic are about the item SQL Server Resource Governor

  • Thanks for the article.

  • ...and I love the avatar by the way.

  • You could very well be tuning for the wrong thing here. What I want to see is the DURATION of all those batch/maintenance/SSAS jobs. Did things process more quickly in total or less quickly? With the servers not being accessed except by batch jobs I want my CPUs running at 100% where possible as long as that makes all jobs finish more quickly in total.

    Also, at virtually every client I do performance reviews at they have overlapping jobs, sometimes tens of them running at the same time! There are 60 seconds per minute, 60 minutes per hour and X hours in your maintenance window. Use them!! Spread out the batch activities and you can see total runtimes reduce by an order of magnitude in some cases!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • TheSQLGuru (4/26/2016)


    You could very well be tuning for the wrong thing here. What I want to see is the DURATION of all those batch/maintenance/SSAS jobs. Did things process more quickly in total or less quickly? With the servers not being accessed except by batch jobs I want my CPUs running at 100% where possible as long as that makes all jobs finish more quickly in total.

    Also, at virtually every client I do performance reviews at they have overlapping jobs, sometimes tens of them running at the same time! There are 60 seconds per minute, 60 minutes per hour and X hours in your maintenance window. Use them!! Spread out the batch activities and you can see total runtimes reduce by an order of magnitude in some cases!

    Soon as I throttled the CPU assigned to those ridiculous multiple processing SSAS jobs the "day end " processes started to run more to the time frame that we wanted and user experience improved slightly too (as there were users online) at the expense of longer run time for the OLAP stuff.... that's what I found anyways.

  • Glad to hear it was a win all around. I just wanted to point out that is often isn't. 🙂

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • You do know that if you are going to run SSAS and the database instance on the same server their are instance configurations that you need to make regarding SSAS and SQL Server such that you don't get into this contention issue. Things like memory allocation, thread pooling, etc. You may end up squeezing one instance or the other such that performance issues come back. I also agree that duration of the CPU utilization is a more relevent measure. I want my CPU doing 100% it means I don't have idle resources I just don't want any service pegging it at 100% for a long time.

  • I want my CPU doing 100% it means I don't have idle resources I just don't want any service pegging it at 100% for a long time.

    totally agree.

  • TheSQLGuru (4/26/2016)


    Glad to hear it was a win all around. I just wanted to point out that is often isn't. 🙂

    Appreciate your comments Kevin. Thanks.

  • Thanks for the article, Raul. I especially like what I saw on the PerfMon chart in the "Results" section.

    Not much to add because Kevin beat me too it. The only thing that I'll add is that some well meaning folks throttle code when what they really should be doing is fixing it to run better, faster, and with less resource contention. Really cool that it worked out well for your situation because that's the whole intent. 😀

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Jeff Moden (4/26/2016)


    Thanks for the article, Raul. I especially like what I saw on the PerfMon chart in the "Results" section.

    Not much to add because Kevin beat me too it. The only thing that I'll add is that some well meaning folks throttle code when what they really should be doing is fixing it to run better, faster, and with less resource contention. Really cool that it worked out well for your situation because that's the whole intent. 😀

    what you say is so true, I was even close to withdrawing the article as I can see what I have done as slightly cheeky - but yeah still waiting on those performance fixes 😉 in the mean time at least there is a slight element of QoS.

    thanks jeff.

  • nice article on enabling RG

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Perry Whittle (4/27/2016)


    nice article on enabling RG

    thanks perry

  • BLOB_EATER (4/27/2016)


    Jeff Moden (4/26/2016)


    Thanks for the article, Raul. I especially like what I saw on the PerfMon chart in the "Results" section.

    Not much to add because Kevin beat me too it. The only thing that I'll add is that some well meaning folks throttle code when what they really should be doing is fixing it to run better, faster, and with less resource contention. Really cool that it worked out well for your situation because that's the whole intent. 😀

    what you say is so true, I was even close to withdrawing the article as I can see what I have done as slightly cheeky - but yeah still waiting on those performance fixes 😉 in the mean time at least there is a slight element of QoS.

    thanks jeff.

    Oh no... don't feel that way. I'm tickled that you didn't withdraw the article. It shows a good working example of what can be done when there are conflicts in priorities during ad hoc "emergency" runs. This is going to be very useful and I appreciate the time you took to write it.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Jeff Moden (4/27/2016)


    BLOB_EATER (4/27/2016)


    Jeff Moden (4/26/2016)


    Thanks for the article, Raul. I especially like what I saw on the PerfMon chart in the "Results" section.

    Not much to add because Kevin beat me too it. The only thing that I'll add is that some well meaning folks throttle code when what they really should be doing is fixing it to run better, faster, and with less resource contention. Really cool that it worked out well for your situation because that's the whole intent. 😀

    what you say is so true, I was even close to withdrawing the article as I can see what I have done as slightly cheeky - but yeah still waiting on those performance fixes 😉 in the mean time at least there is a slight element of QoS.

    thanks jeff.

    Oh no... don't feel that way. I'm tickled that you didn't withdraw the article. It shows a good working example of what can be done when there are conflicts in priorities during ad hoc "emergency" runs. This is going to be very useful and I appreciate the time you took to write it.

    Appreciate the feedback. I would like to write some more this year but wow does it take up a lot of my spare time... which at the moment is non-existent with 2 little kids running around.

    😛

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

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