Merge Join transformation hangs

I have serached through this forum, and I could not find the solution.

My SSIS data flow is a simple one. Extracting two tables from Oracle using OLE DB Source , join them together using merge join and loading into a table in SQL server by SQL Server Destination.

I haven't gone through this simple procedure because Merge join always hangs there. Down to further investigation, I found one input (randomly one of two inputs) is always stuck. Sometimes the input is empty, sometimes is about half way.

Is there workround to see what is happening there and to fix this problem?

TIA

As far as I can see, Mergejoin is not the culprit, it is by design for MergeJoin to wait until it gets buffers from both inputs before it outputs. Looks to me your input(s) did not get through at your Oracle server. I suggest you to use an external tool (rowsetviewer?) to perform the same queries you put on the two OLEDBSrc inputs, to see whether it works there.

thanks

wenyang

|||

Also make sure the 2 inputs of the Merge Join are actually sorted.

Rafael Salas

|||

Hi, Wenyang & Rafael,

Thanks very much for your input.

The 2 inputs of the Merge Join are sorted by "order by" and the Issorted = true. I found the killer is the "data viewer" of the path, which shows the input data. Once I removed the "data viewer", the problem's gone. I cannot explain why.

BTW, what's the external tool (rowsetviewer)? Where can I get it?

Merge and Transactional from the same server

I need to come up with a solution to my replication issue. The solution that I come up with involves using one publisher to push transactional and merge replication. The database that is published is the same.

Would this cause any issues? Can this be done? I would think that if a table is needed for both, I would have to create a separate publication for each type right?

Is there any documentation anywhere to help prove if this is possible?

Hi, William,

It can be done if you use readonly transactional replication and merge replication on the same db (same tables). You can't enable updatable transactional replication & merge replication at the same time on the same table same db (it's not allowed) since they both will try to add a guid column to the publishing table. Yes you need to create separate tran (readonly) publication and merge publication on the same set of tables. And you will have problems if tran and merge subscription are both on the same db (I guess you will not do this, right?). The other restriction is that for transactional replication, you need to have an primary key on the publisher table, I guess you know it already.

Not sure if we have any documentation specifically for this particular configuration but I just tried to play with this configuration and it worked just fine for me.

Thanks,

Zhiqiang Feng

|||

So readonly for both transactional and merge? the tables I want to add readonly transactional to already have updatable merge (if I understand this correctly it is two way replication right)... The transactional will go downstream only to a server and the merge can go downstream (not to the same server as the transactional) and upstream....

It doesn't sound like this is possible does it? If not I have a backup plan..

|||

William,

To avoid confusion, I should say "It can be done if you use merge replication and readonly transactional replication on the same db (same tables)". For merge, there is no concept of readonly, it only has one type which you can always download & upload changes between publisher and subscriber.

Thanks,

Zhiqiang Feng

|||

Ok... So as long as the transactional only goes to the subscriber and not from the subscriber back to the publisher it should work with no issues? Is that what you are saying?

Sorry if I sound ignorant but I usually have only had one or the other and now I need both.

|||

Yes, your understanding is correct.

Thanks,

Zhiqiang Feng

sql

Merge and Transactional from the same server

I need to come up with a solution to my replication issue. The solution that I come up with involves using one publisher to push transactional and merge replication. The database that is published is the same.

Would this cause any issues? Can this be done? I would think that if a table is needed for both, I would have to create a separate publication for each type right?

Is there any documentation anywhere to help prove if this is possible?

Hi, William,

It can be done if you use readonly transactional replication and merge replication on the same db (same tables). You can't enable updatable transactional replication & merge replication at the same time on the same table same db (it's not allowed) since they both will try to add a guid column to the publishing table. Yes you need to create separate tran (readonly) publication and merge publication on the same set of tables. And you will have problems if tran and merge subscription are both on the same db (I guess you will not do this, right?). The other restriction is that for transactional replication, you need to have an primary key on the publisher table, I guess you know it already.

Not sure if we have any documentation specifically for this particular configuration but I just tried to play with this configuration and it worked just fine for me.

Thanks,

Zhiqiang Feng

|||

So readonly for both transactional and merge? the tables I want to add readonly transactional to already have updatable merge (if I understand this correctly it is two way replication right)... The transactional will go downstream only to a server and the merge can go downstream (not to the same server as the transactional) and upstream....

It doesn't sound like this is possible does it? If not I have a backup plan..

|||

William,

To avoid confusion, I should say "It can be done if you use merge replication and readonly transactional replication on the same db (same tables)". For merge, there is no concept of readonly, it only has one type which you can always download & upload changes between publisher and subscriber.

Thanks,

Zhiqiang Feng

|||

Ok... So as long as the transactional only goes to the subscriber and not from the subscriber back to the publisher it should work with no issues? Is that what you are saying?

Sorry if I sound ignorant but I usually have only had one or the other and now I need both.

|||

Yes, your understanding is correct.

Thanks,

Zhiqiang Feng

merge agent

sql server enterprise manager could not start merge agent
error:22022 can any one tell the solution for this would be appreciated.
Message posted via http://www.droptable.com
I think we need more info...what is the whole error message?
However, here are some tips on getting more details.
-Check the agent history (expand steps) for more detail about errors.
-Put an output file on the merge agent (use -Output c:\merge.txt
-OutputVerboseLevel 3)
-Try starting the agent from the command line (with an output file! :-))
-Check SQL Errorlog, SQL Agent Errorlog, and Application and System event
logs for more errors.
Tell us what you find so we can be more help. Or let me know if you need
exact steps on setting up an output file.
Donna
"pardhi a via droptable.com" wrote:

> sql server enterprise manager could not start merge agent
> error:22022 can any one tell the solution for this would be appreciated.
> --
> Message posted via http://www.droptable.com
>
|||Hi Donna
Thanks it is working.Thanks for immediate response.
as you said i copied into the file then saw an error showing (cannot
generate SSPI context) then i stop and restart the agent from then onwards
starts working fine.you have any idea why (cannnot generate sspi context)
cause of the error.
Message posted via http://www.droptable.com

MemToLeave and ODBC

We are running SQL Server 2005, SP2, Standard Edition, 64bit.
We are considering an ODBC solution for client reporting, and have been
reading that the ODBC driver uses MemToLeave memory, up to 7MB per connection.
I was wondering if the queries sent via this ODBC connection, also utilize
MemToLeave, or if those queries use the Buffer Pool?
And then a separate question, not involving ODBC...does an adhoc query
executed from Management Studio utilize Buffer Pool memory, if it needs less
than 8K contiguous memory, or do all adhoc queries in general go directly
against MemToLeave?
As a side note, I have read some from Ken Hendersons blog, threads in this
discussion group, and other posts on the web. I cannot seem to find the
answer to the above questions. Thx.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1Still seeking for answers. Please help.
cbrichards wrote:
>We are running SQL Server 2005, SP2, Standard Edition, 64bit.
>We are considering an ODBC solution for client reporting, and have been
>reading that the ODBC driver uses MemToLeave memory, up to 7MB per connection.
>I was wondering if the queries sent via this ODBC connection, also utilize
>MemToLeave, or if those queries use the Buffer Pool?
>And then a separate question, not involving ODBC...does an adhoc query
>executed from Management Studio utilize Buffer Pool memory, if it needs less
>than 8K contiguous memory, or do all adhoc queries in general go directly
>against MemToLeave?
>As a side note, I have read some from Ken Hendersons blog, threads in this
>discussion group, and other posts on the web. I cannot seem to find the
>answer to the above questions. Thx.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||On a 64bit SQL instance, you should have little to worry about MemToLeave
because you have a huge virtual address space to go about. I might be wrong,
but my understanding is that whether MemToLeave is used depends on factors
such as memory allocation size, not on what database API is being used on the
client side.
Linchi
"cbrichards via SQLMonster.com" wrote:
> We are running SQL Server 2005, SP2, Standard Edition, 64bit.
> We are considering an ODBC solution for client reporting, and have been
> reading that the ODBC driver uses MemToLeave memory, up to 7MB per connection.
> I was wondering if the queries sent via this ODBC connection, also utilize
> MemToLeave, or if those queries use the Buffer Pool?
> And then a separate question, not involving ODBC...does an adhoc query
> executed from Management Studio utilize Buffer Pool memory, if it needs less
> than 8K contiguous memory, or do all adhoc queries in general go directly
> against MemToLeave?
> As a side note, I have read some from Ken Hendersons blog, threads in this
> discussion group, and other posts on the web. I cannot seem to find the
> answer to the above questions. Thx.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||So if I am running SQL Server 2005, SP2, Standard Edition, 64bit, with 16GB
RAM (14GB set as Max Server Memory), are you saying that just because I am
running 64bit, that I have a huge virtual address space? Where does this huge
virtual address space come from if 14Gb is dedicated to SQL Server? The disks?
Linchi Shea wrote:
>On a 64bit SQL instance, you should have little to worry about MemToLeave
>because you have a huge virtual address space to go about. I might be wrong,
>but my understanding is that whether MemToLeave is used depends on factors
>such as memory allocation size, not on what database API is being used on the
>client side.
>Linchi
>> We are running SQL Server 2005, SP2, Standard Edition, 64bit.
>[quoted text clipped - 11 lines]
>> discussion group, and other posts on the web. I cannot seem to find the
>> answer to the above questions. Thx.
--
Message posted via http://www.sqlmonster.com|||Yes, SQL Server isn't even aware of what API is used by the client application.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:76F3D546-8C00-4E8A-81A6-CDA22E1E0193@.microsoft.com...
> On a 64bit SQL instance, you should have little to worry about MemToLeave
> because you have a huge virtual address space to go about. I might be wrong,
> but my understanding is that whether MemToLeave is used depends on factors
> such as memory allocation size, not on what database API is being used on the
> client side.
> Linchi
> "cbrichards via SQLMonster.com" wrote:
>> We are running SQL Server 2005, SP2, Standard Edition, 64bit.
>> We are considering an ODBC solution for client reporting, and have been
>> reading that the ODBC driver uses MemToLeave memory, up to 7MB per connection.
>> I was wondering if the queries sent via this ODBC connection, also utilize
>> MemToLeave, or if those queries use the Buffer Pool?
>> And then a separate question, not involving ODBC...does an adhoc query
>> executed from Management Studio utilize Buffer Pool memory, if it needs less
>> than 8K contiguous memory, or do all adhoc queries in general go directly
>> against MemToLeave?
>> As a side note, I have read some from Ken Hendersons blog, threads in this
>> discussion group, and other posts on the web. I cannot seem to find the
>> answer to the above questions. Thx.
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>>|||MemToLeave is a virtual memory address space concept. It has nothing to do
with how mch physical memory you have.
Let's forget about performance and physical memory for now. When you write a
program in a high-level programming language, you may feel like you can just
keep allocating data structures. But eventually you'll run into out of memory
error because the compiler/OS knows that there is a limit to the number of
unique virtual memory addresses for a process. If those addresses are all
used up in the process, you are not allowed to allocate any more and it's the
OS' job to keep track of the usage of the virtual memeory addresses.
On a 32-bit system, the number of virtual memory addresses is limited.
Basically there are 2GB for a program to allocate its data structures (the
other 2GB are taken by the kernel). Since the SQL process' virtual address
space may get fragmented after it has been allocating data structures for a
while and fragmentation can get so bad so that it may not be able to find
contiguious virtual addrsses for larger data structures (i.e. larger than
8K), the strategy that SQL Server uses is to pre-allocate a chunk of virutal
addresses when it starts so that this chunk is available regardless of it
snormal allocation activities. This pre-allocated chunk of virtual memory
addresses is MemToLeave.
Now with a 64-bit system, the virutal address space is so large that there
is little to no danger that a SQL instance (primarily its buffer pool) will
ever use up so much of virtual addresses that its process will have no
contiguious virtual addresses left for larger data structures.
Again, this is all related virtual memory addresses, and has nothing to do
with how much physical memory you may have on a particular system. In
reality, of course if you don't have enough physical memory, you risk
swapping things to paging files and your performance will suffer.
Linchi
"cbrichards via SQLMonster.com" wrote:
> So if I am running SQL Server 2005, SP2, Standard Edition, 64bit, with 16GB
> RAM (14GB set as Max Server Memory), are you saying that just because I am
> running 64bit, that I have a huge virtual address space? Where does this huge
> virtual address space come from if 14Gb is dedicated to SQL Server? The disks?
>
> Linchi Shea wrote:
> >On a 64bit SQL instance, you should have little to worry about MemToLeave
> >because you have a huge virtual address space to go about. I might be wrong,
> >but my understanding is that whether MemToLeave is used depends on factors
> >such as memory allocation size, not on what database API is being used on the
> >client side.
> >
> >Linchi
> >
> >> We are running SQL Server 2005, SP2, Standard Edition, 64bit.
> >>
> >[quoted text clipped - 11 lines]
> >> discussion group, and other posts on the web. I cannot seem to find the
> >> answer to the above questions. Thx.
> --
> Message posted via http://www.sqlmonster.com
>|||I am really perplexed at this whole virtual address space with 64bit and I do
not seem to be getting a straight answer. It is probably the way I am
phrasing my questions. If so I apologize. If I have 16GB RAM, of which 14GB
is configured as MaxServerMemory, and I have 100GB carved out on the SAN for
my disks, then if the 14GB is being used by SQL Server, and then another
query comes along, and the 14GB is consumed, is the virtual address space the
2GB left for the operating system, plus the 100GB carved out on the SAN?
Tibor Karaszi wrote:
>Yes, SQL Server isn't even aware of what API is used by the client application.
>> On a 64bit SQL instance, you should have little to worry about MemToLeave
>> because you have a huge virtual address space to go about. I might be wrong,
>[quoted text clipped - 19 lines]
>> discussion group, and other posts on the web. I cannot seem to find the
>> answer to the above questions. Thx.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||First of all, virtual address space of a process has nothing to do with how
much physial memory or disk storage you may have on a system. On 32-bit
Windows, the user-mode virtual address space is 2GB (or 3GB with /3GB switch
in the boot.ini). On 64-bit Windows, the virtual address space of a process
is 8TB. These numbers don't change regardless how much RAM or disk storage
you have.
Secondly, the size of the virtual address space is a function of the width
of the address or the number of unique pointer values. So in theory, on
32-bit system, the virtual address space is 2^32, and if each address points
to a byte, that's 4GB. Also in theory, on a 64-bit system, the virtual
address space is 2^64, and if each points to a byte, that's more than 16
exabytes. But actual implementations often impose additional restriction. For
instance, 32-bit Windows gives 2GB to the kernel and leave 2GB to your app to
use. And since 16 exabytes are excessive, currently beyond reach anyway, and
costly to support, only 43 bits are actually used for user-mode virtual
addresses on current x64, and that's ~8TB.
Now, if you really end using this much virtual address space, they (i.e.
virtual memory allocated to represent your data structures) have to be
backed/supported with real stores, which can be physical memory or paging
files.
Linchi
"cbrichards via SQLMonster.com" wrote:
> I am really perplexed at this whole virtual address space with 64bit and I do
> not seem to be getting a straight answer. It is probably the way I am
> phrasing my questions. If so I apologize. If I have 16GB RAM, of which 14GB
> is configured as MaxServerMemory, and I have 100GB carved out on the SAN for
> my disks, then if the 14GB is being used by SQL Server, and then another
> query comes along, and the 14GB is consumed, is the virtual address space the
> 2GB left for the operating system, plus the 100GB carved out on the SAN?
> Tibor Karaszi wrote:
> >Yes, SQL Server isn't even aware of what API is used by the client application.
> >
> >> On a 64bit SQL instance, you should have little to worry about MemToLeave
> >> because you have a huge virtual address space to go about. I might be wrong,
> >[quoted text clipped - 19 lines]
> >> discussion group, and other posts on the web. I cannot seem to find the
> >> answer to the above questions. Thx.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||On Thu, 04 Oct 2007 01:49:25 GMT, "cbrichards via SQLMonster.com"
<u3288@.uwe> wrote:
>If I have 16GB RAM, of which 14GB
>is configured as MaxServerMemory, and I have 100GB carved out on the SAN for
>my disks, then if the 14GB is being used by SQL Server, and then another
>query comes along, and the 14GB is consumed, is the virtual address space the
>2GB left for the operating system, plus the 100GB carved out on the SAN?
There are, or so I heard at some time way in the past, computers with
the architecture where memory and disk are mapped with a common
address space, as if each was an extension of the other. That sounds
like what you are asking about. Microsoft SQL Server does not run on
any such architecture.
To oversimplify a bit... Most of SQL Server's memory is used for
caching database pages. If SQL Server needs a page that is already in
cache it is just referenced from that memory location. If it needs a
page that is not in cache it is read into cache. When there is no
room in memory for a new page a page that hasn't been used recently is
overwritten. When a page is updated it will be written to disk -
eventually. (Logs are handled a bit differently and always written
immediately.)
Sorry if I have misunderstood your statement.
Roy Harvey
Beacon Falls, CT

Memory usage of SQL Server 2005 Mobile Edition

Hi forum readers,

we are working on a release 2.0 mobile solution right now. In our version 1.0 we did not have to worry about memory issues as our application was the only application running on our target devices (e.g. T-Mobile MDA Compact II Pocket PCs, WM2005).
Now we need to share the available memory with others. As our application relies on its SQL Server 2005 Mobile Edition database we are wondering about memory usage of that server.

We know that a Pocket PC divides its memory into Storage and Program. If our application uses a 5 MB database and 1.5 MB for DLLs and it's exe-file. These files reside in the storage space when not loaded. When the application starts up it is loaded in the program memory. What happens to the 5 MB database file? Is is loaded into Program memory as well? Are only portions of that file loaded? Or is nothing loaded at all?

Does anyone have a deeper insight into that server an can answer my questions.

Best regards,
Tobias

No, we don't load entire 5MB database into program memory. Only required pages are loaded to the buffer pool as and when required.

When database is loaded, lot of runtime structures are maintained for multi proc/thread synchronization. Some of these are maintained in shared memory. This total shared memory size figure is dependent mainly on the configurable parameters buffer pool size, max database size etc. If you increase them the figure goes up.

Assuming buffer pool size is the default (160 pages ~ 160*4096), we can roughly estimate the total size to be 800 KB + (max database size/1024) KB.

For ex: for a 60MB max size database, it would roughly 860 KB.

for a 2GB max size database, it would be roughly 2800 KB

Apart from this there would be a lot of other objects that are created & maintained as the client requests. For ex rowsets/cursors. It will be too difficult to estimate these as these are created based on the client code/usage.

Thanks - Raja

|||

Hi Raja,

Thank you very much, this gives us a better understanding of the required memory and the loading process.

Best Regards,

Tobias