MAXDOP Option not getting used
-
Hi,
A server has 2 databases A and B (700GB each). B is the copy of A. A query makes use of parallel processing on database 'A' but not in 'B' (proved using trace and sp_who2). On 'A' query takes 10mins and same query takes over an hour to complete in 'B'.
Server has 8 processors (4 physical with hyperthreading enabled).
MaxDOP is set to 4 at sql server level. I tried forcing MAXDOP using following hint in the query (against B): option (maxdop 1) but no use.
Both databases have data/log files spread across the disks in similar fashion. Both database have SAME settings too.
Can anyone tell why the difference in performance? Also, why MAXDOP is not getting used (even forcefully)?
Any help in this regard will be appreciated.
Thanks, Madhur
-
One thing more...I'm still analyzing the health of indexes on both databases...but i doubt there will be any difference.
-
You can't force a parallel plan using maxdop, only restrict the available processors. The only settings I can figure would alter the plan would be stats and indexes, are the stats up to date ?( assuming absolute identical databases )
I'd do a dbreindex on all tables, update stats for good measure, dbcc updateusage on both databases and run the query again. I'd examine the query plans to make sure prallel processing is being used - not sp_who.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Thanks for your reply.
I ran update stats against database 'B' but to no use. Query plan still not making use of parallelism.
DBCC updateusage is currently running.
One more difference observed:
Each database has 8 data files.
For database ‘A’, these files are spread across 8 different disks (each Raid5)
For database ‘B’, these files are spread across 8 different disks (5disks are Raid0 and 3disks are RAID5)
Apart from above, rest all settings are same for both the databases.
Does this ring any bells??
-
as I say the optimiser is unable to take disk layout into consideration.
I take it the query in question is being run from the same location and that the session settings are identical? Database collations are identical ? Data is identical, indexes identical, stats identical? examine the plans in the cache to make sure the plans look the same - i.e have the same setops value etc.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
sorry - noticed you mentioned using maxdop 1 - this would make both queries run the same , yes ? as there would be no parallelism in either case.
Are we talking a proc or query and do you have any in-line functions involved?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Yes, session settings/data/indexes are identical. As far as stats are concerned, i updated them on the poor performing database (but it didn't help). We are taliking about a query. I changed it into a proc too to give it a shot but no luck. There is no inline function being used.
Sorry, i made maxdop=0 and not 1. This should use parallelism, right?
I understand that decision of using or not using parallelism is solely made by SQL server, agreed, but my question is why it is NOT showing any difference in execution plan even after using "option (maxdop 0)" explicitly in the query.
How to examine the plans in the cache to make sure the plans look the same?? So far, i'm looking the execution plan in profiler.
Thanks again for all your time.
-
The queries are _exactly_ the same.
Sounds silly, but if someones swapped out a temp table with a table variable, or got a variable defn different, or populated a variable with the results of a query then passed the variable down instead of running the subquery, or put the values in by hand etc - it seems like the same query but might not be... it's caught me out before
-
you only use the maxdop statement to limit parallel plans. If you want to use parallelism don't include a maxdop statement, I'd assume maxdop=0 will use all, but not necessarily , remove the hint. If the optimiser won't use a parallel plan then the query cost is too low.
You can run a query against the proc cache to view plans - setopts are what you're interested in.
try this link, Randy's article is pretty good http://www.sql-server-performance.com/rd_data_cache.asp
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
parallel plans are decided by all and mainly available CPU and Memory to complete the query and so the time wen u ran the query there may not be enough scope for using parallel plan.......
-
The amount of physical memory for sql server is unlikely to affect parallelism. For an out of the box sql server the only deciding factors on parallel plans are the query cost and are multiple procs available? ( and the edition ) It's sometimes considered best practice to always set maxdop to 1 during development and test, leaving the issue of parallel plans to later - I'm not sure I agree with this but as I say if your sql install is out of the box then use maxdop 1 to linit the query. an estimated query plan ( without maxdop ) will tell you if your query will generate a parallel plan.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Please make me understand the point that sql server takes parallel plan sometimes and serial plan sometimes even when the query is same and the environment is same.
some times think that other processors are busy and one processor is idle then will it take serial plan or queue the threads in the other processors for parallel plan.....??????
and i guess for parallel plan you need more memory then when there is a memory constraint in the server at that particular point of time then how about parallel plans?????
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply