|
Home > Archive > MS SQL Server ODBC > April 2005 > How can I increase the maximum number of parallel connections when exporting to Oracle via DTS?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How can I increase the maximum number of parallel connections when exporting to Oracle via DTS?
|
|
| David Grant 2005-04-18, 1:23 pm |
| Hi to everybody,
When exporting to Oracle from SQL Server using Import/Export Wizard,
I've discovered that SQL Server only creates 4 simultaneous
connections to Oracle. Obviously this fact prevents SQL Server from
exporting the tables in a parallel way. Instead, the tables are forced
to wait until one of the 4 connections gets free to export and due to
this fact the performance of the export operation suffers from an
enormous slow down.
Even though I create new connections in the DTS Designer (up to 20),
SQL Server 2K continues only using the same 4 ones which makes me
suspect that there must be a hidden option somewhere to change this.
Is there any way in SQL Server 2K to increase the number of maximum
simultaneous connections to Oracle?
Thank your from beforehand for reading my post.
Greetings,
David Grant
| |
| Dejan Sarka 2005-04-18, 8:23 pm |
| By default, DTS executes only 4 tasks concurrently. Check how to change this
at
http://msdn.microsoft.com/library/d...dsgnr2_60mp.asp
(the last option on the General tab of package properties).
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www. SolidQualityLearning
.com
"David Grant" <icebold54@hotmail.com> wrote in message
news:18503386.0504181032.2b96883c@posting.google.com...
> Hi to everybody,
>
> When exporting to Oracle from SQL Server using Import/Export Wizard,
> I've discovered that SQL Server only creates 4 simultaneous
> connections to Oracle. Obviously this fact prevents SQL Server from
> exporting the tables in a parallel way. Instead, the tables are forced
> to wait until one of the 4 connections gets free to export and due to
> this fact the performance of the export operation suffers from an
> enormous slow down.
>
>
> Even though I create new connections in the DTS Designer (up to 20),
> SQL Server 2K continues only using the same 4 ones which makes me
> suspect that there must be a hidden option somewhere to change this.
> Is there any way in SQL Server 2K to increase the number of maximum
> simultaneous connections to Oracle?
>
>
>
> Thank your from beforehand for reading my post.
> Greetings,
> David Grant
| |
| Allan Mitchell 2005-04-18, 8:23 pm |
| In addition to Dejan's excellent advice, note that you will need to look at how optimal hiking the tasks in parallel actually is.
If you are running on a 4 way then hiking this value to 20 would not be a good thing and you would see a lot of context switches as
things come in and off the processors. I would start with CPU Count -1
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Dejan Sarka" < dejan_please_reply_t
o_newsgroups.sarka@avtenta.si> wrote in message news:%23RTgwCFRFHA.204@TK2MSFTNGP15.phx.gbl...
> By default, DTS executes only 4 tasks concurrently. Check how to change this
> at
> http://msdn.microsoft.com/library/d...dsgnr2_60mp.asp
> (the last option on the General tab of package properties).
>
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www. SolidQualityLearning
.com
>
> "David Grant" <icebold54@hotmail.com> wrote in message
> news:18503386.0504181032.2b96883c@posting.google.com...
>
>
|
|
|
|
|