Merge join: nr of output rows unchanged when amount of input changes

Dear all,

I created a package that seems to work fine with a small amount of data. When I run the package however with more data (as in production) the merge join output is limites to 9963 rows, no matter if I change the number of input rows.

Situation as follows.

The package has 2 OLE DB Sources, in which SQL-statements have been defined in order to retrieve the data.

The flow of source 1 is: retrieving source data -> trimming (non-key) columns -> sorting on the key-columns.

The flow of source 2 is: retrieving source data -> deriving 2 new columns -> aggregating the data to the level of source 1 -> sorting on the key columns.

Then both flows are merged and other steps are performed.

If I test with just a couple of rows it works fine. But when I change the where-clause in the data source retrieval, so that the number of rows is for instance 15000 or 150000 the number of rows after the merge join is 9963.

When I run the package in debug-mode the step is colored green, nevertheless an error is displayed:

Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Merge Join" (4703) failed with error code 0xC0047020. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

To be honest, a few more errormessages appear, but they don't seem related to this issue. The package stops running after some 6000 rows have been written to the destination.

Any help will be greatly appreciated.

Kind regards,

Albert.

If you could post the full error output here, that would probably be helpful. Sometimes it is the "big view" that helps point you at the cause of the problem, especially since many of SSIS's error messages are not particularly transparent. The earlier errors often show what triggered the later errors, even if they do not appear directly related.

|||Can you also include details of the next task in the pipeline, the one that accepts the ~9000 rows. It sounds like it's failing on the first buffer it receives as input.|||

Ok, for the big picture: I feel like a fool.

Solving one of the other errors solved the reported issue as well.

Apologies for bothering you.

Merge Join Output Bug?

I've run into something that looks like a bug to me but I wanted to run it by the board:

Merge join 2 sorted tables.

Table1: ColumnA : Sort Order 1, ColumnB Sort Order 2

Table2 : ColumnA: Sort Order 1, ColumnB Sort Order 2, ColumnC not sorted

Merge Join the two tables on ColumnA and ColumnB...

Choose the following as output columns

A + B + C = works

C = works

A + C = works

B + C = NOT work.. error message: The column with the SortKeyPosition value of 0 is not valid. It should be 2.

Basically if you choose one or more of the sorted columns in the output at least one of them has to be the column with Sort position 1 or you'll get that error.

Is this a bug or intentional? If you do not have sort column 1 in the output that output could no longer be considered sorted... so perhaps the error is related to that (instead of error I'd expect some warning about the sorting). Interesting that it lets you choose C only becuase that also makes the output unsorted.

I see your point Chris.

I think it is intential -

. The reason why B+C not work is because column B has a non-zero sortKeyPosition which indicates the output (to which B belongs) should be sorted (in other words, the output's "isSorted" property is true), but the output can not find a column with SortKeyPosition 1

. As for why C column only works is because the output is then not sorted.

If you think the error message is not very helpful, please log a customer issue through our connect website http://connect.microsoft.com/SQLServer and your request will be addressed soon as appropriate.

Thanks

wenyang

Merge Join Output Bug?

I've run into something that looks like a bug to me but I wanted to run it by the board:

Merge join 2 sorted tables.

Table1: ColumnA : Sort Order 1, ColumnB Sort Order 2

Table2 : ColumnA: Sort Order 1, ColumnB Sort Order 2, ColumnC not sorted

Merge Join the two tables on ColumnA and ColumnB...

Choose the following as output columns

A + B + C = works

C = works

A + C = works

B + C = NOT work.. error message: The column with the SortKeyPosition value of 0 is not valid. It should be 2.

Basically if you choose one or more of the sorted columns in the output at least one of them has to be the column with Sort position 1 or you'll get that error.

Is this a bug or intentional? If you do not have sort column 1 in the output that output could no longer be considered sorted... so perhaps the error is related to that (instead of error I'd expect some warning about the sorting). Interesting that it lets you choose C only becuase that also makes the output unsorted.

I see your point Chris.

I think it is intential -

. The reason why B+C not work is because column B has a non-zero sortKeyPosition which indicates the output (to which B belongs) should be sorted (in other words, the output's "isSorted" property is true), but the output can not find a column with SortKeyPosition 1

. As for why C column only works is because the output is then not sorted.

If you think the error message is not very helpful, please log a customer issue through our connect website http://connect.microsoft.com/SQLServer and your request will be addressed soon as appropriate.

Thanks

wenyang

memory usage of sqlservr.exe

Hi,

Can we limit the memory usage of sqlservr.exe ?
I am using MSDE to run a 24x7 system and the sqlservr.exe takes around 200,000K (as shown in task manager). My concern is whether such usage would have adverse effect on other processes.

The system configuration is:
PIII-700MHz, 256MB RAM
MSDE 2000 over Win NT 4.0 (SP6)
Thanks in advance.

regards,
henryThere is a way to limit how much memory sql server can use in a given machine. But judging from the hardware configuration of your server, the machine is barely enough to run sql 2000. I don't know how much resource you need to run your database, but since you've mentioned 'other processes' in the box I would think that box has other things running. The slowdown could very well be caused by reaching the limit of your hardware capacities.

To set a fixed amount of memory

Expand a server group.

Right-click a server, and then click Properties.

Click the Memory tab.

Click Use a fixed memory size (MB), and then position the fixed memory slider.|||Hi Joe!

Thaks for your valuable input.

Yes, there is are several other exes running alongwith the msde.
Actually, i have not noticed slowdown in any process. But due to the varying memory consumption of the sqlmangr.exe, i am wondering whether the fixed memory option should be applied.

Please advise.

Thanks and regards,
henry|||The memory usage you see in Task Manager is simple the max of what you give to sql. SQL will allocate as much memory as you define in the Max Memory property. The actual usage of memory in sql is different. If you want to see the actual usage of memory by sql, run the performance monitor. If you don't see any slowdown, you can give it a try to lower the max memory.|||Hi!

I used the Performance monitor and compared the Private bytes with Dynamic memory and with fixed memory.
Except for the Private bytes and working set which get restricted in fixed memory case, i could not differentiate between the two options.
On reading the SQL online books, i found that the default dynamic setting is best for the system and i think i shall keep the setting as it is.

Thanks and regards,
henry|||Probably sounds stupid, but still, wouldn't hurt restarting that NT server once a week, even if it needs to be up 24x7. Could help improve overall performance. Generally they do this during Sunday night, when server usage is reduced.

Best regards!

Memory of SQL server

The configuration of SQL server is
1. Windows 2000 server Standard version
2. SQL server 2000 standard
3. 3G memory
4. mainly run by IIS and SQL, sometimes word or excel triggered by IIS.
i have allocated all the memory to the SQL server at Enterprise manager.
i find at task manager the SQL uses up 1.8G memory at maximum.
The database size at disk is 600m
i find the server runs a query at 30 minutes.
After reboot, the query runs at 15 minutues, maybe release of some memory.
Can i say the memory is not sufficient for the SQL?
If yes, i know there is memory limitation from standard version of Windows
2K and SQL 2K, should i consider Enterprise version of Windows Server and
SQL to allow more memory.
Grateful if you could kindly give me some advices. Thanks.
Tony
Tony
First of all I'd suggest you staring to tune a 'bad' query , see if the
optomizer uses indexes , how efficient is an execution plan?
"tony wong" <x34@.netvigator.com> wrote in message
news:ebKNJg3NHHA.5064@.TK2MSFTNGP04.phx.gbl...
> The configuration of SQL server is
> 1. Windows 2000 server Standard version
> 2. SQL server 2000 standard
> 3. 3G memory
> 4. mainly run by IIS and SQL, sometimes word or excel triggered by IIS.
> i have allocated all the memory to the SQL server at Enterprise manager.
> i find at task manager the SQL uses up 1.8G memory at maximum.
> The database size at disk is 600m
> i find the server runs a query at 30 minutes.
> After reboot, the query runs at 15 minutues, maybe release of some memory.
> Can i say the memory is not sufficient for the SQL?
> If yes, i know there is memory limitation from standard version of Windows
> 2K and SQL 2K, should i consider Enterprise version of Windows Server and
> SQL to allow more memory.
> Grateful if you could kindly give me some advices. Thanks.
> Tony
>
|||On Sun, 14 Jan 2007 09:05:08 +0800, "tony wong" <x34@.netvigator.com>
wrote:

>The configuration of SQL server is
>1. Windows 2000 server Standard version
>2. SQL server 2000 standard
>3. 3G memory
>4. mainly run by IIS and SQL, sometimes word or excel triggered by IIS.
>i have allocated all the memory to the SQL server at Enterprise manager.
>i find at task manager the SQL uses up 1.8G memory at maximum.
>The database size at disk is 600m
>i find the server runs a query at 30 minutes.
>After reboot, the query runs at 15 minutues, maybe release of some memory.
>Can i say the memory is not sufficient for the SQL?
>If yes, i know there is memory limitation from standard version of Windows
>2K and SQL 2K, should i consider Enterprise version of Windows Server and
>SQL to allow more memory.
>Grateful if you could kindly give me some advices. Thanks.
Generally you'd expect it to run slower after reboot, as the cache
would be empty and physical IO is a lot slower.
I'd say Uri has the right idea, you might be producing a huge join
that preferentially should be kept in memory, or missing an index that
causes some other unnatural memory/cache configuration to be optimal.
Generally these can be addressed by tuning the query.
Of course, a multitude of sins can be hidden if you have enough RAM!
J.
|||> i find the server runs a query at 30 minutes.
> After reboot, the query runs at 15 minutues,
Can this be consistently reproduced?
Linchi
"tony wong" wrote:

> The configuration of SQL server is
> 1. Windows 2000 server Standard version
> 2. SQL server 2000 standard
> 3. 3G memory
> 4. mainly run by IIS and SQL, sometimes word or excel triggered by IIS.
> i have allocated all the memory to the SQL server at Enterprise manager.
> i find at task manager the SQL uses up 1.8G memory at maximum.
> The database size at disk is 600m
> i find the server runs a query at 30 minutes.
> After reboot, the query runs at 15 minutues, maybe release of some memory.
> Can i say the memory is not sufficient for the SQL?
> If yes, i know there is memory limitation from standard version of Windows
> 2K and SQL 2K, should i consider Enterprise version of Windows Server and
> SQL to allow more memory.
> Grateful if you could kindly give me some advices. Thanks.
> Tony
>
>

Memory of SQL server

The configuration of SQL server is
1. Windows 2000 server Standard version
2. SQL server 2000 standard
3. 3G memory
4. mainly run by IIS and SQL, sometimes word or excel triggered by IIS.
i have allocated all the memory to the SQL server at Enterprise manager.
i find at task manager the SQL uses up 1.8G memory at maximum.
The database size at disk is 600m
i find the server runs a query at 30 minutes.
After reboot, the query runs at 15 minutues, maybe release of some memory.
Can i say the memory is not sufficient for the SQL?
If yes, i know there is memory limitation from standard version of Windows
2K and SQL 2K, should i consider Enterprise version of Windows Server and
SQL to allow more memory.
Grateful if you could kindly give me some advices. Thanks.
TonyTony
First of all I'd suggest you staring to tune a 'bad' query , see if the
optomizer uses indexes , how efficient is an execution plan?
"tony wong" <x34@.netvigator.com> wrote in message
news:ebKNJg3NHHA.5064@.TK2MSFTNGP04.phx.gbl...
> The configuration of SQL server is
> 1. Windows 2000 server Standard version
> 2. SQL server 2000 standard
> 3. 3G memory
> 4. mainly run by IIS and SQL, sometimes word or excel triggered by IIS.
> i have allocated all the memory to the SQL server at Enterprise manager.
> i find at task manager the SQL uses up 1.8G memory at maximum.
> The database size at disk is 600m
> i find the server runs a query at 30 minutes.
> After reboot, the query runs at 15 minutues, maybe release of some memory.
> Can i say the memory is not sufficient for the SQL?
> If yes, i know there is memory limitation from standard version of Windows
> 2K and SQL 2K, should i consider Enterprise version of Windows Server and
> SQL to allow more memory.
> Grateful if you could kindly give me some advices. Thanks.
> Tony
>|||On Sun, 14 Jan 2007 09:05:08 +0800, "tony wong" <x34@.netvigator.com>
wrote:

>The configuration of SQL server is
>1. Windows 2000 server Standard version
>2. SQL server 2000 standard
>3. 3G memory
>4. mainly run by IIS and SQL, sometimes word or excel triggered by IIS.
>i have allocated all the memory to the SQL server at Enterprise manager.
>i find at task manager the SQL uses up 1.8G memory at maximum.
>The database size at disk is 600m
>i find the server runs a query at 30 minutes.
>After reboot, the query runs at 15 minutues, maybe release of some memory.
>Can i say the memory is not sufficient for the SQL?
>If yes, i know there is memory limitation from standard version of Windows
>2K and SQL 2K, should i consider Enterprise version of Windows Server and
>SQL to allow more memory.
>Grateful if you could kindly give me some advices. Thanks.
Generally you'd expect it to run slower after reboot, as the cache
would be empty and physical IO is a lot slower.
I'd say Uri has the right idea, you might be producing a huge join
that preferentially should be kept in memory, or missing an index that
causes some other unnatural memory/cache configuration to be optimal.
Generally these can be addressed by tuning the query.
Of course, a multitude of sins can be hidden if you have enough RAM!
J.|||> i find the server runs a query at 30 minutes.
> After reboot, the query runs at 15 minutues,
Can this be consistently reproduced?
Linchi
"tony wong" wrote:

> The configuration of SQL server is
> 1. Windows 2000 server Standard version
> 2. SQL server 2000 standard
> 3. 3G memory
> 4. mainly run by IIS and SQL, sometimes word or excel triggered by IIS.
> i have allocated all the memory to the SQL server at Enterprise manager.
> i find at task manager the SQL uses up 1.8G memory at maximum.
> The database size at disk is 600m
> i find the server runs a query at 30 minutes.
> After reboot, the query runs at 15 minutues, maybe release of some memory.
> Can i say the memory is not sufficient for the SQL?
> If yes, i know there is memory limitation from standard version of Windows
> 2K and SQL 2K, should i consider Enterprise version of Windows Server and
> SQL to allow more memory.
> Grateful if you could kindly give me some advices. Thanks.
> Tony
>
>

Memory of SQL server

The configuration of SQL server is
1. Windows 2000 server Standard version
2. SQL server 2000 standard
3. 3G memory
4. mainly run by IIS and SQL, sometimes word or excel triggered by IIS.
i have allocated all the memory to the SQL server at Enterprise manager.
i find at task manager the SQL uses up 1.8G memory at maximum.
The database size at disk is 600m
i find the server runs a query at 30 minutes.
After reboot, the query runs at 15 minutues, maybe release of some memory.
Can i say the memory is not sufficient for the SQL?
If yes, i know there is memory limitation from standard version of Windows
2K and SQL 2K, should i consider Enterprise version of Windows Server and
SQL to allow more memory.
Grateful if you could kindly give me some advices. Thanks.
TonyTony
First of all I'd suggest you staring to tune a 'bad' query , see if the
optomizer uses indexes , how efficient is an execution plan?
"tony wong" <x34@.netvigator.com> wrote in message
news:ebKNJg3NHHA.5064@.TK2MSFTNGP04.phx.gbl...
> The configuration of SQL server is
> 1. Windows 2000 server Standard version
> 2. SQL server 2000 standard
> 3. 3G memory
> 4. mainly run by IIS and SQL, sometimes word or excel triggered by IIS.
> i have allocated all the memory to the SQL server at Enterprise manager.
> i find at task manager the SQL uses up 1.8G memory at maximum.
> The database size at disk is 600m
> i find the server runs a query at 30 minutes.
> After reboot, the query runs at 15 minutues, maybe release of some memory.
> Can i say the memory is not sufficient for the SQL?
> If yes, i know there is memory limitation from standard version of Windows
> 2K and SQL 2K, should i consider Enterprise version of Windows Server and
> SQL to allow more memory.
> Grateful if you could kindly give me some advices. Thanks.
> Tony
>|||On Sun, 14 Jan 2007 09:05:08 +0800, "tony wong" <x34@.netvigator.com>
wrote:
>The configuration of SQL server is
>1. Windows 2000 server Standard version
>2. SQL server 2000 standard
>3. 3G memory
>4. mainly run by IIS and SQL, sometimes word or excel triggered by IIS.
>i have allocated all the memory to the SQL server at Enterprise manager.
>i find at task manager the SQL uses up 1.8G memory at maximum.
>The database size at disk is 600m
>i find the server runs a query at 30 minutes.
>After reboot, the query runs at 15 minutues, maybe release of some memory.
>Can i say the memory is not sufficient for the SQL?
>If yes, i know there is memory limitation from standard version of Windows
>2K and SQL 2K, should i consider Enterprise version of Windows Server and
>SQL to allow more memory.
>Grateful if you could kindly give me some advices. Thanks.
Generally you'd expect it to run slower after reboot, as the cache
would be empty and physical IO is a lot slower.
I'd say Uri has the right idea, you might be producing a huge join
that preferentially should be kept in memory, or missing an index that
causes some other unnatural memory/cache configuration to be optimal.
Generally these can be addressed by tuning the query.
Of course, a multitude of sins can be hidden if you have enough RAM!
J.