Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kanati
Starting Member
36 Posts |
 Posted - 2002-09-05 : 13:21:32
|
| Is there a way to limit cpu usage of a process (execution of a job or stored procedure)? I have a process that is rather lengthy and pegs our cpu usage at 100%. Since it is also used for radius authentication, email authentication, and even ftp authentication, when this process runs and pegs the cpu, other processes either come to a halt, or they are running so slowly that a lot of authentication requests time out and people are denied. If we could limit the cpu usage to say 50% for that process, it would help immensely.The machine is a dual P4 with a gig of memory too, so it's not ancient by any means. :)Thanks Kanati |
|
|
solart
Posting Yak Master
148 Posts |
Posted - 2002-09-05 : 13:50:02
|
| Since the machine that SQL Server runs on seems to support multiple functions, which you don't want grinding to a halt and you have multiple processors, maybe restricting SQL Server to the use of one processor will accomplish what you want.You could test this.SQL Server can be restricted via its affinity mask option. Please refer to BOL. I believe the modification of this option will require a shutdown and restart of SQL Server for it to take effect.HTHs solart |
 |
|
|
Kanati
Starting Member
36 Posts |
Posted - 2002-09-05 : 15:33:13
|
| I know you can do that, but the other functions are handled by SQL as well so I don't think that's a viable option. Thanks anyway.Kanati |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-09-05 : 16:00:46
|
| As for a solution, you could pull the data into a separate multi-threded application and do whatever manipulations you need etc.I think the root of this problem is that you have a TON of stuff running on that one box. That's very dangerous because if you lose that one box, you (or your network guy) are going to be in a world of hurt.Maybe you could have your stored proc run on a batch of records at a time, and then WAIT for some period of time. Do a search on this board for either batch or WAIT.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-09-05 : 16:05:58
|
| Another option is to change the "max degree of parallelism" option for that stored proc to 1, and then back to zero when it's done.Certainly not the best solution because this will tell SQL server to only use one processor until you change it back to 0. Pretty much the same thing as affinity as far as I can tell.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
|
|
|
|
|
CPU Usage