Do you enable parallel execution?

Question

Post reply

Do you enable parallel execution?

Allen Cui-55137

SSC Guru

Points: 51650
More actions
January 8, 2004 at 12:05 pm

#152008

Do you enable parallel execution in SQL Server on your OLTP and OLAP system?

Do you see any benefits by enabling it?

Do you have any problems after turn it on?

Do you see any behavior changes in parallel execution of query after applying service pack 3a?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Green Armchair Hall of Fame Points: 3561 More actions · Answer 1

I think it depends on the number of processors you have. I understand that SQL is not very good at working out how best to parallelise up when there are lots of CPUs (>8). We have 8 physical CPUs with hyperthreading to give us 16 logical CPUs and it has been suggested to us that we should set "max degree of parallelism" to 4. It seems that SQL will sometimes spin up too many threads and spend more time working out how to distribute the load than actually doing the work. I haven't implemented this yet.

Hope this helps

Ferguson SSCertifiable Points: 5837 More actions · Answer 2

I've often wondered the same. We have 4 CPU's and I am convinced that at times we get basically hung up because some query creates a huge number of parallel threads and then other threads can't even get started. I've not experimented as the condition is rare, but I do know at some times the system just plain stops doing new work (as opposed to everyone and everything running slower which is fine).

Larry Stein Default port Points: 1447 More actions · Answer 3

We selectively turn it off for certain ugly queries.

We have one system that has double-digit millions of rows in each of 36 (and growing) partitions of a union partitioned view. Actually, there are 3 sets of that type of union partition view, each with a similar amount of data. We have queries that need to search most of those partitions. The resulting SQL ends up looking at over 70 relations + indices. With parallelism, occasionally dozens of threads would be started on an 8 processor box and things would come to a screeching halt.

We have found no way to predict when something like this would happen. Our test box has fewer processors and less data, so we can't recreate the conditions in a safe environment. Sadly, we just learn from experience: "Well, I guess we'd better not do THAT again!".

If you can, test, test, and test again in a realistic environment.

Larry

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 4

Although many of the client's I've worked for end up turning it off for the very reasons our collegues have expressed above, I've found that it's better to work with your server and queries to find the threshhold that works best for your particular situation. If you are able, profile your server for a few days to get a baseline, then adjust the parallelism threshhold value up slightly, and profile again. Continue this until your performance starts to decrease, and you will be able to find a sweet spot, which allows most queries to not use parallelism, while still allowing the largest of them to take advantage of it, as it can be a major benefit if the query is very large, or pulls together many recordsets to form a single return set. For instance, a query across federated servers, or partitioned views generally sees major performance gains from parallelism, where queries across a few tables generally does not, and in fact, decreases performance. If the threshhold is set so that the smaller queries are not broken up in threads, yet the larger ones are still able to use it, you get the best of both worlds......