September 18, 2008 at 11:21 am
We have a stored procedure that produces different results when invoked from ADO vs. directly from Management Studio. Through ADO the query never completes, but through Management Studio it completes in a few seconds. Showplan XML reveals the exact plan being used for each execution. It also shows parallelism is being used when performing the final SELECT statement. If modified by adding OPTION (MAXDOP 1), the ADO process completes in a few seconds. The question is why does parallelism appear to cause the ADO-related process to not complete, yet execution through Management Studio is not a problem?
Thanks, Dave
September 18, 2008 at 12:15 pm
you may have different set options in place from ado, most likely, which means the code runs differently. Make sure you've compiled the proc with ansi nulls and quoted identifier both set to ON.
These may alter the way the proc runs.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 18, 2008 at 12:19 pm
Thanks. I'm not familiar with how ADO works so we have to trust the developers have things setup correctly. Would the SQL provider chosen for the ADO connection also impact parallelism?
September 18, 2008 at 12:31 pm
I wouldn't have thought so, but set options in connections strings can do strange things - you can usually capture the set options with profiler when the connection is established. Failing that you can extract the set options from the proc cache and decode but it can be a bit hit and miss.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 18, 2008 at 1:14 pm
You may also want to change the parallelism threshold. I've always found the default, 5, to be way too low. I've usually bumped it up to somewhere between 20 and 30 depending on the server, the load, the behavior of the queries. I've heard of people who put it as high as 50.
But the difference between the two is probably caused by connection settings.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 18, 2008 at 1:23 pm
What does the threshold value control? How does it work?
Thanks
September 18, 2008 at 3:49 pm
Its the number of seconds for the part of the query before SQL Server decides to parallelize it.
In other words, if SQL Server's optimizer determines that a part of a query plan will take more than 5 seconds (the default value), it will parallelize that part of the query plan.
September 18, 2008 at 4:09 pm
I believe I found the option in BOL. Is it cost threshold for parallelism?
September 19, 2008 at 5:37 am
Yes, that's it. Jeremy Brown described it well above.
My take. The optimizer doesn't decide on parallelism or not, it's the query engine. Any given query can have two execution plans, one with parallelism and one without. The thing is, parallelism can be very helpful. But it comes at a cost. It has to split the processing up into multiple streams and then marshal the streams back together. The cost threshold for parallelism says that any query that takes longer than five seconds should be a candidate for parallelism. While that makes sense, the cost of all the work to manage the parallel streams frequently, in most of the systems I've managed and a healthy percentage of those I've heard about, out weighs the benefit. So moving the cost threshold to a higher number means it only functions against the queries that really need it and not just some weakly written proc that is going to suffer under the load of the parallel streams.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 19, 2008 at 7:26 am
Thanks Grant. I read quite a few MS and other documents about parallelism last night. Most make sense, but a few were a bit too complicated for my current level of understanding. One of interest talked about setting affinity for processors and I/O on multi-instance servers. Do you agree? That may explain some of the issues we see on one test server with three instances.
Thanks, Dave
September 19, 2008 at 9:23 am
Most of our production systems don't have multiple instances, but we do see that kind of contention in the dev & qa systems. If you do have multiple instances, it's probably a good idea.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 22, 2008 at 4:23 pm
I was told on a couple of occasions that changing the threashold actually doesn't make much difference as the optimiser will over-rule your settings if it thinks best - how you'd prove that I don't know - I have some tested parallel queries so perhaps I'll see if I can raise the threshold to force the query to a single thread.
I still think it's set options.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 22, 2008 at 5:28 pm
We are going to call Microsoft for some assistance. The first line of MS support can be hit and miss so I'm not holding my breath. I'm guessing the second line of support will get involved after about 4 hours. I'll update everyone once we know more.
Thanks, Dave
September 23, 2008 at 6:21 am
colin Leversuch-Roberts (9/22/2008)
I was told on a couple of occasions that changing the threashold actually doesn't make much difference as the optimiser will over-rule your settings if it thinks best - how you'd prove that I don't know - I have some tested parallel queries so perhaps I'll see if I can raise the threshold to force the query to a single thread.I still think it's set options.
I don't doubt it's possible, but I've never seen that kind of behavior (which proves exactly squat). It is possible to force parallelism by lowering the threshold, so it only makes sense that raising it must eliminate (limit?) parallelism.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply