CPU dedicated to one Stored Procedure

Hi,
I have one Stored prodecure that need too much time to finish. When this SP
runs, it uses 100% of all CPUs that I have in the SQL server machine.
How can I dedicate one CPU to work with this SP and leave the rest of CPUs
to works with other SPs?
Thanks.
Well, if you're not concerned with the performance of anything else on the
server you can set MaxDOP = 1 in sp_configure. If you are worried about
other processes and it's just this one, then you can't do that at the SP
level, but you can do it at the statement level. Again, just use a query
hint and set MaxDOP = 1.
Here's an example:
select top 10 * from MyTable OPTION(MaxDOP 1)
So just do that for all of the nasty queries in your SP and you should be
fine.
"Abel" wrote:

> Hi,
> I have one Stored prodecure that need too much time to finish. When this SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
|||In addition to what Sean states I would look into tuning that sp as well.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
|||Check out SQL Server 2008 also. It is going to 'finally' provide us with a
Resource Governor!
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
|||You cannot do this currently to my knowledge. However, SQL 2008 will have a
Resource Governor that should allow you to limit CPU usage for certain
classes of executions.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>
|||I would tune this query as much as possible first. Otherwise, you could use
soft numa, have that node listen on a separate port and have a separate
connection string for that proc. That is a sketchy setup though.
Jason Massie
http://statisticsio.com
"Abel" <Abel@.discussions.microsoft.com> wrote in message
news:88F3DB22-49B4-4966-AA9F-7951017B595C@.microsoft.com...
> Hi,
> I have one Stored prodecure that need too much time to finish. When this
> SP
> runs, it uses 100% of all CPUs that I have in the SQL server machine.
> How can I dedicate one CPU to work with this SP and leave the rest of CPUs
> to works with other SPs?
> Thanks.
>