December 21, 2010 at 6:39 am
Hi all,
I am in a situation where I want to force SQL Server to use Parallel Plan insted of using serial plan to execute query.
Is there any Query Hint or any other way to force/ explicitly force SQL Server to make Parallel Plan?
Current SQL Server Configurations :
1. Max degree of parallelism : 0 (Default)
2. Cost threshold for parallelism : 1
3. CPU (Scheduler) : 8
4. RAM : 16 GB
5. SQL Server: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
I find a url http://www.sqlmag.com/article/sql-server/forcing-a-parallel-plan.aspx but is only available for paid member.
Ram
MSSQL DBA
December 21, 2010 at 7:13 am
I'm not a member either... but for 5.95$, it doesn't seem like a huge barrier to entry.
P.S. Have you considered using plan guides?
December 21, 2010 at 7:21 am
You owe me 5.95$ :hehe:
Check out those 2 links :
http://technet.microsoft.com/en-us/library/ms190772.aspx
Which I found here :
http://sqlserver-qa.net/blogs/perftune/archive/2007/10/23/2212.aspx
December 21, 2010 at 8:14 am
what does mean by "You owe me 5.95$" Please make clear.
Ram
MSSQL DBA
December 21, 2010 at 8:22 am
Reo (12/21/2010)
what does mean by "You owe me 5.95$" Please make clear.
I was just joking. That's the price of the subscription for the article you cannot read.
But I found you another article that gave a solution for the problem so you don't have to pay anything. :w00t:
December 21, 2010 at 10:00 am
Basically, in OLTP system, parallelism is not a good thing. The fact is that, the overhead on the parallelism and sometimes, one thread has to wait for the other to complete the process. However, it will helpful for index rebuilding etc.
MAXDOP WITH(MAXDOP 8) option can be explicitly speciied at query level. This can even be specified at server level too(check sp_configure).
December 21, 2010 at 10:05 am
sqlchanakya (12/21/2010)
Basically, in OLTP system, parallelism is not a good thing. The fact is that, the overhead on the parallelism and sometimes, one thread has to wait for the other to complete the process. However, it will helpful for index rebuilding etc.MAXDOP WITH(MAXDOP 8) option can be explicitly speciied at query level. This can even be specified at server level too(check sp_configure).
How will maxdop(8) FORCE the plan to use parrallelism? Can't the server still choose single thread if it still thinks it's better that way?
December 21, 2010 at 10:22 am
ofcourse, if sql server thinks which ever is best, it will choose.
December 21, 2010 at 10:38 am
sqlchanakya (12/21/2010)
ofcourse, if sql server thinks which ever is best, it will choose.
Ya, I'm trying to force maxdop 2 on a semi-complex query and the optimizer is just not budging about it.
I guess we're back to plan guides (which you'll have to extract by overloading the tables with more data so that the server chooses dop 2+). Then you'll be able to save the plan and reuse later.
December 22, 2010 at 12:29 am
sqlchanakya (12/21/2010)
ofcourse, if sql server thinks which ever is best, it will choose.
I do not want to SQL Server to think. I want to force/instruct SQL Server to use Parallel Plan to give result of my query. Can it possible?
Ram
MSSQL DBA
December 22, 2010 at 6:36 am
Reo (12/22/2010)
sqlchanakya (12/21/2010)
ofcourse, if sql server thinks which ever is best, it will choose.I do not want to SQL Server to think. I want to force/instruct SQL Server to use Parallel Plan to give result of my query. Can it possible?
Check out the PLAN GUIDE link of provided earlier. It should be possible with that but I cannot garantee it.
December 22, 2010 at 11:03 pm
•Reo; may I ask why, you don’t want sql to decide on the parallelism? What is you final objective ?
•I know that a query can be limited to use “X” number of CPU on parallelism but not very sure of forcing to use a parallel exec plan. You can attempt writing the query to have a parallel plan but no guaranty.
December 23, 2010 at 7:26 am
Hi,
My objective is reduce the elapsed time one of my large queries. Thats why I want to test out with Parallel Query Plan. I know it is not fit in every query.
There is same kind of feature in Oracle but it can be explicitly define like "parallel (degree n)"
Ram
MSSQL DBA
December 23, 2010 at 8:40 am
Maybe we're going about this the wrong way.
The optimizer is build by some of the smartest minds on the planet. So while it's not perfect I hardly feel better than those guys.
Can you post the tables definition, sample data, indexes, queries and execution plan (.sqlplan). With that maybe we can find a better way to get you the results you need.
December 23, 2010 at 10:51 pm
If you can provide the following info it will help to better understand the situation that you have encountered
-Table definitions
-The size of the tables (number of rows)
-The usage of the tables in production ( high , medium , low)
-The procedure or query that you have encountered the issue with(actual table name can be replaced with alias 🙂
-The execution plan
-The indexes and stats ( if possible) , finally the fragmentation details of the individual tables
-How many CPU’s you have in the server ?is the server having multiple sql instances? If it is clustered(please share the cluster configuration)?
Just one last questions, Are you seeing any clustered index scans on the execution plan? If you are, can you please check the extend of the fragmentation.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply