August 8, 2013 at 8:30 am
Do you have indexing on any of these tables? I ask because of memory allocation requirements on higher DoPs.
From: http://technet.microsoft.com/en-us/library/ms190787.aspx
For both aligned and nonaligned indexes, the memory requirement can be greater if SQL Server is applying degrees of parallelism to the build operation on a multiprocessor computer. This is because the greater the degrees of parallelism, the greater the memory requirement. For example, if SQL Server sets degrees of parallelism to 4, a nonaligned partitioned index with 100 partitions requires sufficient memory for four processors to sort 4,000 pages at the same time, or 16,000 pages. If the partitioned index is aligned, the memory requirement is reduced to four processors sorting 40 pages, or 160 (4 * 40) pages. You can use the MAXDOP index option to manually reduce the degrees of parallelism.
Sorry if this doesn't apply to your situation, just rolling ideas in my head.
August 8, 2013 at 8:37 am
I would think that looking at the execution plans with 4 and 8 DOP would be a starting point.
Any chance that your tempdb setup is not optimised for MAXDOP 8?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);August 8, 2013 at 8:57 am
There are indexes on these tables but nothing "exotic" as they are your typical clustered primary key and a few other indexes on relevant columns. On this server tempdb is not on its own array and can't be as this server is maxed out. In the future that will change when the server is replaced. I did look at tempdb but there was no growth and the metrics for read/write, etc. were where they typically are.
The queries that trigger parallelism are not generated by the main application (the one with look up issues) and I've even had the programmer add the query hint for MAXDOP 1 to some of the stuff to control this. The kicker here is that nothing (as far as we know) has changed with respect to how anything is done. No application changes or DB changes were made with the exception of the MAX DOP setting yesterday.
What really bothers me here is that this setting change corrected the issue with looking up certain tickets. As mentioned, the method to look these up, how they are stored, etc. is exactly the same as those with no look up issue. As far as the application, storage and other components go these *should* be a one-to-one type of thing. I could see it if everything was slow meaning, all tickets had a look up issue but this wasn't the case.
Cheers
August 9, 2013 at 2:33 am
That could be a data skew and some form of parameter sniffing and changing MAXDOP has just had a lucky effect on compiling a stable plan that is good enough for all sets of parameters.
Generally I'd play around with the Cost Threshold for Parallelism setting before messing around too much with MAXDOP.
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply