Is there a way to restrict a certain process from using 100% of the CPU time.
Is it possible to control how much % CPU time a process can use?
For e.g. there is a process that generates OLAP Cubes and is using 100% of the CPU time. This just completely stops me from doing anything else on the server or the database. I would like to allocate a designated percentage of CPU time to this process (or all processes), or even make it run in the background as a lower priority process, so it is possible to work on the database. Is it possible to achieve this?
You could try finding the process in the Windows Task Manager on the Server.
Once you have located the process right click on it and set the priority to BelowNormal or Low. I've never tried this but it sounds reasonable.
Also, if you have a multi-processor system you could change the Servers properties in EM by telling the system to use only a specified number of processors as opposed to all of them which is the default.
As with anything plan for the worst - create backups and test it first before trying it on the live server.
Windows task manager does not show me the processes running inside the database. It shows only one SQL server process running. Now only if Enterprise Manager will let me do that.......
I like your idea about the processors. But the change would be a global change for all the processes. Is there a way to do it only for a particular process? i.e. the rest of the processes can use all the CPU while a chosed process can use only one CPU?
Enterprise Manager is a snap-in to the Microsoft Management Console and you should be able to find it in task manager as mmc.exe. Just be aware that other snap-ins may be running as well as the one for EM. To be sure you might want to close any other instances of mmc so you can select the correct one.
Your right about the processor change - it's global. I am not aware that you can set a particular process to use just a single CPU but hey who knows. Anyone out there have an answer?