Performance Tweeking using Parallel Hint

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Performance Tweeking using Parallel Hint

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-07-08 : 07:14:49

Brian writes "I have a query that involves multiple joins. Alone, the query takes 162 seconds. If I add in

/*+ PARALLEL(Table1_name 4)*/

after the first SELECT statement, the runtime decreases to just under 5 seconds. It does, however increase the strain on the server CPUs.

I am trying to allow the query to manage how much of the CPUs it should take according to the current CPU usage in order to handle multi user environments better.

I ran across the boolean: PARALLEL_ADAPTIVE_MULTI_USER but I am not sure how it works or where to put it. Thanks for your help!"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-07-08 : 07:24:12

quote:
I am trying to allow the query to manage how much of the CPUs it should take according to the current CPU usage in order to handle multi user environments better.

SQL Server's query engine already does this, and anything you do with your code to change this is likely to interfere with the process and make it worse. Microsoft's recommendation has always been to allow the query optimizer to work without adding hints to the query, UNLESS you see a consistent, demonstrable improvement by using hints.

Manually varying the degree of parallelism based on existing CPU load will probably not work; if the CPUs are available, then they would all be used to process the query anyway. There are certainly other methods you can use to optimize the query that will have a better chance of improving the performance (index hints, limiting the number of rows affected, reducing the number of joins, etc.) If the SQL is sub-optimal, adding hints via the application layer will not help much.