Merge heavy on distr cpu

125Gb heavy OLTP publication db w/ Transactional Replication replicating 400
tables to 5 different servers. No problems on remote distributor, cpu hardly
used, sits practically idle most of the day staying under 10%. Ram is used
however not getting taxed.
We added merge replication for 1 table and the cpu's pegged to 90 to 100%
each time the merge agents pick up transactions at pub. Every 20 minutes a
job populated this table w/ 100 to 200 inserts at publisher, the 5 merge
agents kick in immediately (continuously running) and spike cpu bringing
server to a crawl.
Publisher, Distributor, & all Subscribers on LAN w/ 100Mbps connections.
SQL 2000 SP3 on all servers. Distributor is a 6CPU PIII 700Mhz each. New
Distributor hardware not an option. Have to limp along until new budgets kick
in...
Thoughts...?
TIA,
Chris
Chris,
are the 100/200 inserts involving BLOB datatypes? How long does the spike
last? Anyway, I'd change the merge agents from running continuously, in
order to stagger their impact on the publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Merge Agent takes all the CPU time

The merge Agent takes all the CPU resources and makes no space for other
aplications. When I stope the Server Agent the Server becomes available to
other applications. I start the merge agent again and every thing works fine
.
How can I handle this so I wont have to stop the merge agent each time the
usrs start complaining?
Thanks a lot,
LinaHow often is your merge agent running..? Hourly ..?
Ideally you want to run the merge agent frequently, that way it has a small
amount of data to process and will have less impact on resources
HTH. Ryan
"Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in message
news:956B2254-A621-4752-BFCD-1DF47151A269@.microsoft.com...
> The merge Agent takes all the CPU resources and makes no space for other
> aplications. When I stope the Server Agent the Server becomes available to
> other applications. I start the merge agent again and every thing works
> fine.
> How can I handle this so I wont have to stop the merge agent each time the
> usrs start complaining?
> Thanks a lot,
> Lina|||Hi Ryan
I have to schedules:
One continuos
and the other one each 5 minutes.
Thank you, Lina
"Ryan" wrote:

> How often is your merge agent running..? Hourly ..?
> Ideally you want to run the merge agent frequently, that way it has a smal
l
> amount of data to process and will have less impact on resources
> --
> HTH. Ryan
> "Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in messag
e
> news:956B2254-A621-4752-BFCD-1DF47151A269@.microsoft.com...
>
>|||In that case i would suggest running a profiler trace to capture what stored
procedure the merge agent is running when the CPU spike occurs. You may find
it's related to the size of the metadata tables "MSmerge_contents,
MSmerge_genhistory" or the filtering conditions within the publication.
How to troubleshoot SQL Server merge replication problems :-
http://support.microsoft.com/?id=315521
HTH. Ryan
"Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in message
news:6FD33C8B-468A-4DC4-860C-9E27ED644743@.microsoft.com...
> Hi Ryan
> I have to schedules:
> One continuos
> and the other one each 5 minutes.
> Thank you, Lina
> "Ryan" wrote:
>|||Thanks a lot
Lina
"Ryan" wrote:

> In that case i would suggest running a profiler trace to capture what stor
ed
> procedure the merge agent is running when the CPU spike occurs. You may fi
nd
> it's related to the size of the metadata tables "MSmerge_contents,
> MSmerge_genhistory" or the filtering conditions within the publication.
>
> How to troubleshoot SQL Server merge replication problems :-
> http://support.microsoft.com/?id=315521
>
> --
> HTH. Ryan
> "Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in messag
e
> news:6FD33C8B-468A-4DC4-860C-9E27ED644743@.microsoft.com...
>
>

memory usage

Hello,
I have a sql 2000 box that has roughly 15 datbases. How do i determine how
much memory/cpu a particular db is using?
thanks!
Calvin
DBs don't use CPU, client connections do. You can correlate CPU use for
current connections by doing a little extrapolation from the
master.dbo.sysprocesses table. That table lists all current connections
and each row specifies which DB is currently being used (dbid) by the
connection and how much CPU time that connection has consumed. (Take
this with a grain of salt though because a SPID can change DB simply by
running a USE statement, so there's no guarantee the SPID has been using
the same DB its whole life.) This is a cumulative value so if a client
has been connected to the server by the same SPID for a long time, the
cumulative CPU will be high compared to a relatively new SPID. Just
bear that in mind when extrapolating - you might do well to divide the
CPU figure by the number of hours, "datediff(hh, login_time,
getdate())", (or minutes or days or...) the SPID has been alive for to
get an hourly average CPU - slightly more helpful.
As for memory, it's very hard to tell what percentage of memory is
attributable to each DB. SQL Server simply caches data pages
(regardless of which DB they belong to) when they're accessed. The
execution plans stored in the procedure cache can involve objects in
many databases so you can't really attribute them to any particular DB.
You can see a little bit of information about the buffer cache by running:
DBCC MEMUSAGE
This will show you a little info about the top 20 objects in terms of
cache use. It lists the dbid (what you're interested in), the objectid
& indexid from that DB (this will be the id of the associated
table/index) and the number of buffers (8K pages?) being used by that
object in the buffer cache. NB/ Microsoft recommend not using DBCC
MEMUSAGE and using the related perfmon counters instead (see
http://msdn.microsoft.com/library/de...kcomp_992x.asp)
but I find DBCC MEMUSAGE still slightly helpful, although I suppose you
could get the perfmon data you're after (that replaces DBCC MEMUSAGE)
from querying the master.dbo.sysperfinfo table and correlating that data.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Calvin Do wrote:

>Hello,
>
>I have a sql 2000 box that has roughly 15 datbases. How do i determine how
>much memory/cpu a particular db is using?
>
>thanks!
>Calvin
>
>
|||thank you for the email Mike, i will review...
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%23bzeX6gHFHA.720@.TK2MSFTNGP10.phx.gbl...
DBs don't use CPU, client connections do. You can correlate CPU use for current connections by doing a little extrapolation from the master.dbo.sysprocesses table. That table lists all current connections and each row specifies which DB is currently being used (dbid) by the connection and how much CPU time that connection has consumed. (Take this with a grain of salt though because a SPID can change DB simply by running a USE statement, so there's no guarantee the SPID has been using the same DB its whole life.) This is a cumulative value so if a client has been connected to the server by the same SPID for a long time, the cumulative CPU will be high compared to a relatively new SPID. Just bear that in mind when extrapolating - you might do well to divide the CPU figure by the number of hours, "datediff(hh, login_time, getdate())", (or minutes or days or...) the SPID has been alive for to get an hourly average CPU - slightly more helpful.
As for memory, it's very hard to tell what percentage of memory is attributable to each DB. SQL Server simply caches data pages (regardless of which DB they belong to) when they're accessed. The execution plans stored in the procedure cache can involve objects in many databases so you can't really attribute them to any particular DB. You can see a little bit of information about the buffer cache by running:
DBCC MEMUSAGE
This will show you a little info about the top 20 objects in terms of cache use. It lists the dbid (what you're interested in), the objectid & indexid from that DB (this will be the id of the associated table/index) and the number of buffers (8K pages?) being used by that object in the buffer cache. NB/ Microsoft recommend not using DBCC MEMUSAGE and using the related perfmon counters instead (see http://msdn.microsoft.com/library/de...kcomp_992x.asp) but I find DBCC MEMUSAGE still slightly helpful, although I suppose you could get the perfmon data you're after (that replaces DBCC MEMUSAGE) from querying the master.dbo.sysperfinfo table and correlating that data.
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Calvin Do wrote:
Hello,
I have a sql 2000 box that has roughly 15 datbases. How do i determine how
much memory/cpu a particular db is using?
thanks!
Calvin

memory setting in SQL 2005 in Windows 2003

We have a SQL 2005 installed in Windows 2003 64 bit system, which has 16 CPU and 32GB RAM, but the performance is poor. SQL server is AWE enable and the sql start account with "Lock Pages In Memory". I checked the task manager and it looks that the SQL server used only about 300 MB memory. Here is what I found:

http://blogs.msdn.com/psssql/archive/2006/11/28/sql-server-becomes-sluggish-or-appears-to-stall-on-64-bit-installations.aspx?CommentPosted=true#commentmessage

What I need to do?

Thanks

Sounds like you have installed the 32 bit version of SQL Server, not the 64bit version of SQL Server.

Can you verify which version of SQL Server was installed?

|||

If I recall, AWE has no effect in 64 bit OS, and extended memory usage is not properly captured with Perf Monitor.

Please refer to this article:

Configuration -Memory, 32 bit SQL 2005 on 64 Bit Windows 2003
http://msdn2.microsoft.com/en-us/library/ms187499.aspx

And for reference on Memory, this is good starting point:

http://msdn2.microsoft.com/en-us/library/ms187499.aspx

|||

Hi Sandlu,

AWE will have no effect on 64 bit servers.

Yes you still need "LOCK PAGES IN MEMORY".

To check exactly how much memory is being used by SQL Server use Perfmon to check for SQL ServerMemoryManager:

Total Server Memory (KB)

Also check if you have capped the memory by using Max Server Memory under sp_configure.

With regards to slow performance, it could be any problem like disks, memory or no/out of date stats or Index defragmentation.

Jag

|||Thank you, everyone, will try.

Memory Paging Issue

We currently are using SQL 7.0 Standard Edition w/3 gigs of ram (yes, I know
that SQL is only using 2 gigs) and 4 cpu's. I am having paging issues as
shown below:
Averaged of 24 hour period:
Page Reads/sec: 39.287
Pages Input/sec: 438.466
I have recommended we go to a server with 8 gigs of ram, 8 cpu's and SQL
2000 Enterprise Edition. I have now been asked to give management some idea
of when this configuration will be to limited and prove to them that this
will eliminate the paging issues.
Anybody have any ideas on how I can intelligently give Management this
information? Does Microsoft have any Capacity Planning White Papers?First, what happens to the application when it starts
paging? What behaviors do you notice? Have you done any
other monitoring?
Since SQL 2000 is a different beast than SQL 7, start your
numbers based on what you see in SQL 7 - with N number of
users doing X, Y, and Z type of work, we max out at 4
proc/2 GB for SQL. But that really is a guesstimate.
What you need to honestly do is understand why your app is
paging - is the code written poorly? Sure, throwing new
OS, SQL, and HW at it may solve your problem, but it may
not. You should definitively prove that. As much as I
love SQL 2K, moving for the sake of moving is never a good
idea.
>--Original Message--
>We currently are using SQL 7.0 Standard Edition w/3 gigs
of ram (yes, I know
>that SQL is only using 2 gigs) and 4 cpu's. I am having
paging issues as
>shown below:
>Averaged of 24 hour period:
> Page Reads/sec: 39.287
> Pages Input/sec: 438.466
>I have recommended we go to a server with 8 gigs of ram,
8 cpu's and SQL
>2000 Enterprise Edition. I have now been asked to give
management some idea
>of when this configuration will be to limited and prove
to them that this
>will eliminate the paging issues.
>Anybody have any ideas on how I can intelligently give
Management this
>information? Does Microsoft have any Capacity Planning
White Papers?
>
>.
>|||sql server by itself should not cause paging,
are there other applications that could be generating the
paging activity?
are there extended stored procs?
if there a large difference between the memory usage and
virtual memory in task manager?
have you checked the perfmon counters for Process - sql
servr Page File Bytes and Page Faults?
I like many of the features in SQL Server 2000, but agree
with Allan
in any case the SQL Serve version should not be related to
the paging,
your SQL instance may certainly benefit from more memory,
but should show in the physical disk counters, not the
memory paging counters
>--Original Message--
>We currently are using SQL 7.0 Standard Edition w/3 gigs
of ram (yes, I know
>that SQL is only using 2 gigs) and 4 cpu's. I am having
paging issues as
>shown below:
>Averaged of 24 hour period:
> Page Reads/sec: 39.287
> Pages Input/sec: 438.466
>I have recommended we go to a server with 8 gigs of ram,
8 cpu's and SQL
>2000 Enterprise Edition. I have now been asked to give
management some idea
>of when this configuration will be to limited and prove
to them that this
>will eliminate the paging issues.
>Anybody have any ideas on how I can intelligently give
Management this
>information? Does Microsoft have any Capacity Planning
White Papers?
>
>.
>|||I do know that the code is very poorly written and there are over 200
databases on the server. I have recommended that they need to move some of
the databases to a different server. All 200+ are heavily used. I recently
counted over 2 million queries/Stored Procs that were run in a 1 hour
period. The only thing running on the server is SQL. I have monitored CPU
and it is actually in good shape, the only problem is paging and during the
periods of paging, extreme slowness. So far I have been unable to corelate
the paging with any particular query/stored proc.
"Allan Hirt" <allanh@.NOSPAMavanade.com> wrote in message
news:010f01c3be76$2731a850$a301280a@.phx.gbl...
> First, what happens to the application when it starts
> paging? What behaviors do you notice? Have you done any
> other monitoring?
> Since SQL 2000 is a different beast than SQL 7, start your
> numbers based on what you see in SQL 7 - with N number of
> users doing X, Y, and Z type of work, we max out at 4
> proc/2 GB for SQL. But that really is a guesstimate.
> What you need to honestly do is understand why your app is
> paging - is the code written poorly? Sure, throwing new
> OS, SQL, and HW at it may solve your problem, but it may
> not. You should definitively prove that. As much as I
> love SQL 2K, moving for the sake of moving is never a good
> idea.
>
> >--Original Message--
> >We currently are using SQL 7.0 Standard Edition w/3 gigs
> of ram (yes, I know
> >that SQL is only using 2 gigs) and 4 cpu's. I am having
> paging issues as
> >shown below:
> >
> >Averaged of 24 hour period:
> > Page Reads/sec: 39.287
> > Pages Input/sec: 438.466
> >
> >I have recommended we go to a server with 8 gigs of ram,
> 8 cpu's and SQL
> >2000 Enterprise Edition. I have now been asked to give
> management some idea
> >of when this configuration will be to limited and prove
> to them that this
> >will eliminate the paging issues.
> >
> >Anybody have any ideas on how I can intelligently give
> Management this
> >information? Does Microsoft have any Capacity Planning
> White Papers?
> >
> >
> >.
> >|||SQL should not page in that configuration if it's the only app on there.
Are you seeing high disk queuing on the page file disk and have correleted
spikes in those counters with performance problems as well? If not, I
wouldn't be overly concerned about those numbers.
A couple things that causes fairly steady paging I have seen is file server
or disk-tape backup activities, but those generally won't cause severe
performance problems.
"Mike Johnson" <mj@.noemail.com> wrote in message
news:%23V8An$mvDHA.2464@.TK2MSFTNGP12.phx.gbl...
> We currently are using SQL 7.0 Standard Edition w/3 gigs of ram (yes, I
know
> that SQL is only using 2 gigs) and 4 cpu's. I am having paging issues as
> shown below:
> Averaged of 24 hour period:
> Page Reads/sec: 39.287
> Pages Input/sec: 438.466
> I have recommended we go to a server with 8 gigs of ram, 8 cpu's and SQL
> 2000 Enterprise Edition. I have now been asked to give management some
idea
> of when this configuration will be to limited and prove to them that this
> will eliminate the paging issues.
> Anybody have any ideas on how I can intelligently give Management this
> information? Does Microsoft have any Capacity Planning White Papers?
>

Memory Manager

The CPU pegged on a production server this morning. There is a warning
message in the log. I'm not sure what it means. Or what to do to prevent it
in the future.
Date 1/23/2008 4:11:36 AM
Log SQL Server (Archive #1 - 1/23/2008 6:41:00 AM)
Source spid3s
Message
Memory Manager
VM Reserved = 8521512 KB
VM Committed = 8504536 KB
AWE Allocated = 0 KB
Reserved Memory = 1024 KB
Reserved Memory In Use = 0 KB
Then later on
Date 1/23/2008 4:11:36 AM
Log SQL Server (Archive #1 - 1/23/2008 6:41:00 AM)
Source spid3s
Message
MEMORYCLERK_SQLGENERAL (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 28920 KB
MultiPage Allocator = 4888 KB
Still later
Date 1/23/2008 4:11:36 AM
Log SQL Server (Archive #1 - 1/23/2008 6:41:00 AM)
Source spid3s
Message
Buffer Counts: Committed=1032192 Target=1047941 Hashed=608946
Internal Reservation=209588 External Reservation=1297
Stolen Potential=571000
Min Free=128 Visible=1047941
Available Paging File=858726400
Finally:
Date 1/23/2008 4:12:19 AM
Log SQL Server (Archive #1 - 1/23/2008 6:41:00 AM)
Source Server
Message
***Stack Dump being sent to D:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\LOG\SQLDump0008.txt
--
Regards,
JamieProbably time to call Microsoft tech support. Or scan the bug fix lists for
all patches you don't have on your server to see if one of them addresses
the symptoms you are seeing.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:B2F823A0-5AB3-4EF6-8F16-D515052F7DE4@.microsoft.com...
> The CPU pegged on a production server this morning. There is a warning
> message in the log. I'm not sure what it means. Or what to do to prevent
> it
> in the future.
> Date 1/23/2008 4:11:36 AM
> Log SQL Server (Archive #1 - 1/23/2008 6:41:00 AM)
> Source spid3s
> Message
> Memory Manager
> VM Reserved = 8521512 KB
> VM Committed = 8504536 KB
> AWE Allocated = 0 KB
> Reserved Memory = 1024 KB
> Reserved Memory In Use = 0 KB
>
> Then later on
> Date 1/23/2008 4:11:36 AM
> Log SQL Server (Archive #1 - 1/23/2008 6:41:00 AM)
> Source spid3s
> Message
> MEMORYCLERK_SQLGENERAL (Total)
> VM Reserved = 0 KB
> VM Committed = 0 KB
> AWE Allocated = 0 KB
> SM Reserved = 0 KB
> SM Committed = 0 KB
> SinglePage Allocator = 28920 KB
> MultiPage Allocator = 4888 KB
> Still later
> Date 1/23/2008 4:11:36 AM
> Log SQL Server (Archive #1 - 1/23/2008 6:41:00 AM)
> Source spid3s
> Message
> Buffer Counts: Committed=1032192 Target=1047941 Hashed=608946
> Internal Reservation=209588 External Reservation=1297
> Stolen Potential=571000
> Min Free=128 Visible=1047941
> Available Paging File=858726400
>
> Finally:
> Date 1/23/2008 4:12:19 AM
> Log SQL Server (Archive #1 - 1/23/2008 6:41:00 AM)
> Source Server
> Message
> ***Stack Dump being sent to D:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\LOG\SQLDump0008.txt
>
> --
> Regards,
> Jamie

Memory limited to 1.24 GB

HI,
My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu, 2 GB
of ram
The memory is configuring as dynamic, no max, no min
So, my question is :
Why the sql server use only 1.24 GB of ram et no 2 GB ?
Thanks a lot
Regards
Thierry
First off what are you using to determine the memory? DOn't use task
manager use Perfmon and the proper SQL Server memory counters. SQL Server
will only use memory if it needs to. It simply may not need all 2GB. Even
so with 2GB you likely to only see about 1.75GB's used for SQL Server
anyway.
Andrew J. Kelly SQL MVP
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
> HI,
> My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu, 2 GB
> of ram
> The memory is configuring as dynamic, no max, no min
> So, my question is :
> Why the sql server use only 1.24 GB of ram et no 2 GB ?
> Thanks a lot
> Regards
> Thierry
>
|||Thanks,
But I use SpotLigth to monitor my server and if I limit the sql server
memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
grow up to 1.24 GB never upper.
Why ?
Thanks
"Andrew J. Kelly" wrote:

> First off what are you using to determine the memory? DOn't use task
> manager use Perfmon and the proper SQL Server memory counters. SQL Server
> will only use memory if it needs to. It simply may not need all 2GB. Even
> so with 2GB you likely to only see about 1.75GB's used for SQL Server
> anyway.
> --
> Andrew J. Kelly SQL MVP
>
> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
> news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
>
>
|||Are you absolutely sure you have as much memory as you think? I don't know
for sure how Spotlight is measuring memory so I can't comment on that part.
My guess you have other apps runing on that server that take up a certain
amount of ram as well or SQL just does not need more than that.
Andrew J. Kelly SQL MVP
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...[vbcol=seagreen]
> Thanks,
> But I use SpotLigth to monitor my server and if I limit the sql server
> memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
> grow up to 1.24 GB never upper.
> Why ?
> Thanks
> "Andrew J. Kelly" wrote:
Server[vbcol=seagreen]
Even[vbcol=seagreen]
message[vbcol=seagreen]
2 GB[vbcol=seagreen]
|||Andrew J. Kelly wrote:[vbcol=seagreen]
> Are you absolutely sure you have as much memory as you think? I
> don't know for sure how Spotlight is measuring memory so I can't
> comment on that part. My guess you have other apps runing on that
> server that take up a certain amount of ram as well or SQL just does
> not need more than that.
>
> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in
> message news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...
I agree with Andrew. Whatever algorithm SQL Server uses to determine how
much system memory it can grab is probably coming up the 1.24GB number
on that particular server.
For a server, having 700MB or so dedicated to server functions doesn't
sound unreasonable.
David G.
|||use Perfmon / Process / Private Bytes to see if there are any other non-sql
processes using a large amount of memory.
cheers,
Andy.
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...[vbcol=seagreen]
> Thanks,
> But I use SpotLigth to monitor my server and if I limit the sql server
> memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
> grow up to 1.24 GB never upper.
> Why ?
> Thanks
> "Andrew J. Kelly" wrote:
Server[vbcol=seagreen]
Even[vbcol=seagreen]
message[vbcol=seagreen]
2 GB[vbcol=seagreen]
|||Hi,
Thanks a lot all for your response.
In fact, the server have 500 MB RAM free in all times, sqlserver reserved
1.24 GB and the other process reserved 250 MB.
Why ?
"Andy Ball" wrote:

> use Perfmon / Process / Private Bytes to see if there are any other non-sql
> processes using a large amount of memory.
> cheers,
> Andy.
> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
> news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...
> Server
> Even
> message
> 2 GB
>
>

Memory limited to 1.24 GB

HI,
My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu, 2 GB
of ram
The memory is configuring as dynamic, no max, no min
So, my question is :
Why the sql server use only 1.24 GB of ram et no 2 GB ?
Thanks a lot
Regards
ThierryFirst off what are you using to determine the memory? DOn't use task
manager use Perfmon and the proper SQL Server memory counters. SQL Server
will only use memory if it needs to. It simply may not need all 2GB. Even
so with 2GB you likely to only see about 1.75GB's used for SQL Server
anyway.
--
Andrew J. Kelly SQL MVP
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
> HI,
> My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu, 2 GB
> of ram
> The memory is configuring as dynamic, no max, no min
> So, my question is :
> Why the sql server use only 1.24 GB of ram et no 2 GB ?
> Thanks a lot
> Regards
> Thierry
>|||Are you absolutely sure you have as much memory as you think? I don't know
for sure how Spotlight is measuring memory so I can't comment on that part.
My guess you have other apps runing on that server that take up a certain
amount of ram as well or SQL just does not need more than that.
--
Andrew J. Kelly SQL MVP
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...
> Thanks,
> But I use SpotLigth to monitor my server and if I limit the sql server
> memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
> grow up to 1.24 GB never upper.
> Why ?
> Thanks
> "Andrew J. Kelly" wrote:
> > First off what are you using to determine the memory? DOn't use task
> > manager use Perfmon and the proper SQL Server memory counters. SQL
Server
> > will only use memory if it needs to. It simply may not need all 2GB.
Even
> > so with 2GB you likely to only see about 1.75GB's used for SQL Server
> > anyway.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in
message
> > news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
> > > HI,
> > >
> > > My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu,
2 GB
> > > of ram
> > > The memory is configuring as dynamic, no max, no min
> > >
> > > So, my question is :
> > >
> > > Why the sql server use only 1.24 GB of ram et no 2 GB ?
> > >
> > > Thanks a lot
> > >
> > > Regards
> > >
> > > Thierry
> > >
> >
> >
> >|||Andrew J. Kelly wrote:
> Are you absolutely sure you have as much memory as you think? I
> don't know for sure how Spotlight is measuring memory so I can't
> comment on that part. My guess you have other apps runing on that
> server that take up a certain amount of ram as well or SQL just does
> not need more than that.
>
> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in
> message news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...
>> Thanks,
>> But I use SpotLigth to monitor my server and if I limit the sql
>> server memory to 500 MB, It use 500 MB, afterv that I set no limit
>> and the memory grow up to 1.24 GB never upper.
>> Why ?
>> Thanks
>> "Andrew J. Kelly" wrote:
>> First off what are you using to determine the memory? DOn't use
>> task manager use Perfmon and the proper SQL Server memory counters.
>> SQL Server will only use memory if it needs to. It simply may not
>> need all 2GB. Even so with 2GB you likely to only see about
>> 1.75GB's used for SQL Server anyway.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in
>> message news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
>> HI,
>> My config is Windows 2000 entreprise, sqlserver 2000 standard, 2
>> cpu, 2 GB of ram
>> The memory is configuring as dynamic, no max, no min
>> So, my question is :
>> Why the sql server use only 1.24 GB of ram et no 2 GB ?
>> Thanks a lot
>> Regards
>> Thierry
I agree with Andrew. Whatever algorithm SQL Server uses to determine how
much system memory it can grab is probably coming up the 1.24GB number
on that particular server.
For a server, having 700MB or so dedicated to server functions doesn't
sound unreasonable.
David G.|||use Perfmon / Process / Private Bytes to see if there are any other non-sql
processes using a large amount of memory.
cheers,
Andy.
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...
> Thanks,
> But I use SpotLigth to monitor my server and if I limit the sql server
> memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
> grow up to 1.24 GB never upper.
> Why ?
> Thanks
> "Andrew J. Kelly" wrote:
> > First off what are you using to determine the memory? DOn't use task
> > manager use Perfmon and the proper SQL Server memory counters. SQL
Server
> > will only use memory if it needs to. It simply may not need all 2GB.
Even
> > so with 2GB you likely to only see about 1.75GB's used for SQL Server
> > anyway.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in
message
> > news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
> > > HI,
> > >
> > > My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu,
2 GB
> > > of ram
> > > The memory is configuring as dynamic, no max, no min
> > >
> > > So, my question is :
> > >
> > > Why the sql server use only 1.24 GB of ram et no 2 GB ?
> > >
> > > Thanks a lot
> > >
> > > Regards
> > >
> > > Thierry
> > >
> >
> >
> >|||Hi,
Thanks a lot all for your response.
In fact, the server have 500 MB RAM free in all times, sqlserver reserved
1.24 GB and the other process reserved 250 MB.
Why ?
"Andy Ball" wrote:
> use Perfmon / Process / Private Bytes to see if there are any other non-sql
> processes using a large amount of memory.
> cheers,
> Andy.
> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
> news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...
> > Thanks,
> >
> > But I use SpotLigth to monitor my server and if I limit the sql server
> > memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
> > grow up to 1.24 GB never upper.
> >
> > Why ?
> >
> > Thanks
> >
> > "Andrew J. Kelly" wrote:
> >
> > > First off what are you using to determine the memory? DOn't use task
> > > manager use Perfmon and the proper SQL Server memory counters. SQL
> Server
> > > will only use memory if it needs to. It simply may not need all 2GB.
> Even
> > > so with 2GB you likely to only see about 1.75GB's used for SQL Server
> > > anyway.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in
> message
> > > news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
> > > > HI,
> > > >
> > > > My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu,
> 2 GB
> > > > of ram
> > > > The memory is configuring as dynamic, no max, no min
> > > >
> > > > So, my question is :
> > > >
> > > > Why the sql server use only 1.24 GB of ram et no 2 GB ?
> > > >
> > > > Thanks a lot
> > > >
> > > > Regards
> > > >
> > > > Thierry
> > > >
> > >
> > >
> > >
>
>

Memory limited to 1.24 GB

HI,
My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu, 2 GB
of ram
The memory is configuring as dynamic, no max, no min
So, my question is :
Why the sql server use only 1.24 GB of ram et no 2 GB ?
Thanks a lot
Regards
ThierryFirst off what are you using to determine the memory? DOn't use task
manager use Perfmon and the proper SQL Server memory counters. SQL Server
will only use memory if it needs to. It simply may not need all 2GB. Even
so with 2GB you likely to only see about 1.75GB's used for SQL Server
anyway.
Andrew J. Kelly SQL MVP
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
> HI,
> My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu, 2 GB
> of ram
> The memory is configuring as dynamic, no max, no min
> So, my question is :
> Why the sql server use only 1.24 GB of ram et no 2 GB ?
> Thanks a lot
> Regards
> Thierry
>|||Thanks,
But I use SpotLigth to monitor my server and if I limit the sql server
memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
grow up to 1.24 GB never upper.
Why ?
Thanks
"Andrew J. Kelly" wrote:

> First off what are you using to determine the memory? DOn't use task
> manager use Perfmon and the proper SQL Server memory counters. SQL Server
> will only use memory if it needs to. It simply may not need all 2GB. Eve
n
> so with 2GB you likely to only see about 1.75GB's used for SQL Server
> anyway.
> --
> Andrew J. Kelly SQL MVP
>
> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in messag
e
> news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
>
>|||Are you absolutely sure you have as much memory as you think? I don't know
for sure how Spotlight is measuring memory so I can't comment on that part.
My guess you have other apps runing on that server that take up a certain
amount of ram as well or SQL just does not need more than that.
Andrew J. Kelly SQL MVP
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...[vbcol=seagreen]
> Thanks,
> But I use SpotLigth to monitor my server and if I limit the sql server
> memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
> grow up to 1.24 GB never upper.
> Why ?
> Thanks
> "Andrew J. Kelly" wrote:
>
Server[vbcol=seagreen]
Even[vbcol=seagreen]
message[vbcol=seagreen]
2 GB[vbcol=seagreen]|||Andrew J. Kelly wrote:[vbcol=seagreen]
> Are you absolutely sure you have as much memory as you think? I
> don't know for sure how Spotlight is measuring memory so I can't
> comment on that part. My guess you have other apps runing on that
> server that take up a certain amount of ram as well or SQL just does
> not need more than that.
>
> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in
> message news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...
I agree with Andrew. Whatever algorithm SQL Server uses to determine how
much system memory it can grab is probably coming up the 1.24GB number
on that particular server.
For a server, having 700MB or so dedicated to server functions doesn't
sound unreasonable.
David G.|||use Perfmon / Process / Private Bytes to see if there are any other non-sql
processes using a large amount of memory.
cheers,
Andy.
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...[vbcol=seagreen]
> Thanks,
> But I use SpotLigth to monitor my server and if I limit the sql server
> memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
> grow up to 1.24 GB never upper.
> Why ?
> Thanks
> "Andrew J. Kelly" wrote:
>
Server[vbcol=seagreen]
Even[vbcol=seagreen]
message[vbcol=seagreen]
2 GB[vbcol=seagreen]|||Hi,
Thanks a lot all for your response.
In fact, the server have 500 MB RAM free in all times, sqlserver reserved
1.24 GB and the other process reserved 250 MB.
Why ?
"Andy Ball" wrote:

> use Perfmon / Process / Private Bytes to see if there are any other non-sq
l
> processes using a large amount of memory.
> cheers,
> Andy.
> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in messag
e
> news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...
> Server
> Even
> message
> 2 GB
>
>