Merge Join question

Hi, All,

In the data flow of Intergration Service, my understanding is that the "Merge Join" only does the "join", there is no way to input the "where" clause. If I need to input the the "Where" clause or selection criteria, how should I do it in Integration Service?

TIA,

Maybe put in a conditional split transformation before you go into the merge... Filter your rows there.|||

john zhang wrote:

Hi, All,

In the data flow of Intergration Service, my understanding is that the "Merge Join" only does the "join", there is no way to input the "where" clause. If I need to input the the "Where" clause or selection criteria, how should I do it in Integration Service?

TIA,

Depending in the source you are using; I would use a SQL statement right in the surce components to 'pull' only required rows; otherwise I would use conditional split transforms within the data flow.

Rafael Salas

|||Thanks Phil.|||

Thanks Rafael for the input. Now I have "conditional split" worked for me even it's inconvenient

|||

john zhang wrote:

Thanks Rafael for the input. Now I have "conditional split" worked for me even it's inconvenient

Why is it inconvenient?

-Jamie

|||

With other popular ETL tool, in one transformation, you can do both "join" and "where". But in SQL Server 2005 Integration service, you have to use two transformation to finish the task.

|||

john zhang wrote:

With other popular ETL tool, in one transformation, you can do both "join" and "where". But in SQL Server 2005 Integration service, you have to use two transformation to finish the task.

Why is that a bad thing? Is having 2 boxes on the design surface really such a bad thing?

SSIS components are deliberately "atomic". By that I mean they do one single operation and thus the aim is to build data-flows which are greater than the sum of their parts. Just because the "join" and "where" is in the same box then it doesn't mean its doing any less work. In fact it might actually be doing more because you are using a CPU to do two operations whereas in SSIS this may not be the case.

Please don't think I'm trying to say that you're wrong - this is just my opinion and in a way I'm playing devil's advocate. I think its a useful discussion to have.

-Jamie

|||

here's a discussion, the merge join would be even better if it could join more than 2 tables at a time.

I have many packages that require a left join to 5 or 6 other tables and the only way to do this is to use a merge join for the first two tables, then get the data from the 3rd table, add another merge join transform, then join the output of the first merge join to the data from the third table to get a 3 table join and so on...

It works ok though..

|||

bobbins wrote:

here's a discussion, the merge join would be even better if it could join more than 2 tables at a time.

I have many packages that require a left join to 5 or 6 other tables and the only way to do this is to use a merge join for the first two tables, then get the data from the 3rd table, add another merge join transform, then join the output of the first merge join to the data from the third table to get a 3 table join and so on...

It works ok though..

Nice idea. Submit it at http://connect.microsoft.com

-Jamie

sql

Memory usage SQL Server 2000

Hi,
We have a scenario wherein RAM usage is creeping up until the DB has to be restarted (SQL server 2000 no service packs). There are 15 concurrent users connecting via ODBC (TCP/IP).
Please can anyone give any tips as to how we can track down the problem?
Thanks.We are also having the same problem on a couple of our MSSQL Server 2000 machines. All help in this would be greatly appreciated.
Sam|||You need to have a server with the following configuration:
It must include Backup Drive PV110T DLT1 40/80GB INTERNAL
and also the server preferrably must contain the following:
PowerEdge 6400 PIII Xeon 700MHz/2MB
2 x Terminator Card PE64x0
Add. CPU PIII Xeon 700MHz/2MB
2GB (4*512) 133MHz SDRAM
48x IDE CD-ROM
PERC3/DC 128MB RAID, U160 SCSI
4 x 36GB SCSI HDD, 15K RPM, 80-pin
Embedded Intel Pro 100+ PCI Ethernet NIC
and other ordinary devices.

You must have a very excellent RAM Capacity as mentioned above to avoid the slow performance of your service and to allow all users maintain their own work without any complaints.

Wish you good luck.

Memory usage

Why the memory usage in task manager shows significant difference before and
after the restart of SQL Server service ? Is that due to the memory leak ?
I am running SQL Server 2000 sp3a
Thanks very much,
natasa
Natasa,
If SQL Server's memory is set to be dynamic (default), it will grow to the
min boundary then up-to the max boundary (if needed) then fluctuate in
between.
HTH
Jerry
"natasa" <natasa@.discussions.microsoft.com> wrote in message
news:C0197641-02D7-460F-8346-E3609CD4E15C@.microsoft.com...
> Why the memory usage in task manager shows significant difference before
> and
> after the restart of SQL Server service ? Is that due to the memory leak ?
> I am running SQL Server 2000 sp3a
> Thanks very much,
> natasa
|||also if you have /PAE enabled (> 4GB RAM) , Task Manager will say a silly
amount for SQLServr.exe - around 100mb. In this scenario Perfmon/SQL Server
Memory Manager/Total Server Memory is the correct thing to look at
cheers,
Andy
"natasa" <natasa@.discussions.microsoft.com> wrote in message
news:C0197641-02D7-460F-8346-E3609CD4E15C@.microsoft.com...
> Why the memory usage in task manager shows significant difference before
> and
> after the restart of SQL Server service ? Is that due to the memory leak ?
> I am running SQL Server 2000 sp3a
> Thanks very much,
> natasa
|||to regulate SQL memory usage
use WSRM windows system resource manager
for win2k3
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"natasa" wrote:

> Why the memory usage in task manager shows significant difference before and
> after the restart of SQL Server service ? Is that due to the memory leak ?
> I am running SQL Server 2000 sp3a
> Thanks very much,
> natasa

Memory usage

Win2K Advanced Server , SQL2K Enterprise Edition(SP3). 80 GB database, 3.7
GB RAM. Cluster Service is running but currently there is no failover
server. Currently the Perfmon counters Target Server Memeory and Total
Server Memory are about 1682534 KB with only slight flucuations. AWE is not
enabled nor is the /3GB switch. The server's memory setting is 'Dynamically
configure SQL Server memory'. Since this is a fairly active db I expect the
memory usage to be closer to 2 GB.
Why is it not maxing out memory up to 2 GB?
I eventually want to enable memory greater than 2 GB. I believe all I need
to do is enable AWE using sp_configure and restart the instance. Do I need
the /3GB switch also? Should I set the max memory setting or leave it
dynamic? If I set the max memory setting what should I set it to?
Buffer Cache Hit Ratio - 98%+
Average Page Life 20 - 1000 secs.
CPU - 20% - 50 %
It has been a while since I have worked on a server that has had memory =
config set to 2GB, but if I remember correctly the servers that I worked =
on that were limited to 2GB of usage showed less (somewhere between 1.6 =
and 1.8GB). I think what you are seeing is normal.
You will want to add /3GB to the boot.ini. Yes, you would need to use =
sp_configure to enable AWE. You will also want to set min and max =
server memory (MB). Try setting min/max server memory to 3GB. =20
To be honest, I don't know that you will see a large performance =
increase in going from 2GB to 3GB of RAM on a server with an 80GB =
database. Then again, it depends on how the database is used.
--=20
Keith
"Dean" <deann@.dtn.com> wrote in message =
news:eDKK2CGNEHA.640@.TK2MSFTNGP12.phx.gbl...
> Win2K Advanced Server , SQL2K Enterprise Edition(SP3). 80 GB database, =
3.7
> GB RAM. Cluster Service is running but currently there is no failover
> server. Currently the Perfmon counters Target Server Memeory and Total
> Server Memory are about 1682534 KB with only slight flucuations. AWE =
is not
> enabled nor is the /3GB switch. The server's memory setting is =
'Dynamically
> configure SQL Server memory'. Since this is a fairly active db I =
expect the
> memory usage to be closer to 2 GB.
>=20
> Why is it not maxing out memory up to 2 GB?
>=20
> I eventually want to enable memory greater than 2 GB. I believe all I =
need
> to do is enable AWE using sp_configure and restart the instance. Do I =
need
> the /3GB switch also? Should I set the max memory setting or leave it
> dynamic? If I set the max memory setting what should I set it to?
>=20
> Buffer Cache Hit Ratio - 98%+
> Average Page Life 20 - 1000 secs.
> CPU - 20% - 50 %
>=20
>

Memory usage

Win2K Advanced Server , SQL2K Enterprise Edition(SP3). 80 GB database, 3.7
GB RAM. Cluster Service is running but currently there is no failover
server. Currently the Perfmon counters Target Server Memeory and Total
Server Memory are about 1682534 KB with only slight flucuations. AWE is not
enabled nor is the /3GB switch. The server's memory setting is 'Dynamically
configure SQL Server memory'. Since this is a fairly active db I expect the
memory usage to be closer to 2 GB.
Why is it not maxing out memory up to 2 GB?
I eventually want to enable memory greater than 2 GB. I believe all I need
to do is enable AWE using sp_configure and restart the instance. Do I need
the /3GB switch also? Should I set the max memory setting or leave it
dynamic? If I set the max memory setting what should I set it to?
Buffer Cache Hit Ratio - 98%+
Average Page Life 20 - 1000 secs.
CPU - 20% - 50 %It has been a while since I have worked on a server that has had memory =
config set to 2GB, but if I remember correctly the servers that I worked =
on that were limited to 2GB of usage showed less (somewhere between 1.6 =
and 1.8GB). I think what you are seeing is normal.
You will want to add /3GB to the boot.ini. Yes, you would need to use =
sp_configure to enable AWE. You will also want to set min and max =
server memory (MB). Try setting min/max server memory to 3GB. =20
To be honest, I don't know that you will see a large performance =
increase in going from 2GB to 3GB of RAM on a server with an 80GB =
database. Then again, it depends on how the database is used.
--=20
Keith
"Dean" <deann@.dtn.com> wrote in message =
news:eDKK2CGNEHA.640@.TK2MSFTNGP12.phx.gbl...
> Win2K Advanced Server , SQL2K Enterprise Edition(SP3). 80 GB database, =
3.7
> GB RAM. Cluster Service is running but currently there is no failover
> server. Currently the Perfmon counters Target Server Memeory and Total
> Server Memory are about 1682534 KB with only slight flucuations. AWE =
is not
> enabled nor is the /3GB switch. The server's memory setting is =
'Dynamically
> configure SQL Server memory'. Since this is a fairly active db I =
expect the
> memory usage to be closer to 2 GB.
>=20
> Why is it not maxing out memory up to 2 GB?
>=20
> I eventually want to enable memory greater than 2 GB. I believe all I =
need
> to do is enable AWE using sp_configure and restart the instance. Do I =
need
> the /3GB switch also? Should I set the max memory setting or leave it
> dynamic? If I set the max memory setting what should I set it to?
>=20
> Buffer Cache Hit Ratio - 98%+
> Average Page Life 20 - 1000 secs.
> CPU - 20% - 50 %
>=20
>

Memory usage

Why the memory usage in task manager shows significant difference before and
after the restart of SQL Server service ? Is that due to the memory leak ?
I am running SQL Server 2000 sp3a
Thanks very much,
natasaNatasa,
If SQL Server's memory is set to be dynamic (default), it will grow to the
min boundary then up-to the max boundary (if needed) then fluctuate in
between.
HTH
Jerry
"natasa" <natasa@.discussions.microsoft.com> wrote in message
news:C0197641-02D7-460F-8346-E3609CD4E15C@.microsoft.com...
> Why the memory usage in task manager shows significant difference before
> and
> after the restart of SQL Server service ? Is that due to the memory leak ?
> I am running SQL Server 2000 sp3a
> Thanks very much,
> natasa|||also if you have /PAE enabled (> 4GB RAM) , Task Manager will say a silly
amount for SQLServr.exe - around 100mb. In this scenario Perfmon/SQL Server
Memory Manager/Total Server Memory is the correct thing to look at
cheers,
Andy
"natasa" <natasa@.discussions.microsoft.com> wrote in message
news:C0197641-02D7-460F-8346-E3609CD4E15C@.microsoft.com...
> Why the memory usage in task manager shows significant difference before
> and
> after the restart of SQL Server service ? Is that due to the memory leak ?
> I am running SQL Server 2000 sp3a
> Thanks very much,
> natasa|||to regulate SQL memory usage
use WSRM windows system resource manager
for win2k3
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"natasa" wrote:

> Why the memory usage in task manager shows significant difference before a
nd
> after the restart of SQL Server service ? Is that due to the memory leak ?
> I am running SQL Server 2000 sp3a
> Thanks very much,
> natasa

memory usage

Hi, can I get the SQL service memory usage?
and which one holding lots of memory?See Profiler for this
Otherwise Set Show Execution plan at QA and run the query
Madhivanan|||See Profiler for this
Otherwise Set Show Execution plan at QA and run the query
Madhivanan|||You can use 'dbcc memorystatus':
http://support.microsoft.com/kb/271624
-oj
"js" <js@.someone@.hotmail.com> wrote in message
news:%23%23YwunrUFHA.4000@.TK2MSFTNGP14.phx.gbl...
> Hi, can I get the SQL service memory usage?
> and which one holding lots of memory?
>|||Hi,
You can use PERFMON to get the memory usage.
Thanks
Hari
SQL Server MVP
"js" <js@.someone@.hotmail.com> wrote in message
news:%23%23YwunrUFHA.4000@.TK2MSFTNGP14.phx.gbl...
> Hi, can I get the SQL service memory usage?
> and which one holding lots of memory?
>|||Hari Whne I run PERFMON at Query Analyser,
I get the error
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'PERFMON'.
Madhivanan|||Hari was referring to the Windows tool "Performance Monitor", aka "System Mo
nitor".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1115459019.983953.289570@.o13g2000cwo.googlegroups.com...
> Hari Whne I run PERFMON at Query Analyser,
> I get the error
> Server: Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'PERFMON'.
> Madhivanan
>|||that's it. thanks for the info
"oj" <nospam_ojngo@.home.com> wrote in message
news:%231G47RtUFHA.3428@.TK2MSFTNGP10.phx.gbl...
> You can use 'dbcc memorystatus':
> http://support.microsoft.com/kb/271624
>
> --
> -oj
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:%23%23YwunrUFHA.4000@.TK2MSFTNGP14.phx.gbl...
>

Memory usage

Win2K Advanced Server , SQL2K Enterprise Edition(SP3). 80 GB database, 3.7
GB RAM. Cluster Service is running but currently there is no failover
server. Currently the Perfmon counters Target Server Memeory and Total
Server Memory are about 1682534 KB with only slight flucuations. AWE is not
enabled nor is the /3GB switch. The server's memory setting is 'Dynamically
configure SQL Server memory'. Since this is a fairly active db I expect the
memory usage to be closer to 2 GB.
Why is it not maxing out memory up to 2 GB?
I eventually want to enable memory greater than 2 GB. I believe all I need
to do is enable AWE using sp_configure and restart the instance. Do I need
the /3GB switch also? Should I set the max memory setting or leave it
dynamic? If I set the max memory setting what should I set it to?
Buffer Cache Hit Ratio - 98%+
Average Page Life 20 - 1000 secs.
CPU - 20% - 50 %It has been a while since I have worked on a server that has had memory =config set to 2GB, but if I remember correctly the servers that I worked =on that were limited to 2GB of usage showed less (somewhere between 1.6 =and 1.8GB). I think what you are seeing is normal.
You will want to add /3GB to the boot.ini. Yes, you would need to use =sp_configure to enable AWE. You will also want to set min and max =server memory (MB). Try setting min/max server memory to 3GB.
To be honest, I don't know that you will see a large performance =increase in going from 2GB to 3GB of RAM on a server with an 80GB =database. Then again, it depends on how the database is used.
-- Keith
"Dean" <deann@.dtn.com> wrote in message =news:eDKK2CGNEHA.640@.TK2MSFTNGP12.phx.gbl...
> Win2K Advanced Server , SQL2K Enterprise Edition(SP3). 80 GB database, =3.7
> GB RAM. Cluster Service is running but currently there is no failover
> server. Currently the Perfmon counters Target Server Memeory and Total
> Server Memory are about 1682534 KB with only slight flucuations. AWE =is not
> enabled nor is the /3GB switch. The server's memory setting is ='Dynamically
> configure SQL Server memory'. Since this is a fairly active db I =expect the
> memory usage to be closer to 2 GB.
> > Why is it not maxing out memory up to 2 GB?
> > I eventually want to enable memory greater than 2 GB. I believe all I =need
> to do is enable AWE using sp_configure and restart the instance. Do I =need
> the /3GB switch also? Should I set the max memory setting or leave it
> dynamic? If I set the max memory setting what should I set it to?
> > Buffer Cache Hit Ratio - 98%+
> Average Page Life 20 - 1000 secs.
> CPU - 20% - 50 %
> >

Memory usage

Why the memory usage in task manager shows significant difference before and
after the restart of SQL Server service ? Is that due to the memory leak ?
I am running SQL Server 2000 sp3a
Thanks very much,
natasaNatasa,
If SQL Server's memory is set to be dynamic (default), it will grow to the
min boundary then up-to the max boundary (if needed) then fluctuate in
between.
HTH
Jerry
"natasa" <natasa@.discussions.microsoft.com> wrote in message
news:C0197641-02D7-460F-8346-E3609CD4E15C@.microsoft.com...
> Why the memory usage in task manager shows significant difference before
> and
> after the restart of SQL Server service ? Is that due to the memory leak ?
> I am running SQL Server 2000 sp3a
> Thanks very much,
> natasa|||also if you have /PAE enabled (> 4GB RAM) , Task Manager will say a silly
amount for SQLServr.exe - around 100mb. In this scenario Perfmon/SQL Server
Memory Manager/Total Server Memory is the correct thing to look at
cheers,
Andy
"natasa" <natasa@.discussions.microsoft.com> wrote in message
news:C0197641-02D7-460F-8346-E3609CD4E15C@.microsoft.com...
> Why the memory usage in task manager shows significant difference before
> and
> after the restart of SQL Server service ? Is that due to the memory leak ?
> I am running SQL Server 2000 sp3a
> Thanks very much,
> natasa|||to regulate SQL memory usage
use WSRM windows system resource manager
for win2k3
--
thanks,
--
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"natasa" wrote:
> Why the memory usage in task manager shows significant difference before and
> after the restart of SQL Server service ? Is that due to the memory leak ?
> I am running SQL Server 2000 sp3a
> Thanks very much,
> natasa