Restricting CPU usage per user session?

[hengisglasgow73]

We have a dual processor SQL Server which supports ~7 different systems. One of these systems runs a daily update which hogs the CPU to 100% and slows down these other systems.

We have just moved that system to use just one processor. I have two questions

1: If that system uses 100% of CPU A will the other systems all start using CPU B

2: Is there any way of restricting the CPU usage any session gets so it would not cause the server to slow down all on its own?

I havent seen anyhting quite like this before on this group except thread962-656659

TIA

Hengis

 

[mrdenny]

From your description I think that what you want to do is set the processor affinity of the other processes to all use the second processor, and the affinity of the offending process to use the first processor.

I'm not sure what your deffination of a "system" is. Are you talking about 7 different programms running on the server, or 7 different databases running within the same sql server instance.

If you are trying to restrict a single process from using to much cpu power try adding OPTION (MAXDOP 1) to the end of the query. That will restrict the query to use a single processor, leaving the other processor for the other tasks being handled by the system.

Denny Cherry

 

[john230873]

To my understanding you can not restrict how much CPU a process is allowed. Don't let this get confused with out much caped CPU cycles a query can use, these are very different. First you will need to see what is using the processor, is it the DTSRUN or is it SQL server.

If it is SQL server check under the properties of SQL to see if you have given it boost priority. If you have then you can try turning this off. This boost priority is for that instance, so if you have multiple instances check all of them. Warning if you have more than 1 instance and only one is set to boost the others will suffer.

If it is DTSrun then check your memory, are you page swapping to disk, if so reduce the maximum amount of memory allowed for SQL server. SQL is meant to give memory back to applications if they require it, I have found that this is not always the best option. In some cases it better to cap it you can work out the amount to reduce it by using task manager.

Hope this helps.

John

 

[hengisglasgow73]

Thanks guys for the pointers.

By systems I meant database instances - there are no applications on there. I liked the MAXDOP 1 option and wil probably use that.

I dont know that much about the DTSRUN process. But I will look into that as well.

Thanks

Iain

 

[john230873]

Ok try this

if you have instances that are important Boost them but leave the SQL instance that is not important. What will happen is when the process runs it will use as much as the CPU it can but as soon as any other instance start processing they will over rule this one.