cpu quota / resource management

Hello *,

Is there a way to restrict resource consumption by user or connection?

If I execute some long running reports my complete server slowes down. I'd like to assign this reports only limited resources.

Thanks,

Michael

You can configure database mirroring for the target database in your production SQL instance and create snapshot on the mirror server (your backup SQL instnace) on different machine. The principle system can be considered as your normal OLTP business system and the mirror server can be used to support your reporting system. Since the reporting system will be run on differet SQL Server instance on different machine, it will not hurt your normal business onthe principle server. HTH|||Really your easy options are limited to setting query govenor which is time based not resource based.|||Thanks for the hint, "SET QUERY_GOVERNOR_COST_LIMIT" is useful in this context. Additional question:-
Is there a way to cancel running querries? For instance by issuing a "cancel" call from a seperate thread?

Thanks,
Michael

CPU %usage restriction for a process

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.|||Thanks for your reply.

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?

Wish I had a better solution.

Best Regards|||Resource management for a processes is deal on MS SQL Server engine. ... and Windows also. You can't change priority for one process only.