Merge Join - Add Cross Join

Please implement a Cross Join option in SP1. Thanks.Andrew,
I asked for the same thing a long time ago. Submit it at betaplace or the product feedback centre. The more people that ask for it the more likely we are to get it.

-Jamiesql

Memtoleave and -g-switch

hi out there
On our Windows 2003 servers w. sp1 and running MS SQL Server 2000 w. sp4 we
see from time to time that we get this error "cannot allocate 64k
continous memory" or "SQL Server could not spawn process_loginread thread"
which could be caused by nothing left in the "Memtoleave" pool - I have now
search for advice on how to determine the values for the -g-switch - but
without much success - and if I just go for the "try&error" concept my
sql-server just allocates less and less ? - ehh - in which units are the
parameters for the -g option specified - bytes, kilobytes, mbytes - 4k block
? Any suggestions for measuring the actual running value of this pool -
memtoleave ?

best regards /ti

The units for -g are MB. The default is 256.

Is this a 32 or 64 bit system?

How much memory is on the system?

Are you running SQL Server with AWE enabled?

|||

Could you run the following query (when you are having this problem) and gives us the results.

Code Snippet

SELECTtype, multi_pages_kb FROMsys.dm_os_memory_clerksWHERE multi_pages_kb > 0

ORDERBY multi_pages_kb DESC

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

SQL 2000 does not have DMVs. There's no easy way to profile memory usage in SQL 2000. See http://msdn2.microsoft.com/en-US/library/aa175282(sql.80).aspx for more information

Thanks, Ron D.

|||Oops, I missed the 2000 part :-)

Memtoleave and -g-switch

hi out there
On our Windows 2003 servers w. sp1 and running MS SQL Server 2000 w. sp4 we
see from time to time that we get this error "cannot allocate 64k
continous memory" or "SQL Server could not spawn process_loginread thread"
which could be caused by nothing left in the "Memtoleave" pool - I have now
search for advice on how to determine the values for the -g-switch - but
without much success - and if I just go for the "try&error" concept my
sql-server just allocates less and less ? - ehh - in which units are the
parameters for the -g option specified - bytes, kilobytes, mbytes - 4k block
? Any suggestions for measuring the actual running value of this pool -
memtoleave ?

best regards /ti

The units for -g are MB. The default is 256.

Is this a 32 or 64 bit system?

How much memory is on the system?

Are you running SQL Server with AWE enabled?

|||

Could you run the following query (when you are having this problem) and gives us the results.

Code Snippet

SELECT type, multi_pages_kb FROM sys.dm_os_memory_clerks WHERE multi_pages_kb > 0

ORDER BY multi_pages_kb DESC

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

SQL 2000 does not have DMVs. There's no easy way to profile memory usage in SQL 2000. See http://msdn2.microsoft.com/en-US/library/aa175282(sql.80).aspx for more information

Thanks, Ron D.

|||Oops, I missed the 2000 part :-)

Memory Usage - PAE / AWE question

I'm working on a server (Windows Server 2003 R2 SP1 - SQL Server
Enterprise Edition SP4) which has 16Gb memory.
I'm not 100% sure the server has been correctly configured to use the
memory up to 12Gb as defined in the SQL Server properties.
Based on the KB article at http://support.microsoft.com/kb/274750 I
have two questions:
Firstly, I cannot find the file boot.ini to include the /PAE switch.
Where is this file supposed to be located? Do I need to create a new
one?
Secondly, if I output the sp_configure 'awe enabled', it is set to 0.
sample output below from sp_confiure:
name minimum maximum
config_value run_value
-- -- --
-- --
show advanced options 0 1
1 1
awe enabled 0 1
0 0
max server memory (MB) 4 2147483647
12032 12032BOOT.INI is a hidden system file in C:\. Add the /PAE switch and reboot.
You need to run:
sp_configure 'aew', 1
reconfigure
... and restart SQL Server.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
<bmwbase-newsgroup@.yahoo.com> wrote in message
news:1181256247.731355.184430@.p47g2000hsd.googlegroups.com...
I'm working on a server (Windows Server 2003 R2 SP1 - SQL Server
Enterprise Edition SP4) which has 16Gb memory.
I'm not 100% sure the server has been correctly configured to use the
memory up to 12Gb as defined in the SQL Server properties.
Based on the KB article at http://support.microsoft.com/kb/274750 I
have two questions:
Firstly, I cannot find the file boot.ini to include the /PAE switch.
Where is this file supposed to be located? Do I need to create a new
one?
Secondly, if I output the sp_configure 'awe enabled', it is set to 0.
sample output below from sp_confiure:
name minimum maximum
config_value run_value
-- -- --
-- --
show advanced options 0 1
1 1
awe enabled 0 1
0 0
max server memory (MB) 4 2147483647
12032 12032|||Go to the Windows Explorer menu, Tools | Folder Options ..., and the View
tab, then uncheck Hide Protected operating system files. You'll then see
boot.ini in the system root directory (typically C:\).
Linchi
"bmwbase-newsgroup@.yahoo.com" wrote:
> I'm working on a server (Windows Server 2003 R2 SP1 - SQL Server
> Enterprise Edition SP4) which has 16Gb memory.
> I'm not 100% sure the server has been correctly configured to use the
> memory up to 12Gb as defined in the SQL Server properties.
> Based on the KB article at http://support.microsoft.com/kb/274750 I
> have two questions:
> Firstly, I cannot find the file boot.ini to include the /PAE switch.
> Where is this file supposed to be located? Do I need to create a new
> one?
> Secondly, if I output the sp_configure 'awe enabled', it is set to 0.
> sample output below from sp_confiure:
> name minimum maximum
> config_value run_value
> -- -- --
> -- --
> show advanced options 0 1
> 1 1
> awe enabled 0 1
> 0 0
> max server memory (MB) 4 2147483647
> 12032 12032
>

Memory Usage - PAE / AWE question

I'm working on a server (Windows Server 2003 R2 SP1 - SQL Server
Enterprise Edition SP4) which has 16Gb memory.
I'm not 100% sure the server has been correctly configured to use the
memory up to 12Gb as defined in the SQL Server properties.
Based on the KB article at http://support.microsoft.com/kb/274750 I
have two questions:
Firstly, I cannot find the file boot.ini to include the /PAE switch.
Where is this file supposed to be located? Do I need to create a new
one?
Secondly, if I output the sp_configure 'awe enabled', it is set to 0.
sample output below from sp_confiure:
name minimum maximum
config_value run_value
-- -- --
-- --
show advanced options 0 1
1 1
awe enabled 0 1
0 0
max server memory (MB) 4 2147483647
12032 12032
BOOT.INI is a hidden system file in C:\. Add the /PAE switch and reboot.
You need to run:
sp_configure 'aew', 1
reconfigure
... and restart SQL Server.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
<bmwbase-newsgroup@.yahoo.com> wrote in message
news:1181256247.731355.184430@.p47g2000hsd.googlegr oups.com...
I'm working on a server (Windows Server 2003 R2 SP1 - SQL Server
Enterprise Edition SP4) which has 16Gb memory.
I'm not 100% sure the server has been correctly configured to use the
memory up to 12Gb as defined in the SQL Server properties.
Based on the KB article at http://support.microsoft.com/kb/274750 I
have two questions:
Firstly, I cannot find the file boot.ini to include the /PAE switch.
Where is this file supposed to be located? Do I need to create a new
one?
Secondly, if I output the sp_configure 'awe enabled', it is set to 0.
sample output below from sp_confiure:
name minimum maximum
config_value run_value
-- -- --
-- --
show advanced options 0 1
1 1
awe enabled 0 1
0 0
max server memory (MB) 4 2147483647
12032 12032
|||Go to the Windows Explorer menu, Tools | Folder Options ..., and the View
tab, then uncheck Hide Protected operating system files. You'll then see
boot.ini in the system root directory (typically C:\).
Linchi
"bmwbase-newsgroup@.yahoo.com" wrote:

> I'm working on a server (Windows Server 2003 R2 SP1 - SQL Server
> Enterprise Edition SP4) which has 16Gb memory.
> I'm not 100% sure the server has been correctly configured to use the
> memory up to 12Gb as defined in the SQL Server properties.
> Based on the KB article at http://support.microsoft.com/kb/274750 I
> have two questions:
> Firstly, I cannot find the file boot.ini to include the /PAE switch.
> Where is this file supposed to be located? Do I need to create a new
> one?
> Secondly, if I output the sp_configure 'awe enabled', it is set to 0.
> sample output below from sp_confiure:
> name minimum maximum
> config_value run_value
> -- -- --
> -- --
> show advanced options 0 1
> 1 1
> awe enabled 0 1
> 0 0
> max server memory (MB) 4 2147483647
> 12032 12032
>

Memory Usage - PAE / AWE question

I'm working on a server (Windows Server 2003 R2 SP1 - SQL Server
Enterprise Edition SP4) which has 16Gb memory.
I'm not 100% sure the server has been correctly configured to use the
memory up to 12Gb as defined in the SQL Server properties.
Based on the KB article at http://support.microsoft.com/kb/274750 I
have two questions:
Firstly, I cannot find the file boot.ini to include the /PAE switch.
Where is this file supposed to be located? Do I need to create a new
one?
Secondly, if I output the sp_configure 'awe enabled', it is set to 0.
sample output below from sp_confiure:
name minimum maximum
config_value run_value
-- -- --
-- --
show advanced options 0 1
1 1
awe enabled 0 1
0 0
max server memory (MB) 4 2147483647
12032 12032BOOT.INI is a hidden system file in C:\. Add the /PAE switch and reboot.
You need to run:
sp_configure 'aew', 1
reconfigure
... and restart SQL Server.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
<bmwbase-newsgroup@.yahoo.com> wrote in message
news:1181256247.731355.184430@.p47g2000hsd.googlegroups.com...
I'm working on a server (Windows Server 2003 R2 SP1 - SQL Server
Enterprise Edition SP4) which has 16Gb memory.
I'm not 100% sure the server has been correctly configured to use the
memory up to 12Gb as defined in the SQL Server properties.
Based on the KB article at http://support.microsoft.com/kb/274750 I
have two questions:
Firstly, I cannot find the file boot.ini to include the /PAE switch.
Where is this file supposed to be located? Do I need to create a new
one?
Secondly, if I output the sp_configure 'awe enabled', it is set to 0.
sample output below from sp_confiure:
name minimum maximum
config_value run_value
-- -- --
-- --
show advanced options 0 1
1 1
awe enabled 0 1
0 0
max server memory (MB) 4 2147483647
12032 12032|||Go to the Windows Explorer menu, Tools | Folder Options ..., and the View
tab, then uncheck Hide Protected operating system files. You'll then see
boot.ini in the system root directory (typically C:\).
Linchi
"bmwbase-newsgroup@.yahoo.com" wrote:

> I'm working on a server (Windows Server 2003 R2 SP1 - SQL Server
> Enterprise Edition SP4) which has 16Gb memory.
> I'm not 100% sure the server has been correctly configured to use the
> memory up to 12Gb as defined in the SQL Server properties.
> Based on the KB article at http://support.microsoft.com/kb/274750 I
> have two questions:
> Firstly, I cannot find the file boot.ini to include the /PAE switch.
> Where is this file supposed to be located? Do I need to create a new
> one?
> Secondly, if I output the sp_configure 'awe enabled', it is set to 0.
> sample output below from sp_confiure:
> name minimum maximum
> config_value run_value
> -- -- --
> -- --
> show advanced options 0 1
> 1 1
> awe enabled 0 1
> 0 0
> max server memory (MB) 4 2147483647
> 12032 12032
>

Memory Questions

I am running SSAS 2005 SP1 on a machine with Windows 2003 Server R2 and 2 GB of memory.I have approximately 30 analysis databases defined on this machine.

When I process one of my analysis databases, MSMDSRV.EXE gradually takes over 1 GB of virtual memory and does not release it once processing is completed.Once processing completes, MSMDSRV.EXE continues to take up 5-15% of CPU to process something.This goes on for hours.

Let’s say I go into BIDS on this machine and try to open an analysis database by entering the server name then attempting to select a database from the database drop down.It takes about 10 minutes for the list of databases to appear on the screen, and you can watch MSMDSRV.EXE take up more and more virtual memory until it takes over 1 GB of virtual memory.

- Why is SSAS taking up so much virtual memory to perform these operations? Is it metadata overload?

- Is there any way to get MSMDSRV.EXE to release the virtual memory it has taken?

- Once database processing has been completed, what is MSMDSRV.EXE doing that would take 5-15% of CPU?

- Is there a recommended solution to improve memory handling and performance in this situation?

Thanks in advance for your help.

Wendell G.

Is this a recent event or a continual event?

Is the server recognizing the 2gig's of RAM and the full capacity of the processor(s)?

How old is the server?

Adamus

|||

This has been a continual event and has appeared to get worse as more analysis services databases are added to the server.

The server is recognizing the 2 GB ram and is less than one year old.

Wendell G.

|||

Not knowing your situation, the immiteate suggestion is to increase memory of your machine.

2 Gb looks litte on the lower side.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Memory Questions

I am running SSAS 2005 SP1 on a machine with Windows 2003 Server R2 and 2 GB of memory.I have approximately 30 analysis databases defined on this machine.

When I process one of my analysis databases, MSMDSRV.EXE gradually takes over 1 GB of virtual memory and does not release it once processing is completed.Once processing completes, MSMDSRV.EXE continues to take up 5-15% of CPU to process something.This goes on for hours.

Let’s say I go into BIDS on this machine and try to open an analysis database by entering the server name then attempting to select a database from the database drop down.It takes about 10 minutes for the list of databases to appear on the screen, and you can watch MSMDSRV.EXE take up more and more virtual memory until it takes over 1 GB of virtual memory.

- Why is SSAS taking up so much virtual memory to perform these operations? Is it metadata overload?

- Is there any way to get MSMDSRV.EXE to release the virtual memory it has taken?

- Once database processing has been completed, what is MSMDSRV.EXE doing that would take 5-15% of CPU?

- Is there a recommended solution to improve memory handling and performance in this situation?

Thanks in advance for your help.

Wendell G.

Is this a recent event or a continual event?

Is the server recognizing the 2gig's of RAM and the full capacity of the processor(s)?

How old is the server?

Adamus

|||

This has been a continual event and has appeared to get worse as more analysis services databases are added to the server.

The server is recognizing the 2 GB ram and is less than one year old.

Wendell G.

|||

Not knowing your situation, the immiteate suggestion is to increase memory of your machine.

2 Gb looks litte on the lower side.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I have exactly the same situation but I have 12G RAM
each process of the cube increases the memory of the msmdsrv.exe by 2G
eventualy after 4 / 5 processings it fails with a page file % nearly full and the users cannot access. CPU useage by is 13% constant build of sql is 3054 on test server and 3152 on live server both servers have same issue.

Was there an answer to the above appart from more RAM i.e. should the memory be released?

Firts process of cube useage = 951M ,
Second process of cube usage starts at 951M and completes at 2,951M
Third process starts at 2,951 and increases etc and so on till memory all used
Thanks

Memory question

I've been reading a lot of the posts about memory but I'm still unclear.
We have 3.37GB RAM - Win2003 SP1 - SQL2005 SP1.
Min Server Memory = 0
Max Server Memory=2147483647
Will SQL use more than 2GB memory with this setup. I saw note of a boot.ini
file in other posts but I couldn't find one on our server. Will SQL use 3GB
if it needs it?
Thanks.
RonHi
Min and Max server memory settings are in Megabytes.
You need to set the /3GB switch in boot.ini for SQL Server to use more than
2GB RAM in your configuration.
--
Regards
Michel Epprecht [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0A1CE509-ACE9-4D01-8F54-757C38074D91@.microsoft.com...
> I've been reading a lot of the posts about memory but I'm still unclear.
> We have 3.37GB RAM - Win2003 SP1 - SQL2005 SP1.
> Min Server Memory = 0
> Max Server Memory=2147483647
> Will SQL use more than 2GB memory with this setup. I saw note of a
> boot.ini
> file in other posts but I couldn't find one on our server. Will SQL use
> 3GB
> if it needs it?
> Thanks.
> Ron
>|||Where is the boot.ini file? I search the hard drive and couldn't find it.
"Michael Epprecht [MSFT]" wrote:
> Hi
> Min and Max server memory settings are in Megabytes.
> You need to set the /3GB switch in boot.ini for SQL Server to use more than
> 2GB RAM in your configuration.
> --
> Regards
> Michel Epprecht [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:0A1CE509-ACE9-4D01-8F54-757C38074D91@.microsoft.com...
> > I've been reading a lot of the posts about memory but I'm still unclear.
> >
> > We have 3.37GB RAM - Win2003 SP1 - SQL2005 SP1.
> > Min Server Memory = 0
> > Max Server Memory=2147483647
> >
> > Will SQL use more than 2GB memory with this setup. I saw note of a
> > boot.ini
> > file in other posts but I couldn't find one on our server. Will SQL use
> > 3GB
> > if it needs it?
> >
> > Thanks.
> >
> > Ron
> >
>

Memory question

Hello.

I have just intstalled 32-bit 2005 Standard Edition on a W2003 SP1 Server.

Am I right in assuming that this version will use as much memory as it can grab from the server up to a limit of 4Gb without me having to alter any settings in SQLServer or Windows?

Thanks

What's the w2003 version? If it is x86 OS, the max vas sql can use is 2GB according to default sql configration. If it is x64 OS, the max memory of sql is up to 4GB (assume the physical memory is enough)|||

apologies, it is 32-bit x86.

The physical memory in the server is 4Gb, can I make SQL use more of the available memory than 2Gb?

|||Yes, you can try to use /3GB or AWE. The actually memory usage will be increased to 2.6-2.7GB in 4G physical memory system.|||

ah-ha, I remember reading somthing about 3GB and AWE a long time ago.

The /3Gb thing increase the memory for any application but AWE increses it for SQL only, am I right?

oh, and Thanks

|||All AWE-aware applicaiton can benefit by AWE configration.|||

Hi,

You can check the article at http://www.kodyaz.com/articles/article.aspx?articleid=41 for sp_configure 'awe enabled' usage and editing boot.ini file for /3GB switch.

Eralper

Memory question

I've been reading a lot of the posts about memory but I'm still unclear.
We have 3.37GB RAM - Win2003 SP1 - SQL2005 SP1.
Min Server Memory = 0
Max Server Memory=2147483647
Will SQL use more than 2GB memory with this setup. I saw note of a boot.ini
file in other posts but I couldn't find one on our server. Will SQL use 3GB
if it needs it?
Thanks.
Ron
Hi
Min and Max server memory settings are in Megabytes.
You need to set the /3GB switch in boot.ini for SQL Server to use more than
2GB RAM in your configuration.
Regards
Michel Epprecht [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0A1CE509-ACE9-4D01-8F54-757C38074D91@.microsoft.com...
> I've been reading a lot of the posts about memory but I'm still unclear.
> We have 3.37GB RAM - Win2003 SP1 - SQL2005 SP1.
> Min Server Memory = 0
> Max Server Memory=2147483647
> Will SQL use more than 2GB memory with this setup. I saw note of a
> boot.ini
> file in other posts but I couldn't find one on our server. Will SQL use
> 3GB
> if it needs it?
> Thanks.
> Ron
>
|||Where is the boot.ini file? I search the hard drive and couldn't find it.
"Michael Epprecht [MSFT]" wrote:

> Hi
> Min and Max server memory settings are in Megabytes.
> You need to set the /3GB switch in boot.ini for SQL Server to use more than
> 2GB RAM in your configuration.
> --
> Regards
> Michel Epprecht [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:0A1CE509-ACE9-4D01-8F54-757C38074D91@.microsoft.com...
>

Memory question

I've been reading a lot of the posts about memory but I'm still unclear.
We have 3.37GB RAM - Win2003 SP1 - SQL2005 SP1.
Min Server Memory = 0
Max Server Memory=2147483647
Will SQL use more than 2GB memory with this setup. I saw note of a boot.ini
file in other posts but I couldn't find one on our server. Will SQL use 3G
B
if it needs it?
Thanks.
RonHi
Min and Max server memory settings are in Megabytes.
You need to set the /3GB switch in boot.ini for SQL Server to use more than
2GB RAM in your configuration.
Regards
Michel Epprecht [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0A1CE509-ACE9-4D01-8F54-757C38074D91@.microsoft.com...
> I've been reading a lot of the posts about memory but I'm still unclear.
> We have 3.37GB RAM - Win2003 SP1 - SQL2005 SP1.
> Min Server Memory = 0
> Max Server Memory=2147483647
> Will SQL use more than 2GB memory with this setup. I saw note of a
> boot.ini
> file in other posts but I couldn't find one on our server. Will SQL use
> 3GB
> if it needs it?
> Thanks.
> Ron
>|||Where is the boot.ini file? I search the hard drive and couldn't find it.
"Michael Epprecht [MSFT]" wrote:

> Hi
> Min and Max server memory settings are in Megabytes.
> You need to set the /3GB switch in boot.ini for SQL Server to use more tha
n
> 2GB RAM in your configuration.
> --
> Regards
> Michel Epprecht [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:0A1CE509-ACE9-4D01-8F54-757C38074D91@.microsoft.com...
>

Memory Pressure Question

SQL Server 2000 SP4 w/ build 2187 running on OS Windows 2003 SP1.
Active/Passive cluster with two nodes. AWE enabled.
We are experiencing memory pressure problems with errors such as
"unable to reserve contiguous memory" and "insufficient memory
available" which lead to fail over. I've researched causes and
applied corrections and/or recommendations accordingly. However,
there are questions regarding linked servers that I cannot find an
answer to. I've identified two queries in object cache using linked
server whose compiled plan exceeds 8K which overflows into the
MemToLeave and on to the linked server whereby linked server is known
to have memory leaks for plans exceeding 8K.
Question 1: Is the linked server 8K limitation for the query plan only
or does this mean the result set is also affected by the 8K
limitation?
Question 2: Will setting the packet size in the provider string
control the size of the query plan passed to linked server and if
applicable, control the size of the result set?
You mention a fialover , so on the other node it workes fine?
Or do you have the same problem.
Sorry , not a direct answer to youre question.
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL
"Patricia" wrote:

> SQL Server 2000 SP4 w/ build 2187 running on OS Windows 2003 SP1.
> Active/Passive cluster with two nodes. AWE enabled.
> We are experiencing memory pressure problems with errors such as
> "unable to reserve contiguous memory" and "insufficient memory
> available" which lead to fail over. I've researched causes and
> applied corrections and/or recommendations accordingly. However,
> there are questions regarding linked servers that I cannot find an
> answer to. I've identified two queries in object cache using linked
> server whose compiled plan exceeds 8K which overflows into the
> MemToLeave and on to the linked server whereby linked server is known
> to have memory leaks for plans exceeding 8K.
> Question 1: Is the linked server 8K limitation for the query plan only
> or does this mean the result set is also affected by the 8K
> limitation?
> Question 2: Will setting the packet size in the provider string
> control the size of the query plan passed to linked server and if
> applicable, control the size of the result set?
>
|||Both nodes are affected.

Memory Pressure Question

SQL Server 2000 SP4 w/ build 2187 running on OS Windows 2003 SP1.
Active/Passive cluster with two nodes. AWE enabled.
We are experiencing memory pressure problems with errors such as
"unable to reserve contiguous memory" and "insufficient memory
available" which lead to fail over. I've researched causes and
applied corrections and/or recommendations accordingly. However,
there are questions regarding linked servers that I cannot find an
answer to. I've identified two queries in object cache using linked
server whose compiled plan exceeds 8K which overflows into the
MemToLeave and on to the linked server whereby linked server is known
to have memory leaks for plans exceeding 8K.
Question 1: Is the linked server 8K limitation for the query plan only
or does this mean the result set is also affected by the 8K
limitation?
Question 2: Will setting the packet size in the provider string
control the size of the query plan passed to linked server and if
applicable, control the size of the result set?You mention a fialover , so on the other node it workes fine?
Or do you have the same problem.
Sorry , not a direct answer to youre question.
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL
"Patricia" wrote:

> SQL Server 2000 SP4 w/ build 2187 running on OS Windows 2003 SP1.
> Active/Passive cluster with two nodes. AWE enabled.
> We are experiencing memory pressure problems with errors such as
> "unable to reserve contiguous memory" and "insufficient memory
> available" which lead to fail over. I've researched causes and
> applied corrections and/or recommendations accordingly. However,
> there are questions regarding linked servers that I cannot find an
> answer to. I've identified two queries in object cache using linked
> server whose compiled plan exceeds 8K which overflows into the
> MemToLeave and on to the linked server whereby linked server is known
> to have memory leaks for plans exceeding 8K.
> Question 1: Is the linked server 8K limitation for the query plan only
> or does this mean the result set is also affected by the 8K
> limitation?
> Question 2: Will setting the packet size in the provider string
> control the size of the query plan passed to linked server and if
> applicable, control the size of the result set?
>|||Both nodes are affected.

Memory Pressure Question

SQL Server 2000 SP4 w/ build 2187 running on OS Windows 2003 SP1.
Active/Passive cluster with two nodes. AWE enabled.
We are experiencing memory pressure problems with errors such as
"unable to reserve contiguous memory" and "insufficient memory
available" which lead to fail over. I've researched causes and
applied corrections and/or recommendations accordingly. However,
there are questions regarding linked servers that I cannot find an
answer to. I've identified two queries in object cache using linked
server whose compiled plan exceeds 8K which overflows into the
MemToLeave and on to the linked server whereby linked server is known
to have memory leaks for plans exceeding 8K.
Question 1: Is the linked server 8K limitation for the query plan only
or does this mean the result set is also affected by the 8K
limitation?
Question 2: Will setting the packet size in the provider string
control the size of the query plan passed to linked server and if
applicable, control the size of the result set?You mention a fialover , so on the other node it workes fine?
Or do you have the same problem.
Sorry , not a direct answer to youre question.
--
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL :)
"Patricia" wrote:
> SQL Server 2000 SP4 w/ build 2187 running on OS Windows 2003 SP1.
> Active/Passive cluster with two nodes. AWE enabled.
> We are experiencing memory pressure problems with errors such as
> "unable to reserve contiguous memory" and "insufficient memory
> available" which lead to fail over. I've researched causes and
> applied corrections and/or recommendations accordingly. However,
> there are questions regarding linked servers that I cannot find an
> answer to. I've identified two queries in object cache using linked
> server whose compiled plan exceeds 8K which overflows into the
> MemToLeave and on to the linked server whereby linked server is known
> to have memory leaks for plans exceeding 8K.
> Question 1: Is the linked server 8K limitation for the query plan only
> or does this mean the result set is also affected by the 8K
> limitation?
> Question 2: Will setting the packet size in the provider string
> control the size of the query plan passed to linked server and if
> applicable, control the size of the result set?
>|||Both nodes are affected.