CPU Test

Question

CPU Test

arrjay

Hall of Fame

Points: 3455
More actions
July 2, 2015 at 4:34 am

#318651

Hi All - I have recently changed the MAXDOP settings from 4 to 8 in SQL Server. I now want to perform a test to ensure that it is using all CPU cores.
Is there a way to do this? Thanks!
--------------------------------------------
Laughing in the face of contention...

Viewing 6 posts - 1 through 5 (of 5 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

The only way I can think of is to execute sp_who2 when a potentially parallel query is running. If any SPIDs have eight rows, then your change has been successful.

John

arrjay Hall of Fame Points: 3455 More actions · Answer 2

Thanks for the suggestion.

I guess I need to come up with a query that hits all cores.

Thanks.

--------------------------------------------

Laughing in the face of contention...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

John Mitchell-245523 (7/2/2015)
If any SPIDs have eight rows, then your change has been successful.

No. MaxDop limits the number of running threads a query may have, not the total number of threads. A query running under MAXDOP 4 may have 10+ threads. Only 4 will ever be running at once. The others may be coordination threads, threads from other parallel segments that aren't running yet, etc.

To see the actual degree of parallelism, run a query with the actual execution plan included, look at the properties of a parallel operator and look at the 'Actual number of rows' property, expand it out and see how many threads are listed there.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398707 More actions · Answer 4

And if you don't have a plan that will go parallel on its own, just use TRACEFLAG 8649. That will drop the query cost to zero so you'll see a parallel execution.

"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

arrjay Hall of Fame Points: 3455 More actions · Answer 5

Great thanks.

I managed to force a query to use parallelism and found the number of cores by looking at the Parallel Operator (as mentioned).

Thanks again all.

--------------------------------------------

Laughing in the face of contention...