SQL Server Resource Governor

Question

Post reply

SQL Server Resource Governor

BLOB EATER

SSChampion

Points: 14609
More actions
April 26, 2016 at 12:04 am

#310191

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

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

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 1

akljfhnlaflkj

SSC Guru

Points: 76202

April 26, 2016 at 6:21 am

#1874537

Thanks for the article.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 2

akljfhnlaflkj

SSC Guru

Points: 76202

April 26, 2016 at 6:22 am

#1874539

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

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

BLOB EATER SSChampion Points: 14609 More actions · Answer 4

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.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

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

stan.geiger Right there with Babe Points: 727 More actions · Answer 6

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.

BLOB EATER SSChampion Points: 14609 More actions · Answer 7

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.

BLOB EATER SSChampion Points: 14609 More actions · Answer 8

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.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 9

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.

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

BLOB EATER SSChampion Points: 14609 More actions · Answer 10

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.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 11

nice article on enabling RG

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

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

BLOB EATER SSChampion Points: 14609 More actions · Answer 12

Perry Whittle (4/27/2016)
nice article on enabling RG

thanks perry

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 13

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.

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

BLOB EATER SSChampion Points: 14609 More actions · Answer 14

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.

😛