Determing paralllelism being used for a statement

• Wilfred van Dijk

  SSCrazy Eights

  Points: 9003

  More actions

  November 4, 2009 at 3:34 am

  #217137

  Hi,

  Recently we had a discussion about parallelism. We are currently evaluating different settings. Does somebody have a statement which shows for each statement the amount of parallelism being used?

  I'm sure it's somewhere in dm_exec..

  thanks!

  Wilfred
  The best things in life are the simple things

• peterhe

  SSChampion

  Points: 11469

  More actions

  November 4, 2009 at 7:55 am

  #1074815

  take a look at sys.dm_os_tasks. If a request (session_id,request_id) runs in parallel, it has more than one entries.

  SELECT session_id,request_id,count(*) as Parallel

  FROM sys.dm_os_tasks

  WHERE session_id IS NOT NULL AND request_id IS NOT NULL

  GROUP BY session_id,request_id

• Wilfred van Dijk

  SSCrazy Eights

  Points: 9003

  More actions

  November 4, 2009 at 8:35 am

  #1074843

  Thanks, but I don't think this is the right query.

  currrently, MaxDop is set to 2, but this query reported:

  sessionid request_id parallel wanneer

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

  374 0 3 2009-11-04 16:31:00

  which indicated SQL is using 3 processors ? :crazy:

  fyi the server has 4 quadcore CPUs

  Wilfred
  The best things in life are the simple things