Suppose we have a database server, with SQL Server on it. And we are moving data from another database server, through our Informatica server, and onto that SQL Server database server. And our SQL Server database server has four processors. Is it possible to force the connections to be multi-threaded so it sends data quicker? How can this be done?

Right now, only one of the processors is being used on the SQL Server database server.

  • 1
    What is the MAX DOP setting on your sql server instance ? If it is default - 0 then it will use all the available threads. Also, parallelism will be governed by cost threshold of parallelism sp_configure option at the query level. – Kin Shah Jul 21 '15 at 20:37
  • 1
    @macgyver you'll need to create multiple connections in Informatica to perform different parts of the transformation simultaneously. – Max Vernon Jul 21 '15 at 20:59
  • Kin - EXEC sp_configure 'max degree of parallelism' .... gives 5 columns in its output: name: max degree of parallelism, minimum: 0, maximum: 1024, config_value: 0, run_value: 0 ... are we looking at the config_value or the run_value?? – MacGyver Jul 21 '15 at 21:08
  • 1
    Run value .. You have set it to 0 - use all threads. I would agree with what @max said. – Kin Shah Jul 21 '15 at 22:27
  • Max - if it's a single mapping, would I just put in a SQL override (target "SQL Query" field) and filter the records of the target table to 25% in four different session tasks, that all point to the same mapping, assuming I have a four core processor? – MacGyver Jul 22 '15 at 0:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.