High parallel processes

• abhij33t

  SSC Eights!

  Points: 899

  More actions

  September 4, 2020 at 9:58 pm

  #3786502

  Hi,

  I have a SQL server 2012 instance (Ver-11.0.5058). We execute a stored procedure which fetches more than 24 million records after joining data from 3 different CTEs (each of which has 3 to 7 tables in join). The MAXDOP for the instance is set to 8 and server has 16 logical processors.

  When we execute the query, I can see more 80 to 110 parallel processes running in parallel (i use sys.sysprocesses). Questions:

  1. How is it possible that a query ignores the MAXDOP setting (no.. we are not changing it as a query hint in stored proc)
  2. How can i ensure that it runs only within the MAXDOP threshold.

  Thank you.

• Site Owners

  SSC Guru

  Points: 80325

  More actions

  September 5, 2020 at 10:10 pm

  #3786625

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.