merge agent slowing performance

Hi,
I have setup merge replication with push subcription for every 10 min. I
have changed the agent profile to 'high Volume Server to server' but still
the duration and delivery rate remain almost the same. How can i increase the
delivery rate, or what should be the suggested agent profile be. Please help,
it is urget.
Thanks in Advance,
SVur
The history of the merge agent looks like this for hte last run:
# Actions : 32
Last action message: merged 3252 data changes(3209 inserts, 16 updates, 27
deletes)
Duration(secs): 00.00.43
Delivery rate (rows per sec): 73.0000
Inserts at Publisher:2912
Updates at Publisher:16
Deletes at publisher:27
conflicts at publisher:0
Inserts at subscriber:297
Updates at subscriber:0
Deletes at subscriber:0
conflicts at subscriber:0
Hi,
This is my merge agent history. the agent approximately took 9 min to merge
about 6500 changes in the last run. How can i improve the performance of the
merge agent? What are the implications of increasing the upload and download
batch size?
Thanks,
SVur
118/17/2004 8:20:18 AM8/17/2004 8:20:18 AM0Starting
agent.00000000000<Binary>1
118/17/2004 8:20:31 AM8/17/2004 8:20:31
AM0Initializing00000000000<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:20:31 AM0Connecting to Publisher
'01AL10015000025'00000000000<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:20:36 AM4Connecting to Subscriber
'01AL10015000026'00000000000<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:21:24 AM52Uploaded 104 data changes
(100 inserts, 0 updates, 4 deletes, 0
conflicts).18734200001000400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:21:24 AM52Uploaded 100 data changes
(100 inserts, 0 updates, 0 deletes, 0
conflicts).368903.92307692307600002000400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:21:26 AM55Uploaded 100 data changes
(100 inserts, 0 updates, 0 deletes, 0
conflicts).587185.52727272727200003000400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:21:29 AM57Uploaded 100 data changes
(100 inserts, 0 updates, 0 deletes, 0
conflicts).806247.08771929824500004000400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:21:47 AM76Uploaded 100 data changes
(100 inserts, 0 updates, 0 deletes, 0
conflicts).935936.63157894736800005000400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:21:51 AM80Uploaded 100 data changes
(100 inserts, 0 updates, 0 deletes, 0
conflicts).1079847.5500006000400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:21:54 AM82Uploaded 123 data changes
(123 inserts, 0 updates, 0 deletes, 0
conflicts).1247818.86585365853600007230400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:21:59 AM87Uploaded 104 data changes
(100 inserts, 4 updates, 0 deletes, 0
conflicts).1408599.55172413793100008234400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:22:11 AM99Uploaded 69 data changes
(69 inserts, 0 updates, 0 deletes, 0
conflicts).1408599.09090909090900008924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:22:14 AM102Downloaded 142 data
changes (0 inserts, 0 updates, 142 deletes, 0
conflicts).14151510.2156862745090014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:22:25 AM114Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).14289010.017543859649100014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:22:26 AM115Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).14406210.8200014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:22:27 AM116Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).14520311.568965517241300014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:22:28 AM117Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).14620312.324786324786400014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:22:42 AM130Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).14726511.861538461538500014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:22:43 AM132Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).14817112.439393939393600014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:22:45 AM133Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).14932713.097744360902700014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:22:46 AM134Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).15012413.746268656716800014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:22:59 AM147Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).15099913.210884353741900014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:23:00 AM149Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).15198413.7046979865771000014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:23:01 AM149Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).15295314.3758389261741100014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:23:01 AM150Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).15381314.9466666666661200014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:23:11 AM160Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).15471914.63751300014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:23:13 AM161Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).15553115.1677018633541400014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:23:14 AM163Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).15648515.5950920245391500014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:23:15 AM164Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).15743816.109756097561600014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:23:26 AM175Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).15822015.6685714285711700014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:23:27 AM175Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).15898616.241800014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:23:28 AM176Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).15981416.7159090909091900014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:23:30 AM178Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).16218917.0898876404492000014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:23:37 AM185Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).16348616.9837837837832100014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:23:38 AM186Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).16450217.4301075268812200014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:23:38 AM187Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).16558017.871657754012300014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:23:39 AM187Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).16650218.4064171122992400014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:23:52 AM200Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).16725217.712500014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:23:53 AM201Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).16809618.1194029850742600014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:23:54 AM203Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).16895618.4334975369452700014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:23:55 AM204Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).16978418.8333333333332800014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:24:06 AM215Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).17064318.334883720932900014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:24:07 AM215Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).17137818.83000014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:24:08 AM217Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).17242519.0875576036863100014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:24:09 AM217Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).17341019.5483870967743200014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:24:22 AM230Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).17445718.8782608695653300014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:24:23 AM231Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).17555019.2294372294373400014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:24:23 AM232Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).17664419.5775862068963500014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:24:24 AM232Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).17762920.0086206896553600014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:24:32 AM240Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).17847319.7583333333333700014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:24:33 AM241Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).17951920.0912863070533800014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:24:33 AM242Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).18041020.4214876033053900014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:24:34 AM242Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).18117620.8347107438014000014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:24:44 AM252Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).18258220.4047619047614100014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:24:44 AM252Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).18384820.8015873015874200014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:24:44 AM253Downloaded 115 data
changes (115 inserts, 0 updates, 0 deletes, 0
conflicts).18511421.1739130434784315014208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:24:45 AM253Downloaded 151 data
changes (100 inserts, 51 updates, 0 deletes, 0
conflicts).18634921.77075098814244155114208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:24:45 AM254Downloaded 100 data
changes (0 inserts, 100 updates, 0 deletes, 0
conflicts).18741122.07874015748441515114208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:24:46 AM255Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).18858222.38431372549451515114208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:24:46 AM255Downloaded 100 data
changes (99 inserts, 1 updates, 0 deletes, 0
conflicts).18972322.776470588235461415214208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:24:46 AM255Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).19091023.16862745098471415214208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:24:46 AM255Downloaded 100 data
changes (100 inserts, 0 updates, 0 deletes, 0
conflicts).19189423.560784313725481415214208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:24:48 AM256Downloaded 171 data
changes (170 inserts, 1 updates, 0 deletes, 0
conflicts).19278524.13671875498415314208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:24:48 AM256Downloaded 100 data
changes (98 inserts, 2 updates, 0 deletes, 0
conflicts).19373824.52734375508215514208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:24:48 AM257Downloaded 100 data
changes (99 inserts, 1 updates, 0 deletes, 0
conflicts).19475424.821011673151518115614208924400<Binary>0
138/17/2004 8:20:31 AM8/17/2004 8:24:49 AM257No data needed to be
merged.19475424.821011673151518115614208924400<Binary>0
128/17/2004 8:20:31 AM8/17/2004 8:24:49 AM257Merged 6379 data changes
(6073 inserts, 160 updates, 146 deletes, 0 resolved
conflicts).024.821011673151518115614208924400<Binary>1
"SVur" wrote:

> Hi,
> I have setup merge replication with push subcription for every 10 min. I
> have changed the agent profile to 'high Volume Server to server' but still
> the duration and delivery rate remain almost the same. How can i increase the
> delivery rate, or what should be the suggested agent profile be. Please help,
> it is urget.
> Thanks in Advance,
> SVur
> The history of the merge agent looks like this for hte last run:
> # Actions : 32
> Last action message: merged 3252 data changes(3209 inserts, 16 updates, 27
> deletes)
> Duration(secs): 00.00.43
> Delivery rate (rows per sec): 73.0000
> Inserts at Publisher:2912
> Updates at Publisher:16
> Deletes at publisher:27
> conflicts at publisher:0
> Inserts at subscriber:297
> Updates at subscriber:0
> Deletes at subscriber:0
> conflicts at subscriber:0
>

Memory usage - max setting

I am using SQL 8 Personal edition with sp2 applied. I set the max
server memory to 32MB and leave the min server memory at 0. When my
application starts hitting the database hard the memory usage reported
through task manager peaks between 41-42MB. I've stopped and restarted
the MSSQLserver service and checked that the running values are what I
set them to be. Does anybody have any ideas as to why the sqlservr.exe
would be utilizing more memory than the configured value?

This message was posted some years ago but nobody answered. Now I have
the same problem! Does anybody have an explanation?

Thanks a lot[posted and mailed, please reply in news]

Frank Esser (essfra@.gmx.de) writes:
> I am using SQL 8 Personal edition with sp2 applied. I set the max
> server memory to 32MB and leave the min server memory at 0. When my
> application starts hitting the database hard the memory usage reported
> through task manager peaks between 41-42MB. I've stopped and restarted
> the MSSQLserver service and checked that the running values are what I
> set them to be. Does anybody have any ideas as to why the sqlservr.exe
> would be utilizing more memory than the configured value?
> This message was posted some years ago but nobody answered. Now I have
> the same problem! Does anybody have an explanation?

Since SP3 is the latest service pack, you may want to start there and
see if it helps.

As far as I know, this should not occur, so you may have a memory leak.
Hm, are you using any extented stored procedures or calling any OLE
objects in process? I don't if these could take a tool.

I'll ping the people in our private MVP forum, to see if anyone has an
idea, but in the end the only way to resolve may be open a case with
Microsoft.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote:
> [posted and mailed, please reply in news]
> Frank Esser (essfra@.gmx.de) writes:
> > I am using SQL 8 Personal edition with sp2 applied. I set the max
> > server memory to 32MB and leave the min server memory at 0. When my
> > application starts hitting the database hard the memory usage reported
> > through task manager peaks between 41-42MB.

<snip
BOL refers to these settings controlling the buffer pool and doesn't mention
the full VM size reported by task manager. I'm not a SQL Server internals
expert, but if SQL Server is bumping against your max memory setting, I
would expect your VM size in Task Manager to report a higher number (thread
stacks, other memory allocators, etc, etc).

Craig|||essfra@.gmx.de (Frank Esser) wrote in message news:<18d2e86d.0402100734.27861260@.posting.google.com>...
> I am using SQL 8 Personal edition with sp2 applied. I set the max
> server memory to 32MB and leave the min server memory at 0. When my
> application starts hitting the database hard the memory usage reported
> through task manager peaks between 41-42MB. I've stopped and restarted
> the MSSQLserver service and checked that the running values are what I
> set them to be. Does anybody have any ideas as to why the sqlservr.exe
> would be utilizing more memory than the configured value?
> This message was posted some years ago but nobody answered. Now I have
> the same problem! Does anybody have an explanation?
> Thanks a lot

The buffer pool is sized on the lower of the amount of physical
memory, or user mode address space (2GB by default)-MemToLeave which
is (again by default) around 384MB. If max server memory is set then
it becomes the limit if it is lower still. So if a server has 1GB and
no limit the BPool will be 1 GB (or more accuratley 1GB of reserved
space - it's not committed until used). On a server with 1GB for
example and max server memory set to 100MB then the BPool is sized at
100MB as this is lower than the physical memory.This in no way
constrains the SQL process from consuming more than 100MB, it merely
limits the address space reserved for the BPool pages (and by
definition the amount of memory that can be committed for thos BPool
pages). To confirm the sizing of the BPool you can use Buffer
Manager\TargetPages and convert from 8k pages into MB.When the server
starts it works out the limit of the BPool, reserves the MemToLeave
area,reserves the BPool and then releases the MemToLeave area. Having
said that, MemToLeave can obviously be greater than 384 MB as on a
server with 1GB, there will be ~1GB of free space in the user mode
address space. So (I am getting to a point!) the max server memory
limits the maximum size of the buffer pool but it should be in no way
taken as a limit to how much memory SQL can commit from its process
address space (2GB by default). Clear as mud :-)

For more on SQL's memory architecture check out
http://msdn.microsoft.com/data/defa...ev_01262004.asp|||Thanks a lot for your answers but indeed I found an answer to my
problem:
SQL server does not stop at the max memory but uses a maximum of 1.5
times of the set max value. When I set the max server memory to 16MB
it will actually use exactly 24MB as maximum, shown in Windows Task
Manager.

It is explained in the online books:

Quote:
************
Note If you have installed and are running the Full-Text Search
support (Microsoft Search service, also known as MSSearch), then you
must set the max server memory option manually to leave enough memory
for the MSSearch service to run. The max server memory setting must be
adjusted in conjunction with the Windows NT 4.0 virtual memory size
such that the virtual memory remaining for Full-Text Search is 1.5
times the physical memory (excluding the virtual memory requirements
of the other services on the computer). Configure the SQL Server max
server memory option so that there is sufficient virtual memory left
to satisfy this Full-Text Search memory requirement. Total virtual
memory - (SQL Server maximum virtual memory + virtual memory
requirements of other services) >= 1.5 times the physical memory.
************

"Craig Kelly" <cnkelly@.worldnet.att.net> wrote in message news:<8ZAWb.8783$hR.245921@.bgtnsc05-news.ops.worldnet.att.net>...
> "Erland Sommarskog" <sommar@.algonet.se> wrote:
> > [posted and mailed, please reply in news]
> > Frank Esser (essfra@.gmx.de) writes:
> > > I am using SQL 8 Personal edition with sp2 applied. I set the max
> > > server memory to 32MB and leave the min server memory at 0. When my
> > > application starts hitting the database hard the memory usage reported
> > > through task manager peaks between 41-42MB.
> <snip>
> BOL refers to these settings controlling the buffer pool and doesn't mention
> the full VM size reported by task manager. I'm not a SQL Server internals
> expert, but if SQL Server is bumping against your max memory setting, I
> would expect your VM size in Task Manager to report a higher number (thread
> stacks, other memory allocators, etc, etc).
> Craig

Memory Stress Test in SQL Server 2005

I have a Windows 2003 Server running SQL 2005. The server has 32 GB of memory and I have enabled AWE in SQL. I have also configured the min and max SQL memory as 1 GB and 28 GB, respectively. However, this server currently has very low activity so I'm not sure whether my AWE-related changes worked. SQLSERVR.EXE process takes up about 100 MB of memory. Is there any tool or scripts that I can use to memory stress SQL to confirm that AWE is really in effect ?

The are several ways. The easiest is to check the errrolog and look for a message like "Address Windowing Extensions is enabled. This is an informational message only; no user action is required.". This entry in MSDN is also relevant: http://msdn2.microsoft.com/en-us/library/ms190673.aspx

The following links may help you understand SQL Server memory usage:

http://msdn2.microsoft.com/en-us/library/ms176018.aspx (SQL Server 2005 monitoring memory usage)

http://support.microsoft.com/kb/907877 (DBCC memorystatus)

http://blogs.msdn.com/slavao/archive/tags/SQL+Server+Memory+Management/default.aspx (Slava's blog)

Thanks, Ron D.

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

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 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
> > > >
> > >
> > >
> > >
>
>