how to restrict SQL running thread

Hi,
We have a big report running on SQLRS2000+SP2. The report has multiple
datasets and queries. When report starts running, the database server it
connects to run query and pull data will start 33 threads and immediately
reach 100% CPU usage for all 8 CPUs. Databases is over 1TB and sets on EMC
SAN. There is no blocking during report generation. There is no sub-report,
so I assume there is only one DB connection.
We are wondering is it possible to restrict the number of threads
instantiated or is it possible to serialize the report generation, only run
1 thread at a time. Please help.
Jasonyou can set the maxdop option in your queries to limit the number of CPU
used during the execution of the query.
select ... from ... option (maxdop 1)
this reduce the number of CPU used in a parrallel query
the maxdop option can be setup at the server level instead-of the query
level; but not recommanded until you suffer performance issues
test the query, reducing the maxdop can reduce the query performance.
if the query takes 1min instead-of 10sec. the user will not be happy!
why its a problem for you if the queries use all the CPU?
"Jason Wang" <aa@.aa.com> wrote in message
news:eSLkGBeRGHA.5108@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have a big report running on SQLRS2000+SP2. The report has multiple
> datasets and queries. When report starts running, the database server it
> connects to run query and pull data will start 33 threads and immediately
> reach 100% CPU usage for all 8 CPUs. Databases is over 1TB and sets on EMC
> SAN. There is no blocking during report generation. There is no
> sub-report, so I assume there is only one DB connection.
> We are wondering is it possible to restrict the number of threads
> instantiated or is it possible to serialize the report generation, only
> run 1 thread at a time. Please help.
> Jason
>