setting priorities for running SPs | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

setting priorities for running SPs

Hi Is there any way in a stored procedure or a database to set the priority of the task i.e. lowest? There are a number of databases on one server and when running a stored procedure that inputs about 200000 rows into a table in databaseA then this uses all of the processing space and so users trying to run a program that uses databaseB can not and this is not what is wanted. I want to priority for the processing space to go to that for use of databaseB and the running of the stored procedure in databaseA to take a back seat for a while until the task using databaseB is completed. Thanks
CE
Why not use BULK INSERT to import rows instead of controlling thru SP? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I do use the bulk insert command in my stored procedure but within that same procedure i also have another command to manipulate the data in the temporary table before i insert it into the target table in databaseA. Any ideas how to decrease the priority of running this SP?
CE
Set the MAXDOP = 1 so it doesn’t use parallelism. If it’s a multi-processor system, this will make sure it doesn’t cut off the whole system by taking the entire set of processors. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
The server only uses one processor, in this case I thought the max degree of parallelism option was ignored. Is this right or not? Is there any other way the limiting of the amount of CPU the SP uses so there is some left for other queries? Thanks
CE
If it’s one CPU, you don’t really have any options that I know of. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Parallelism works if system has more than one CPU, otherwise follow other roots like using BCP or BULK INSERT for import of rows. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>