Merge join

Hi, folks:
I am now testing merge join with results not expected.
I have two OLE data source (one from Oracle, one from SQLSERVER)
then I do a merge join with the key LOCATION_ID

OLE DATA SOURCE 1 OLE DATA SOURCE 2
-- --

Location_id volume location_id shortname

123 3.5 256 well_far_away
256 4.6

My expected result for my inner join is
256 4.6 well_far_away
But I got no rows

If I change it to full outer join
I got this

123 3.5 null
256 4.6 null
null null well_far_away
What did I do wrong and should I do a merge join like this?

Well from the outer join results it appears that mergejoin is not detecting that the location_id from data source 2 matches either of the rows on data source 1. Can you add the location_id from data source 2 to the output (for testing purposes only, of course) and see what the value is displayed as? You could also put a data viewer on the path from data source 2 to the mergejoin and see what the value coming into the mergejoin is. Looking at this I would guess that the location_id from data source 2 is not what you are expecting, but the only way to know is to look at it.

HTH,
Matt

|||

A simple Merge Join from two OLE-DB sources with the data you describe works fine for me. Is that really your data, if not try it, since it does work. Then described what you are doing in more detail perahps.

A misunderstaning I've seen on the forums and in person several times this week is the IsSorted and SortKey properties. If you set the sort properties by hand, and the data is not sorted then this will fail. Setting the properties does not induce a sort operation, it just describes how the data has already been sorted within the component. For SQL sources, make sure your ORDER BY clause matches the properties.

|||You guys are wonderful, I used the data viewer to look at the OLE source 1 and realized that even though I set the issorted=1, I did not have a order by clause in my SQL statement. Excaltly like what DarrenSQLIS said.I put in my order by clause and it works as expected. BTW, I am going to Vancouver next week attend a 4-day training on SQL server 2005, anyone else going.

Memory usage - double of the expected value?

Hi,
I am not so familiar with SQL server settings...
I use the latest MSDE and recognized huge memory usage of the SQL server
thread (seen in TaskManager). Then I used sp_configure to set the maximum
memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
while the TaskManager showed a SQL server memory usage of about 680 MB which
was quite stable for a long time.
Now I stopped the SQL server and the total memory usage was decreased by
almost 1400 MB...
That's strange because this would mean that the SQL server really used twice
of the memory that was set as maximum and that was dispalyed as real memory
usage in the TaskManager.
Can anybody explain this to me? How can I avoid this behaviour?
Does it mean that I have to set the max memory usage to 350 MB to get a real
memory usage of 700 MB?
Thanks a lot and sorry if this is a silly or already discussed question...
FrankHi
No. SQL Server only uses what is is configured to use (700MB).
This drop of 1400MB, was it Physical Memory or Commit Charge in task manager?
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Frank Esser" wrote:
> Hi,
> I am not so familiar with SQL server settings...
> I use the latest MSDE and recognized huge memory usage of the SQL server
> thread (seen in TaskManager). Then I used sp_configure to set the maximum
> memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
> while the TaskManager showed a SQL server memory usage of about 680 MB which
> was quite stable for a long time.
> Now I stopped the SQL server and the total memory usage was decreased by
> almost 1400 MB...
> That's strange because this would mean that the SQL server really used twice
> of the memory that was set as maximum and that was dispalyed as real memory
> usage in the TaskManager.
> Can anybody explain this to me? How can I avoid this behaviour?
> Does it mean that I have to set the max memory usage to 350 MB to get a real
> memory usage of 700 MB?
> Thanks a lot and sorry if this is a silly or already discussed question...
> Frank
>
>|||It was Physical Memory.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:A25641B5-DAE8-49C2-A1D7-67C48E29FD45@.microsoft.com...
> Hi
> No. SQL Server only uses what is is configured to use (700MB).
> This drop of 1400MB, was it Physical Memory or Commit Charge in task
> manager?
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Frank Esser" wrote:
>> Hi,
>> I am not so familiar with SQL server settings...
>> I use the latest MSDE and recognized huge memory usage of the SQL server
>> thread (seen in TaskManager). Then I used sp_configure to set the maximum
>> memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
>> while the TaskManager showed a SQL server memory usage of about 680 MB
>> which
>> was quite stable for a long time.
>> Now I stopped the SQL server and the total memory usage was decreased by
>> almost 1400 MB...
>> That's strange because this would mean that the SQL server really used
>> twice
>> of the memory that was set as maximum and that was dispalyed as real
>> memory
>> usage in the TaskManager.
>> Can anybody explain this to me? How can I avoid this behaviour?
>> Does it mean that I have to set the max memory usage to 350 MB to get a
>> real
>> memory usage of 700 MB?
>> Thanks a lot and sorry if this is a silly or already discussed
>> question...
>> Frank
>>|||The Max memory setting is only for the buffer pool and does not take into
account the MemtoLeave area which can add up to 386MB's to SQL Servers
usage. But that still would only be around a GB. I think there must have
been something else holding on to it and it was a coincidence.
--
Andrew J. Kelly SQL MVP
"Frank Esser" <Mistral@.nurfuerspam.de> wrote in message
news:eg%23XF0pZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> It was Physical Memory.
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
> news:A25641B5-DAE8-49C2-A1D7-67C48E29FD45@.microsoft.com...
>> Hi
>> No. SQL Server only uses what is is configured to use (700MB).
>> This drop of 1400MB, was it Physical Memory or Commit Charge in task
>> manager?
>> --
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>>
>> "Frank Esser" wrote:
>> Hi,
>> I am not so familiar with SQL server settings...
>> I use the latest MSDE and recognized huge memory usage of the SQL server
>> thread (seen in TaskManager). Then I used sp_configure to set the
>> maximum
>> memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
>> while the TaskManager showed a SQL server memory usage of about 680 MB
>> which
>> was quite stable for a long time.
>> Now I stopped the SQL server and the total memory usage was decreased by
>> almost 1400 MB...
>> That's strange because this would mean that the SQL server really used
>> twice
>> of the memory that was set as maximum and that was dispalyed as real
>> memory
>> usage in the TaskManager.
>> Can anybody explain this to me? How can I avoid this behaviour?
>> Does it mean that I have to set the max memory usage to 350 MB to get a
>> real
>> memory usage of 700 MB?
>> Thanks a lot and sorry if this is a silly or already discussed
>> question...
>> Frank
>>
>

Memory usage - double of the expected value?

Hi,
I am not so familiar with SQL server settings...
I use the latest MSDE and recognized huge memory usage of the SQL server
thread (seen in TaskManager). Then I used sp_configure to set the maximum
memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
while the TaskManager showed a SQL server memory usage of about 680 MB which
was quite stable for a long time.
Now I stopped the SQL server and the total memory usage was decreased by
almost 1400 MB...
That's strange because this would mean that the SQL server really used twice
of the memory that was set as maximum and that was dispalyed as real memory
usage in the TaskManager.
Can anybody explain this to me? How can I avoid this behaviour?
Does it mean that I have to set the max memory usage to 350 MB to get a real
memory usage of 700 MB?
Thanks a lot and sorry if this is a silly or already discussed question...
Frank
Hi
No. SQL Server only uses what is is configured to use (700MB).
This drop of 1400MB, was it Physical Memory or Commit Charge in task manager?
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Frank Esser" wrote:

> Hi,
> I am not so familiar with SQL server settings...
> I use the latest MSDE and recognized huge memory usage of the SQL server
> thread (seen in TaskManager). Then I used sp_configure to set the maximum
> memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
> while the TaskManager showed a SQL server memory usage of about 680 MB which
> was quite stable for a long time.
> Now I stopped the SQL server and the total memory usage was decreased by
> almost 1400 MB...
> That's strange because this would mean that the SQL server really used twice
> of the memory that was set as maximum and that was dispalyed as real memory
> usage in the TaskManager.
> Can anybody explain this to me? How can I avoid this behaviour?
> Does it mean that I have to set the max memory usage to 350 MB to get a real
> memory usage of 700 MB?
> Thanks a lot and sorry if this is a silly or already discussed question...
> Frank
>
>
|||It was Physical Memory.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:A25641B5-DAE8-49C2-A1D7-67C48E29FD45@.microsoft.com...[vbcol=seagreen]
> Hi
> No. SQL Server only uses what is is configured to use (700MB).
> This drop of 1400MB, was it Physical Memory or Commit Charge in task
> manager?
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Frank Esser" wrote:
|||The Max memory setting is only for the buffer pool and does not take into
account the MemtoLeave area which can add up to 386MB's to SQL Servers
usage. But that still would only be around a GB. I think there must have
been something else holding on to it and it was a coincidence.
Andrew J. Kelly SQL MVP
"Frank Esser" <Mistral@.nurfuerspam.de> wrote in message
news:eg%23XF0pZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> It was Physical Memory.
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
> news:A25641B5-DAE8-49C2-A1D7-67C48E29FD45@.microsoft.com...
>

Memory usage - double of the expected value?

Hi,
I am not so familiar with SQL server settings...
I use the latest MSDE and recognized huge memory usage of the SQL server
thread (seen in TaskManager). Then I used sp_configure to set the maximum
memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
while the TaskManager showed a SQL server memory usage of about 680 MB which
was quite stable for a long time.
Now I stopped the SQL server and the total memory usage was decreased by
almost 1400 MB...
That's strange because this would mean that the SQL server really used twice
of the memory that was set as maximum and that was dispalyed as real memory
usage in the TaskManager.
Can anybody explain this to me? How can I avoid this behaviour?
Does it mean that I have to set the max memory usage to 350 MB to get a real
memory usage of 700 MB?
Thanks a lot and sorry if this is a silly or already discussed question...
FrankHi
No. SQL Server only uses what is is configured to use (700MB).
This drop of 1400MB, was it Physical Memory or Commit Charge in task manager
?
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Frank Esser" wrote:

> Hi,
> I am not so familiar with SQL server settings...
> I use the latest MSDE and recognized huge memory usage of the SQL server
> thread (seen in TaskManager). Then I used sp_configure to set the maximum
> memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
> while the TaskManager showed a SQL server memory usage of about 680 MB whi
ch
> was quite stable for a long time.
> Now I stopped the SQL server and the total memory usage was decreased by
> almost 1400 MB...
> That's strange because this would mean that the SQL server really used twi
ce
> of the memory that was set as maximum and that was dispalyed as real memor
y
> usage in the TaskManager.
> Can anybody explain this to me? How can I avoid this behaviour?
> Does it mean that I have to set the max memory usage to 350 MB to get a re
al
> memory usage of 700 MB?
> Thanks a lot and sorry if this is a silly or already discussed question...
> Frank
>
>|||It was Physical Memory.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:A25641B5-DAE8-49C2-A1D7-67C48E29FD45@.microsoft.com...[vbcol=seagreen]
> Hi
> No. SQL Server only uses what is is configured to use (700MB).
> This drop of 1400MB, was it Physical Memory or Commit Charge in task
> manager?
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Frank Esser" wrote:
>|||The Max memory setting is only for the buffer pool and does not take into
account the MemtoLeave area which can add up to 386MB's to SQL Servers
usage. But that still would only be around a GB. I think there must have
been something else holding on to it and it was a coincidence.
Andrew J. Kelly SQL MVP
"Frank Esser" <Mistral@.nurfuerspam.de> wrote in message
news:eg%23XF0pZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> It was Physical Memory.
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
> news:A25641B5-DAE8-49C2-A1D7-67C48E29FD45@.microsoft.com...
>