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.

Memory Utilization

Dear all,

One of the server is having 2 GB of RAM and task manager is showing 1.87 GB memory in use.

I have to migrate few databases on the same server.

With high IO Operations.

I know server require more RAM, but how can i prove that server needs more RAM ?

Regards

Mohd Sufian

To 'Prove' it, you need data. These articles will point you in the right direction to get some data.

Performance Audit
http://www.sql-server-performance.com/articles_audit.asp
http://www.sql-server-performance.com/sql_server_performance_audit10.asp

Performance Monitoring
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx Performance WP's
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx Troubleshooting Performance 2005
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp Hardware Performance CheckList
http://www.sql-server-performance.com/ss_performance_monitoring.asp Practical Solution for Monitoring
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
http://support.microsoft.com/?id=271509 Script to Monitor Blocking

Performance Tuning -Articles
http://www.sql-server-performance.com/articles_performance.asp

Performance Tuning –Hardware
http://www.sql-server-performance.com/sg_sql_server_performance_article.asp

|||

You can look at some easy PerfMon counters, such as Buffer Cache Hit Ratio, Page Life Expectancy, and Memory Grants Pending to get a better idea about whether you are seeing memory pressure. Memory pressure will also cause increased IO pressure.

Generally speaking, you can hardly go wrong by adding more RAM to SQL Server, especially if you only have`2GB of RAM installed.

Memory Utilization

Dear all,

One of the server is having 2 GB of RAM and task manager is showing 1.87 GB memory in use.

I have to migrate few databases on the same server.

With high IO Operations.

I know server require more RAM, but how can i prove that server needs more RAM ?

Regards

Mohd Sufian

To 'Prove' it, you need data. These articles will point you in the right direction to get some data.

Performance Audit
http://www.sql-server-performance.com/articles_audit.asp
http://www.sql-server-performance.com/sql_server_performance_audit10.asp

Performance Monitoring
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx Performance WP's
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx Troubleshooting Performance 2005
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp Hardware Performance CheckList
http://www.sql-server-performance.com/ss_performance_monitoring.asp Practical Solution for Monitoring
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
http://support.microsoft.com/?id=271509 Script to Monitor Blocking

Performance Tuning -Articles
http://www.sql-server-performance.com/articles_performance.asp

Performance Tuning –Hardware
http://www.sql-server-performance.com/sg_sql_server_performance_article.asp

|||

You can look at some easy PerfMon counters, such as Buffer Cache Hit Ratio, Page Life Expectancy, and Memory Grants Pending to get a better idea about whether you are seeing memory pressure. Memory pressure will also cause increased IO pressure.

Generally speaking, you can hardly go wrong by adding more RAM to SQL Server, especially if you only have`2GB of RAM installed.

Memory Usage Active - Active

Dear all,
i am planing to implement a Windows 2003 Cluster with MS SQl 2000
Enterprise Edition. I have 2 Nodes (4 * XEON MP Processors) with 8 GB
RAM per Node.
I have the need for 6 SQL instances and would like to implement a
active-active cluster. What do you think it the best way to configure
the memory for the Servers ?
I would like to run 3 instanced per Node but can i allocate 7 GB per
Node for SQL (and 1 for the Operating System) ?
What is happening then i one server fails ?
Should i plan to allocate only 3 GB per Server for SQL to make sure
that one server can handle the load for all instances if one server
fails ?
Should i use the /AWE switch only in the boot.ini to allow more than 4
GB Memory ?
or should i use the /3 GB switch as well ?
Maybe somebody can give me a hint.
Best regards,
Walter
Answers inline...
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://www.msmvps.com/clustering - Blog
"Walter" <2oznet@.gmx.de> wrote in message
news:58bb24fd.0412172354.505a3c4a@.posting.google.c om...
> Dear all,
> i am planing to implement a Windows 2003 Cluster with MS SQl 2000
> Enterprise Edition. I have 2 Nodes (4 * XEON MP Processors) with 8 GB
> RAM per Node.
> I have the need for 6 SQL instances and would like to implement a
> active-active cluster. What do you think it the best way to configure
> the memory for the Servers ?
Which instances need the most RAM? That is a lot of instance for only two
machines.

> I would like to run 3 instanced per Node but can i allocate 7 GB per
> Node for SQL (and 1 for the Operating System) ?
Again, what are the database/instance requirements.

> What is happening then i one server fails ?
>
You will then failover and have one heck of busy server!!! All 6 instances
will run, but I bet a lot slower.

> Should i plan to allocate only 3 GB per Server for SQL to make sure
> that one server can handle the load for all instances if one server
> fails ?
Can your applications handle that config? That is not a lot of memory for 3
copies of SQL to run under. Not a lot by any stretch.

> Should i use the /AWE switch only in the boot.ini to allow more than 4
> GB Memory ?
Does your hardware support /AWE? If so, yes.

> or should i use the /3 GB switch as well ?
Yes!

> Maybe somebody can give me a hint.
I would love to know your business factors that requir 6 instances of SQL.

> Best regards,
> Walter
|||There might be some dev/test cases where eveyrone wants to have the sysadmin
access to the SQL instance. It then may make sense to give each a separate
instance with the sysadmin rights to keep them isolated from each other. But
I agree you really need to think hard whether there is sufficient rationale
for having six SQL instances in a two-node cluster with 4-way servers.
Linchi
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:usaAppQ5EHA.3472@.TK2MSFTNGP09.phx.gbl...
> Answers inline...
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://www.msmvps.com/clustering - Blog
> "Walter" <2oznet@.gmx.de> wrote in message
> news:58bb24fd.0412172354.505a3c4a@.posting.google.c om...
> Which instances need the most RAM? That is a lot of instance for only two
> machines.
>
> Again, what are the database/instance requirements.
>
> You will then failover and have one heck of busy server!!! All 6 instances
> will run, but I bet a lot slower.
>
> Can your applications handle that config? That is not a lot of memory for
> 3 copies of SQL to run under. Not a lot by any stretch.
>
> Does your hardware support /AWE? If so, yes.
>
> Yes!
>
> I would love to know your business factors that requir 6 instances of SQL.
>
>

Memory leak or performance issue?

Dear all
My some users are complaining that when they restart their sql server
it works good for some time and then as time pass performance degrades.
Again they resrat and it works fine and as time goes it becomes slow.
Why when I restart it works fine and as time goes it becomes slow?
Is there any memory leak or any other problem for this?
Regards
AmishHi,
What is databaser server configuration hw and sw ?
- Windows version ?
- SQL Server version and SP ?
- How many memory do you want ?
- How many processors do you want ?
** * Esta msg foi útil pra você ? Então marque-a como tal. ***
Regards,
Rodrigo Fernandes
"amish" wrote:
> Dear all
> My some users are complaining that when they restart their sql server
> it works good for some time and then as time pass performance degrades.
> Again they resrat and it works fine and as time goes it becomes slow.
> Why when I restart it works fine and as time goes it becomes slow?
> Is there any memory leak or any other problem for this?
>
> Regards
> Amish
>

Memory leak or performance issue?

Dear all
My some users are complaining that when they restart their sql server
it works good for some time and then as time pass performance degrades.
Again they resrat and it works fine and as time goes it becomes slow.
Why when I restart it works fine and as time goes it becomes slow?
Is there any memory leak or any other problem for this?
Regards
AmishHi,
What is databaser server configuration hw and sw ?
- Windows version ?
- SQL Server version and SP ?
- How many memory do you want ?
- How many processors do you want ?
** * Esta msg foi útil pra você ? Ent?o marque-a como tal. ***
Regards,
Rodrigo Fernandes
"amish" wrote:

> Dear all
> My some users are complaining that when they restart their sql server
> it works good for some time and then as time pass performance degrades.
> Again they resrat and it works fine and as time goes it becomes slow.
> Why when I restart it works fine and as time goes it becomes slow?
> Is there any memory leak or any other problem for this?
>
> Regards
> Amish
>