0

I have been using Parallel.ForEach to fetch & process data concurrently. Collection which is used in Parallel.ForEach has more than 100 elements. If i don't restrict number of thread created in parallel then creating SqlConnection out of Connection Pool throwing an exception of exceeding the limit of connection pool.

Now i want to set maxdegreeofparallelism to restrict connections being created at any moment.Production Server might have hyperthreading support .If hyperthreading is supported by system then is it advisable to set No of LOGICAL cores instead of PHYSICAL cores into maxdegreeofparallelism.

So, should I use the setting for # LOGICAL cores or PHYSICAL cores?

محمد
  • 211
  • 1
  • 7
107
  • 552
  • 3
  • 26
  • 3
    Sorry, what is the question exactly? – محمد Jun 03 '15 at 10:02
  • Parallel.Foreach has maxdegreeofparallelism. Should it be # LOGICAL cores or PHYSICAL cores – 107 Jun 03 '15 at 10:03
  • Maybe this answer will help : http://stackoverflow.com/questions/9538452/what-does-maxdegreeofparallelism-do – محمد Jun 03 '15 at 10:06
  • If you are reading from the same database, are you sure you want to do it in parallel? And I really don't know why you would care about cores, if the problem is in the database connection. Its limits won't change based on cores, physical or virtual. Are you sure you're closing the connections? – Sami Kuhmonen Jun 03 '15 at 10:07
  • 2
    The answer is: It doesn't matter. You are clearly restricted by the number of SQL connections, not the number of cores. – H H Jun 03 '15 at 10:15
  • I have been connecting to different databases. If my machine has 8 logical cores then i want 8 or 6 connections to be made at a time. Not restricting throws exception of exceeding the limit of connection pool. – 107 Jun 03 '15 at 10:18
  • @Henk Holterman : As specified by microsoft no of sql connections are 100 by default. I want to put a cap at (number of cores supported - 2). – 107 Jun 03 '15 at 10:21
  • 4
    The number of (client) cores has no direct bearing on the capacity of your SQL server. It is the wrong metric. – H H Jun 03 '15 at 10:25

2 Answers2

2

This really should just be closed, since it's unclear and clearly there is another problem than what you're asking.

You should use the number that you need. The system internally has limits already, which are used. If they don't suit you, then you have to define the limits. It has nothing to do with number of physical or virtual cores in the system.

Usually it is not advisable to get data in parallel from a single database, since it may actually make the whole process slower.

And since you have a problem with connection pool runnning out of connections, there are two options:

  1. set the maximum parallelism to the number of connections you can actually get at one moment
  2. you are not closing connections properly and any number you set will still cause the problems

I would suggest to go back to the design and think, not try to change settings that actually will at best case just hide the problem for now and at worst case waste time and not help at all.

Sami Kuhmonen
  • 30,146
  • 9
  • 61
  • 74
-1

If hyperthreading is supported by system then is it advisable to set No of LOGICAL cores instead of PHYSICAL cores into maxdegreeofparallelism

IMO it should be equal to number of logical cores or be greater than that.

The reason is that a hypethreaded processor attempts to execute two threads independently of each other. Here is a quote from the Nehalem processor architecture paper

The fundamental approach Nehalem (and other modern processors) take to maximize instruction completion rates is to allow the micro-ops of as many instructions as feasible, proceed in parallel with micro-op occupying independent FUs [Functional Unit] at each clock cycle.

If you look in the task manager OS presents each logical core as an independent processor. Surely there are technicalities as to whether logical cores are truly independent. However, at this level you should consider them as independent units IMO.

Another thought is that you could consider increasing the number of threads up to the number of available connections, especially if you have any waiting/blocking inside. When a thread is in a waiting state, for example when waiting for an IO operation completion, it is not scheduled for execution. This is something that you need to test with your code and database.

oleksii
  • 35,458
  • 16
  • 93
  • 163
  • i will test with a number greater than the # of logical cores. Appreciate your advice – 107 Jun 03 '15 at 11:18
  • 2
    Actually `maxdegreeofparallelism` has very little to do with the number of cores in the machine. – mg30rg Jun 03 '15 at 11:19