April 29, 2010 at 6:35 am
i ran a test on a non-critical SQL server where i set the parallelism to 1 instead of the default 0. all the jobs ran slower and it was a nightmare. this is our DBA warehouse server where we collect performance and log data.
has anyone changed it and seen positive results?
April 29, 2010 at 6:45 am
alen teplitsky (4/29/2010)
i ran a test on a non-critical SQL server where i set the parallelism to 1 instead of the default 0. all the jobs ran slower and it was a nightmare. this is our DBA warehouse server where we collect performance and log data.has anyone changed it and seen positive results?
I recall a few years ago where I changed MAXDOP for a query to 1 and it greatly reduced the time it ran. It all depends on what the optimizer decides to do.
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
April 29, 2010 at 8:06 am
It's not parallelism itself that's the issue, it's the cost threshold. The default value of 5 is ridiculously low. I'd suggest starting off with it at 25 and see things go from there.
"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
April 29, 2010 at 8:23 am
if i play with the cost threshold, should i set the max degree of parallelism to 4 or 8 instead of the 1 that i tested with?
April 29, 2010 at 8:29 am
alen teplitsky (4/29/2010)
if i play with the cost threshold, should i set the max degree of parallelism to 4 or 8 instead of the 1 that i tested with?
From what I have read - you shouldn't set it higher than 8 on any system with more than 8 CPU's. With that said, how many CPU's do you have?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 29, 2010 at 8:30 am
It really depends on how many cpu's you have. Zero sets it to use as many as it has available. Setting it to 1 limits it to a single cpu.
"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
April 29, 2010 at 8:32 am
it's a single quad core CPU in there now. the server is experiencing mission creep and i've been thinking about requesting another cpu
April 29, 2010 at 8:39 am
alen teplitsky (4/29/2010)
i ran a test on a non-critical SQL server where i set the parallelism to 1 instead of the default 0.
Just curious why did you set parallelism to 1 ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 29, 2010 at 8:50 am
we're looking to buy a few server with the new 6 core CPU's. the worlk load is going to be different on these with over 2000 connections at a time, but i wanted to run a test to see how it would perform and learn about the differences.
we've had a few instances where a java app sends an update to the SQL server and instead of taking a second or so, it hangs. no one has figured out why. my theory was that maybe it's something in the timing of running the queries through all the cores at once
this server mostly does a small number of batch imports and serves select queries, but lately we've put more databases on it and it now has a few hundred connections at a time where different hosts feed data into it or retrieve data
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply