"Geoff N. Hiten" wrote:

> Comments inline
> "aposql" <aposql@discussions.microsoft.com> wrote in message
> news:E84E994D-A088-4CA6-BAFD-91A7A414CD91@microsoft.com...
> > We have some sp transactions that take minutes to process and tend hog the
> > cpu resources slowing other requests on a busy website. I have a few
> > questions.
> >
> > 1. Can a specific cost be assinged to a stored procedure, so as to allow
> > for
> > the execution of the sp without maxing out the cpu?
> >
> Not really. You can set the maximum degree of parallelism os as to not max
> out all the CPUs in a multi-CPU system.
>
> > 2. Should we break out the sp transacation into smaller nested
> > transactions
> > and use the sql agent to process the transaction?
> >
> As a general rule, SQL does much better at processing lots of small
> transactions rather than one large transaction. This is not a universal
> rule, just a general guideline.
>
> > 3 Could we use a cluster and the dtc to assign specific sp to one SQL
> > server
> > to handle long requests and use the second sql server to handle basic
> > requests?
> >
> MSCS Clustering is not a load-balancing technology.
> > 4 Other thoughts?
> >
> If these are summary or analysis type queries, then you may want to run them
> against a copy of the data. Snapshots, replication, mirroring, and log
> shipping are all options to create copes of your database for analysis and
> ETL sourcing. You may need to take the firs steps towards a reporting and
> data warehousing environment.
>
>
> > I would appreciate comments and ideas to point me in the right direction.
> > Thanks in advance
>