MSXML parser & SQL Server Memory

The below snippet from SQL Server BOL under the topic "sp_xml_preparedocument" implies that the use of XML can be extremely memory intensive. Is is really the case that MSXML uses 1/8 total SQL Server memory? I find this astounding if true.
"Note A parsed document is stored in the internal cache of SQL Server 2000. The MSXML parser uses one-eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory."
It means that it can use up to 1/8th of the memory. Unless you try to parse
a very large document or never release the allocated memory, you should
never use or reach this limit.
HTH
Michael
"glr" <glr@.discussions.microsoft.com> wrote in message
news:DE9CFF6C-B949-4318-9294-6B4EFB773AF2@.microsoft.com...
> The below snippet from SQL Server BOL under the topic
> "sp_xml_preparedocument" implies that the use of XML can be extremely
> memory intensive. Is is really the case that MSXML uses 1/8 total SQL
> Server memory? I find this astounding if true.
> "Note A parsed document is stored in the internal cache of SQL Server
> 2000. The MSXML parser uses one-eighth the total memory available for SQL
> Server. To avoid running out of memory, run sp_xml_removedocument to free
> up the memory."
>

MSSQL2000 Maximum Memory

Hi,
I want to use MSSQL2000 on a server with 8GB ram amount.
Is there a way to use this ram under Win2003 Server?
What version of MSSQL2000 do I have to use?
thx
reinhold
Hi
You need to use:
Windows 2003 Enterprise Edition
SQL Server 2000 Enterprise Edition
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Reinhold" wrote:

> Hi,
> I want to use MSSQL2000 on a server with 8GB ram amount.
> Is there a way to use this ram under Win2003 Server?
> What version of MSSQL2000 do I have to use?
> thx
> reinhold
>
>
|||See if this helps:
http://support.microsoft.com/default...274750&sd=tech
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Reinhold" <reinholderlacher@.msn.com> wrote in message
news:OrbtVcisFHA.3424@.TK2MSFTNGP14.phx.gbl...
Hi,
I want to use MSSQL2000 on a server with 8GB ram amount.
Is there a way to use this ram under Win2003 Server?
What version of MSSQL2000 do I have to use?
thx
reinhold
|||> I want to use MSSQL2000 on a server with 8GB ram amount.
> Is there a way to use this ram under Win2003 Server?
Assuming 32-bit, you need Windows 2003 Server Enterprise Edition and SQL
Server Enterprise Edition. SQL Server AWE needs to be enabled to address
over 3GB. Windows 2003 Standard has a 4GB max memory limitation and SQL
Server Standard is limited to 2Gb.
Windows 2003 system requirements:
http://www.microsoft.com/windowsserv...s/default.mspx
How to configure memory for more than 2 GB in SQL Server:
http://support.microsoft.com/default...b;en-us;274750
Hope this helps.
Dan Guzman
SQL Server MVP
"Reinhold" <reinholderlacher@.msn.com> wrote in message
news:OrbtVcisFHA.3424@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I want to use MSSQL2000 on a server with 8GB ram amount.
> Is there a way to use this ram under Win2003 Server?
> What version of MSSQL2000 do I have to use?
> thx
> reinhold
>

MSSQL2000 Maximum Memory

Hi,
I want to use MSSQL2000 on a server with 8GB ram amount.
Is there a way to use this ram under Win2003 Server?
What version of MSSQL2000 do I have to use?
thx
reinholdHi
You need to use:
Windows 2003 Enterprise Edition
SQL Server 2000 Enterprise Edition
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Reinhold" wrote:

> Hi,
> I want to use MSSQL2000 on a server with 8GB ram amount.
> Is there a way to use this ram under Win2003 Server?
> What version of MSSQL2000 do I have to use?
> thx
> reinhold
>
>|||See if this helps:
http://support.microsoft.com/defaul...;274750&sd=tech
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Reinhold" <reinholderlacher@.msn.com> wrote in message
news:OrbtVcisFHA.3424@.TK2MSFTNGP14.phx.gbl...
Hi,
I want to use MSSQL2000 on a server with 8GB ram amount.
Is there a way to use this ram under Win2003 Server?
What version of MSSQL2000 do I have to use?
thx
reinhold|||> I want to use MSSQL2000 on a server with 8GB ram amount.
> Is there a way to use this ram under Win2003 Server?
Assuming 32-bit, you need Windows 2003 Server Enterprise Edition and SQL
Server Enterprise Edition. SQL Server AWE needs to be enabled to address
over 3GB. Windows 2003 Standard has a 4GB max memory limitation and SQL
Server Standard is limited to 2Gb.
Windows 2003 system requirements:
http://www.microsoft.com/windowsser...qs/default.mspx
How to configure memory for more than 2 GB in SQL Server:
http://support.microsoft.com/defaul...kb;en-us;274750
Hope this helps.
Dan Guzman
SQL Server MVP
"Reinhold" <reinholderlacher@.msn.com> wrote in message
news:OrbtVcisFHA.3424@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I want to use MSSQL2000 on a server with 8GB ram amount.
> Is there a way to use this ram under Win2003 Server?
> What version of MSSQL2000 do I have to use?
> thx
> reinhold
>

MSSQL2000 Maximum Memory

Hi,
I want to use MSSQL2000 on a server with 8GB ram amount.
Is there a way to use this ram under Win2003 Server?
What version of MSSQL2000 do I have to use?
thx
reinholdHi
You need to use:
Windows 2003 Enterprise Edition
SQL Server 2000 Enterprise Edition
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Reinhold" wrote:
> Hi,
> I want to use MSSQL2000 on a server with 8GB ram amount.
> Is there a way to use this ram under Win2003 Server?
> What version of MSSQL2000 do I have to use?
> thx
> reinhold
>
>|||See if this helps:
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Reinhold" <reinholderlacher@.msn.com> wrote in message
news:OrbtVcisFHA.3424@.TK2MSFTNGP14.phx.gbl...
Hi,
I want to use MSSQL2000 on a server with 8GB ram amount.
Is there a way to use this ram under Win2003 Server?
What version of MSSQL2000 do I have to use?
thx
reinhold|||> I want to use MSSQL2000 on a server with 8GB ram amount.
> Is there a way to use this ram under Win2003 Server?
Assuming 32-bit, you need Windows 2003 Server Enterprise Edition and SQL
Server Enterprise Edition. SQL Server AWE needs to be enabled to address
over 3GB. Windows 2003 Standard has a 4GB max memory limitation and SQL
Server Standard is limited to 2Gb.
Windows 2003 system requirements:
http://www.microsoft.com/windowsserver2003/evaluation/sysreqs/default.mspx
How to configure memory for more than 2 GB in SQL Server:
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Reinhold" <reinholderlacher@.msn.com> wrote in message
news:OrbtVcisFHA.3424@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I want to use MSSQL2000 on a server with 8GB ram amount.
> Is there a way to use this ram under Win2003 Server?
> What version of MSSQL2000 do I have to use?
> thx
> reinhold
>

MSSQL Server 2000 Memory Grows Incrementally

Hi All
My SQL Server keeps on taking memory(RAM) on the server without
releasing it.
There seems to be a memory leak.
On every query that gets executed the memory occupied by MSSQL grows.
Does this make the subsequent queries execute slowly?
Also even though i execute the same query multiple time the memory size
still increases.
What could be the possible reason for this?
Thanks & Regards
Vishal.
Vishal wrote:
> Hi All
> My SQL Server keeps on taking memory(RAM) on the server without
> releasing it.
> There seems to be a memory leak.
> On every query that gets executed the memory occupied by MSSQL grows.
> Does this make the subsequent queries execute slowly?
> Also even though i execute the same query multiple time the memory size
> still increases.
> What could be the possible reason for this?
> Thanks & Regards
> Vishal.
>
This is normal behavior. SQL Server will cache data pages in memory as
they are loaded from disk. It won't release memory unless it is
configured for dynamic memory allocation, AND the OS requests it.
Having SQL data cached in memory is a good thing, let it use as much as
it needs.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||The behaviour you are seeing is by design. Here is a KB article on the
subject:
INF: SQL Server Memory Usage
http://support.microsoft.com/default...;en-us;q321363
Keith Kratochvil
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1160134477.258235.198120@.h48g2000cwc.googlegr oups.com...
> Hi All
> My SQL Server keeps on taking memory(RAM) on the server without
> releasing it.
> There seems to be a memory leak.
> On every query that gets executed the memory occupied by MSSQL grows.
> Does this make the subsequent queries execute slowly?
> Also even though i execute the same query multiple time the memory size
> still increases.
> What could be the possible reason for this?
> Thanks & Regards
> Vishal.
>
|||Dear Tracy,
How to set up the "dynamic memory allocation" ?
Thanks
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4526486E.4090509@.realsqlguy.com...
> Vishal wrote:
> This is normal behavior. SQL Server will cache data pages in memory as
> they are loaded from disk. It won't release memory unless it is
> configured for dynamic memory allocation, AND the OS requests it. Having
> SQL data cached in memory is a good thing, let it use as much as it needs.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Thanks Tracy
How to configure dynamic Memory Allocation?
Rgds
Vishal.
Tracy McKibben wrote:
> Vishal wrote:
> This is normal behavior. SQL Server will cache data pages in memory as
> they are loaded from disk. It won't release memory unless it is
> configured for dynamic memory allocation, AND the OS requests it.
> Having SQL data cached in memory is a good thing, let it use as much as
> it needs.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Vishal wrote:
> Thanks Tracy
> How to configure dynamic Memory Allocation?
See "Server Memory Options" under "Setting Configuration Options" in
Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Dear Tracy,
Thank you for your advice.
Peter
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:452B94DC.8000706@.realsqlguy.com...
> Vishal wrote:
> See "Server Memory Options" under "Setting Configuration Options" in Books
> Online...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

MSSQL Server 2000 Memory Grows Incrementally

Hi All
My SQL Server keeps on taking memory(RAM) on the server without
releasing it.
There seems to be a memory leak.
On every query that gets executed the memory occupied by MSSQL grows.
Does this make the subsequent queries execute slowly?
Also even though i execute the same query multiple time the memory size
still increases.
What could be the possible reason for this?
Thanks & Regards
Vishal.Vishal wrote:
> Hi All
> My SQL Server keeps on taking memory(RAM) on the server without
> releasing it.
> There seems to be a memory leak.
> On every query that gets executed the memory occupied by MSSQL grows.
> Does this make the subsequent queries execute slowly?
> Also even though i execute the same query multiple time the memory size
> still increases.
> What could be the possible reason for this?
> Thanks & Regards
> Vishal.
>
This is normal behavior. SQL Server will cache data pages in memory as
they are loaded from disk. It won't release memory unless it is
configured for dynamic memory allocation, AND the OS requests it.
Having SQL data cached in memory is a good thing, let it use as much as
it needs.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The behaviour you are seeing is by design. Here is a KB article on the
subject:
INF: SQL Server Memory Usage
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
--
Keith Kratochvil
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1160134477.258235.198120@.h48g2000cwc.googlegroups.com...
> Hi All
> My SQL Server keeps on taking memory(RAM) on the server without
> releasing it.
> There seems to be a memory leak.
> On every query that gets executed the memory occupied by MSSQL grows.
> Does this make the subsequent queries execute slowly?
> Also even though i execute the same query multiple time the memory size
> still increases.
> What could be the possible reason for this?
> Thanks & Regards
> Vishal.
>|||Dear Tracy,
How to set up the "dynamic memory allocation" ?
Thanks
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4526486E.4090509@.realsqlguy.com...
> Vishal wrote:
>> Hi All
>> My SQL Server keeps on taking memory(RAM) on the server without
>> releasing it.
>> There seems to be a memory leak.
>> On every query that gets executed the memory occupied by MSSQL grows.
>> Does this make the subsequent queries execute slowly?
>> Also even though i execute the same query multiple time the memory size
>> still increases.
>> What could be the possible reason for this?
>> Thanks & Regards
>> Vishal.
> This is normal behavior. SQL Server will cache data pages in memory as
> they are loaded from disk. It won't release memory unless it is
> configured for dynamic memory allocation, AND the OS requests it. Having
> SQL data cached in memory is a good thing, let it use as much as it needs.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Thanks Tracy
How to configure dynamic Memory Allocation'
Rgds
Vishal.
Tracy McKibben wrote:
> Vishal wrote:
> > Hi All
> >
> > My SQL Server keeps on taking memory(RAM) on the server without
> > releasing it.
> > There seems to be a memory leak.
> > On every query that gets executed the memory occupied by MSSQL grows.
> > Does this make the subsequent queries execute slowly?
> >
> > Also even though i execute the same query multiple time the memory size
> > still increases.
> > What could be the possible reason for this?
> >
> > Thanks & Regards
> > Vishal.
> >
> This is normal behavior. SQL Server will cache data pages in memory as
> they are loaded from disk. It won't release memory unless it is
> configured for dynamic memory allocation, AND the OS requests it.
> Having SQL data cached in memory is a good thing, let it use as much as
> it needs.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Vishal wrote:
> Thanks Tracy
> How to configure dynamic Memory Allocation'
See "Server Memory Options" under "Setting Configuration Options" in
Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Dear Tracy,
Thank you for your advice.
Peter
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:452B94DC.8000706@.realsqlguy.com...
> Vishal wrote:
>> Thanks Tracy
>> How to configure dynamic Memory Allocation'
> See "Server Memory Options" under "Setting Configuration Options" in Books
> Online...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

MSSQL Server 2000 Memory Grows Incrementally

Hi All
My SQL Server keeps on taking memory(RAM) on the server without
releasing it.
There seems to be a memory leak.
On every query that gets executed the memory occupied by MSSQL grows.
Does this make the subsequent queries execute slowly?
Also even though i execute the same query multiple time the memory size
still increases.
What could be the possible reason for this?
Thanks & Regards
Vishal.Vishal wrote:
> Hi All
> My SQL Server keeps on taking memory(RAM) on the server without
> releasing it.
> There seems to be a memory leak.
> On every query that gets executed the memory occupied by MSSQL grows.
> Does this make the subsequent queries execute slowly?
> Also even though i execute the same query multiple time the memory size
> still increases.
> What could be the possible reason for this?
> Thanks & Regards
> Vishal.
>
This is normal behavior. SQL Server will cache data pages in memory as
they are loaded from disk. It won't release memory unless it is
configured for dynamic memory allocation, AND the OS requests it.
Having SQL data cached in memory is a good thing, let it use as much as
it needs.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The behaviour you are seeing is by design. Here is a KB article on the
subject:
INF: SQL Server Memory Usage
http://support.microsoft.com/defaul...b;en-us;q321363
Keith Kratochvil
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1160134477.258235.198120@.h48g2000cwc.googlegroups.com...
> Hi All
> My SQL Server keeps on taking memory(RAM) on the server without
> releasing it.
> There seems to be a memory leak.
> On every query that gets executed the memory occupied by MSSQL grows.
> Does this make the subsequent queries execute slowly?
> Also even though i execute the same query multiple time the memory size
> still increases.
> What could be the possible reason for this?
> Thanks & Regards
> Vishal.
>|||Dear Tracy,
How to set up the "dynamic memory allocation" ?
Thanks
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4526486E.4090509@.realsqlguy.com...
> Vishal wrote:
> This is normal behavior. SQL Server will cache data pages in memory as
> they are loaded from disk. It won't release memory unless it is
> configured for dynamic memory allocation, AND the OS requests it. Having
> SQL data cached in memory is a good thing, let it use as much as it needs.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Thanks Tracy
How to configure dynamic Memory Allocation'
Rgds
Vishal.
Tracy McKibben wrote:
> Vishal wrote:
> This is normal behavior. SQL Server will cache data pages in memory as
> they are loaded from disk. It won't release memory unless it is
> configured for dynamic memory allocation, AND the OS requests it.
> Having SQL data cached in memory is a good thing, let it use as much as
> it needs.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Vishal wrote:
> Thanks Tracy
> How to configure dynamic Memory Allocation'
See "Server Memory Options" under "Setting Configuration Options" in
Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Dear Tracy,
Thank you for your advice.
Peter
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:452B94DC.8000706@.realsqlguy.com...
> Vishal wrote:
> See "Server Memory Options" under "Setting Configuration Options" in Books
> Online...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

MSSQL Query Memory when using long fields

Here is our problem...

We are doing a lot of selects against a table that has one large field
in it.

If we do a select against all the fields except for description, the
query comes back relatively quickly. If we add that last field (768
chars) to the query, our query takes 10x longer (5 seconds vs 56
seconds.)

When we run the one without the description column, we can watch
perfmon and see a very quick spike to physical disk. If we add in the
description field we can see that the server becomes I/O bound - the
disk sits at 100% until the query is complete.

We have tweaked the min query memory setting for the server but it
seems to have had no effect no matter how high we set it. Is there
some point at which MSSQL decides it cannot perform the transaction in
memory? What would I increase to cure this problem?

For example:
TMZDIFF int410
WRITETIMEcharno 16
System_Namecharno 64
Timestampcharno16
Name charno32
Mount_Pointcharno32
Size intno4 10
Space_Usedintno410
Space_Availableintno410
Inode_Sizeintno410
Inodes_Usedintno410
Inodes_Freeintno410
Space_Used_Percentintno410
Inodes_Used_Percentintno410
FS_Type charno8
Space_Available_Percentintno410
Name_U ncharno32
Descriptionncharno768(wildfyre53207@.yahoo.com) writes:
> If we do a select against all the fields except for description, the
> query comes back relatively quickly. If we add that last field (768
> chars) to the query, our query takes 10x longer (5 seconds vs 56
> seconds.)

My initial reaction is that there is an index on the table that covers
all columns, save the long column.

Could you post CREATE TABLE and CREATE INDEX statements for the table?
Don't forget PRIMARY KEY and UNIQUE constraints.

> We have tweaked the min query memory setting for the server but it
> seems to have had no effect no matter how high we set it. Is there
> some point at which MSSQL decides it cannot perform the transaction in
> memory? What would I increase to cure this problem?

How many rows are you returning? On what sort of network connection?
How much memory do you have in the machine?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||give us an example of the two selects.