Linked Servers and MemToLeave (VAS)

VDI based backup products such as SQL Backup and others use in process SQL memory to execute, Linked Servers can run in process as well, but the VDI products are typically associated with MemToLeave messages. Have a look at HyperBac as an alternative, it is a file system filter for SQL compression, offers compression for a wider range of SQL processes above and beyond backup.



Chris Wood wrote:

Linked Servers and MemToLeave (VAS)
26-Jan-10

Hi,

I know that the SQL Native Client linked server provider is an in_process
provider and will use MemToLeave space. Is it the linked servers going out
from the server or the linked servers coming in or both that affects
MemToLeave? We still run 32_bit SQL2005 and we getting more requests for
linked servers using the SQL Native Client provider. I just want to know
when I have to worry about MemToLeave as we also have CLR and 3rd party
products (Red Gate SQLBackup) that run in the same space.

Thanks

Chris

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Silverlight 2 Beta 2 - Doing Data Part V: Realtime StockQuotes / Scrolling Display
http://www.eggheadcafe.com/tutorials...eta-2--do.aspx

To late Jeffrey I had ordered Red Gate before I saw Hyperbac but that
doesn't answer my question.

I want to know is it the linked servers going out or the linked servers
coming in that mess with VAS>

Thanks

Chris

wrote in message news:201012617426javenatavensolutionsdotcom...
> VDI based backup products such as SQL Backup and others use in process SQL
> memory to execute, Linked Servers can run in process as well, but the VDI
> products are typically associated with MemToLeave messages. Have a look
> at HyperBac as an alternative, it is a file system filter for SQL
> compression, offers compression for a wider range of SQL processes above
> and beyond backup.
>
>
>
> Chris Wood wrote:
>
> Linked Servers and MemToLeave (VAS)
> 26-Jan-10
>
> Hi,
>
> I know that the SQL Native Client linked server provider is an in_process
> provider and will use MemToLeave space. Is it the linked servers going out
> from the server or the linked servers coming in or both that affects
> MemToLeave? We still run 32_bit SQL2005 and we getting more requests for
> linked servers using the SQL Native Client provider. I just want to know
> when I have to worry about MemToLeave as we also have CLR and 3rd party
> products (Red Gate SQLBackup) that run in the same space.
>
> Thanks
>
> Chris
>
> Previous Posts In This Thread:
>
>
> Submitted via EggHeadCafe - Software Developer Portal of Choice
> Silverlight 2 Beta 2 - Doing Data Part V: Realtime StockQuotes / Scrolling
> Display
> http://www.eggheadcafe.com/tutorials...eta-2--do.aspx

Chris Wood (anonymousatmicrosoftdotcom) writes:
> I want to know is it the linked servers going out or the linked servers
> coming in that mess with VAS>

Linked servers going out. When you access a linked server, SQL Server
becomes the client, and to play a client, it needs to load a client API.

Incoming linked servers is just like any other client connection, and
should not consume anymore VAS than any other connection.

You are aware of that you can control the amount of MemToLeave with the -g
option I guess?

--
Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thanks Erland. I have had to adjust a number of times pushing the -g ever
upward. Of course our major VAS problem happens on the Production server not
the Development or Acceptance ones so I don't want to find VAS starvation
once we add more outgoing linked servers. As they are attempting access to
different databases on servers that may already have linked servers defined
to them I am assuming that as each one is introduced they will all take a
little bit more memory from VAS. I had been told by Bob Ward that we could
make the SQL Native Client provider out of process to reduce the VAS pressue
but I believe there are caviots.

Chris

"Erland Sommarskog" wrote in message
news:Xns9D0D223F734CYazormanat127dot0.0.1...
> Chris Wood (anonymousatmicrosoftdotcom) writes:
>> I want to know is it the linked servers going out or the linked servers
>> coming in that mess with VAS>
>
> Linked servers going out. When you access a linked server, SQL Server
> becomes the client, and to play a client, it needs to load a client API.
>
> Incoming linked servers is just like any other client connection, and
> should not consume anymore VAS than any other connection.
>
> You are aware of that you can control the amount of MemToLeave with the -g
> option I guess?
>
> --
> Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinf...ons/books.mspx
>