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

Question

Post reply

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

Viewing 4 posts - 16 through 18 (of 18 total)

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

Yuckon SSChasing Mays Points: 645 More actions · Answer 1

Hi chaps

Thanks for your input but I think you are missing my point.

The code is made up,

The end user is not the best at writing efficient SQL and I wanted to just limit resources allocated to any code she is running.

Cheers

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

You restruct queries on using over a certain governer cost.

But that setting goes for _all_ queries !

EXEC sys.sp_configure N'query governor cost limit', N'300'

RECONFIGURE WITH OVERRIDE

or you can shut down parallelism for the instance :

EXEC sys.sp_configure N'max degree of parallelism', 1

RECONFIGURE WITH OVERRIDE

or you can have the instance only use one processor:

EXEC sys.sp_configure N'affinity mask', N'1'

EXEC sys.sp_configure N'affinity I/O mask', N'1'

RECONFIGURE WITH OVERRIDE

But keep in mind, everyone will suffer these measures !

Or you can wait for sql2008 ..... 😎

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Yuckon (6/27/2008)
Hi chaps
Thanks for your input but I think you are missing my point.
The code is made up,
The end user is not the best at writing efficient SQL and I wanted to just limit resources allocated to any code she is running.
Cheers

Write a view for her that is effecient... tell her to use that. If she's smart, tell her how bad her queries crush the server... she'll understand. If she's even smarter, teach her how to write good queries that don't crush the server... she'll teach others, as well.

--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)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Jeff Moden (6/27/2008)
Yuckon (6/27/2008)
Hi chaps
Thanks for your input but I think you are missing my point.
The code is made up,
The end user is not the best at writing efficient SQL and I wanted to just limit resources allocated to any code she is running.
Cheers
Write a view for her that is effecient... tell her to use that. If she's smart, tell her how bad her queries crush the server... she'll understand. If she's even smarter, teach her how to write good queries that don't crush the server... she'll teach others, as well.

If she doesn't need up to the minute data, maybe set her up with a local snapshot of only the data she needs. Most PC's these days are more than capable of handling that. Of course - not if it entails highly sensitive stuff, but more routine stuff could live that way. Most of my query-building users tend to be very focused on specific data sets, so the stuff they're after really isn't big.

If that could work for you - the only person they get to piss off with a bad query is themselves.....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?