Merge Agent System Stored Procedures and sp_Recompile

Hello,

We are trying to be proactive and stop a potential performance issue by reducing the number of recompiles in our SQL 2000 database application. This database is replicated. After viewing output from Profiler and PerfMon it seems that over 90% of the recompiles are due to system stored procedures generated by replication merge agents. Can anything be done about this?

Thanks

Can you be more specific - which procs, and under what conditions?|||The databases are setup to use push merge replication with the polling interval set to every 15 seconds. It is replicating 4 databases to one subscriber. The procs that are involved are the ones generated by the merge agent when you set up replication. Hope this helps!|||Thanks Candice, but merge replication involves many many procs, including triggers. Can you please be more specific about which ones? And is this occuring at the publisher, or subscriber?

Menu System

Hi,
Has anybody come across a good menu system for MSRS? We could write our own
but are perfectly willing to look at a packaged product. Thanks for your
help.
CBHere is the code for a simple, yet customizable, dotnet web form treeview
style menu. Paste it into notepad and give it an aspx extension, copy it to
your web server and run it from your browser.
A couple of things first:
1. you need to create a reporting services web proxy and reference it by its
namespace. Change the namespace in the line of code {<%@. import
Namespace="Your RS Proxy Name" %>} below.
2. The line in the code specifying the web server for a report path
{newNode.NavigateURL = "http://Your Report Server/reportserver?" & ci.Path}
has to be changed to point to your web server where reporting services is
installed and the name of the installation. If you took the defaults during
the installation it should be http://Your Report Server/reportserver?
3. you have to download and install the MS web controls. They can be gotten
for free at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspp/html/aspnet-usingtreeviewiewebcontrol.asp
The site has instructions for installing and using the web controls, plus
there are plenty of threads about them on the google news groups pertaining
to customization, look and feel, etc...
4. Finally, this was create in web matrix, another free download from MS.
The difference between web matrix and VS (aside from a lot of funtionality)
is web matrix doesn't use code-behind, it simply places the code between
script tags. But then again, you can use notepad too. Web matrix can be
gotten at http://www.asp.net/WebMatrix/
good luck!
************************* CODE BEGINS
HERE***********************************
<%@. Page Language="VB" Debug="true" AspCompat="true" %>
<%@. Register TagPrefix="myTree" Namespace="Microsoft.Web.UI.WebControls"
Assembly="Microsoft.Web.UI.WebControls" %>
<%@. import Namespace="System" %>
<%@. import Namespace="System.IO" %>
<%@. import Namespace="System.Data" %>
<%@. import Namespace="System.Web.Services" %>
<%@. import Namespace="System.Web.Services.Protocols" %>
<%@. import Namespace="Your RS Proxy Name" %>
<%@. import Namespace="System.Text" %>
<script runat="server">
Sub Page_Load(Sender As Object, E As EventArgs)
if not ispostback then
PopulateMenu()
end if
end sub
sub doThis(Sender As Object, E as
Microsoft.Web.UI.WebControls.TreeViewSelectEventArgs)
dim currentNode as
TreeNode=CatalogTreeView.GetNodeFromIndex(e.NewNode)
end sub
sub AddChildNodes(currentNode as TreeNode)
Dim impersonationContext As
System.Security.Principal.WindowsImpersonationContext
Dim currentWindowsIdentity As
System.Security.Principal.WindowsIdentity
currentWindowsIdentity = CType(User.Identity,
System.Security.Principal.WindowsIdentity)
impersonationContext = currentWindowsIdentity.Impersonate()
Dim rs As New ReportingService()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
currentNode.Expanded = true
Dim items As CatalogItem() = Nothing
' Retrieve a list of all items from the report server
database.
if currentNode.Type="Folder" then
items = rs.ListChildren(currentNode.ID, false)
end if
if currentNode.Type="Folder" then
dim ci as CatalogItem
for each ci in items
dim type as ItemTypeEnum= ci.Type
if ci.Type.ToString()="Report" then
dim newNode as TreeNode= new TreeNode()
newNode.Text = ci.Name
newNode.ID = ci.Path
newNode.Type = type.ToString()
newNode.HoverStyle.CssText
="font-size:8pt;color:dimgray;font-family:MS Sans Serif;font-weight:bold;"
newNode.DEFAULTSTYLE.CssText="font:MS Sans Serif;
color:Indigo; background:white;font-size:8pt"
newNode.NavigateURL = "http://Your Report
Server/reportserver?" & ci.Path
newNode.Expandable = ExpandableValue.Auto
currentNode.Nodes.Add(newNode)
else 'if it is a folder
dim newNode as TreeNode= new TreeNode()
newNode.Text = ci.Name
newNode.ID = ci.Path
newNode.Type = ItemTypeEnum.Folder.ToString()
newNode.Expanded = false
newNode.HoverStyle.CssText
="font-size:8pt;color:dimgray;font-family:MS Sans Serif;font-weight:bold;"
newNode.DEFAULTSTYLE.CssText="font:MS Sans Serif;
color:Indigo; background:white;font-size:8pt"
newNode.Expandable = ExpandableValue.CheckOnce
currentNode.Nodes.Add(newNode)
end if
next ci
end if
impersonationContext.Undo()
end sub
'Then for the Expand event, something like:
sub CatalogTreeView_Expand(Sender As Object, E as
Microsoft.Web.UI.WebControls.TreeViewClickEventArgs)
dim node as TreeNode=CatalogTreeView.GetNodeFromIndex(e.Node)
' Add the children of the node
node.Nodes.Clear()
AddChildNodes(node)
end sub
sub PopulateMenu()
Dim impersonationContext As
System.Security.Principal.WindowsImpersonationContext
Dim currentWindowsIdentity As
System.Security.Principal.WindowsIdentity
currentWindowsIdentity = CType(User.Identity,
System.Security.Principal.WindowsIdentity)
impersonationContext = currentWindowsIdentity.Impersonate()
Dim rs As New ReportingService()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim items As CatalogItem() = Nothing
items = rs.ListChildren("/", true)
dim ci as CatalogItem
for each ci in items
if ci.Type.ToString()="Folder" then
dim root as TreeNode= new TreeNode()
root.Text = ci.Name
root.ID = ci.Path
root.Expanded = false
root.Type = ItemTypeEnum.Folder.ToString()
root.Expandable = ExpandableValue.CheckOnce
root.SelectedStyle.CssText="background:white;color:Indigo;font-name:Arial;fo
nt-weight:bold;font-size:8pt;"
root.HoverStyle.CssText
="font-size:8pt;color:indigo;font-family:MS Sans Serif;font-weight:bold;"
root.DEFAULTSTYLE.CssText="font:arial; color:DimGray;
background:white;font-size:8pt"
root.Expandable = ExpandableValue.CheckOnce
CatalogTreeView.Nodes.Add(root)
end if
next ci
impersonationContext.Undo()
end sub
</script>
<html>
<head>
</head>
<body>
<form font-size="XX-Small" font-names="MS Sans Serif" runat="server">
<MYTREE:TREEVIEW id="CatalogTreeView" runat="server"
onselectedindexchange="doThis" onexpand="CatalogTreeView_Expand"
autopostback="true" SHOWLINES="false" SHOWPLUS="true" SelectExpands="true"
DEFAULTSTYLE="font:arial;color:DimGray;background:white;font-size:8pt;text-d
ecoration:underline;"></MYTREE:TREEVIEW>
</form>
</body>
</html>
"Chuck Burns" <chuckb@.180solutions.com> wrote in message
news:%239fNrp1bEHA.3864@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Has anybody come across a good menu system for MSRS? We could write our
own
> but are perfectly willing to look at a packaged product. Thanks for your
> help.
> CB
>

Memory, Available Bytes

I'm using a performance console system monitor to observe memory performance
of a Win 2K server, running 2K SQL server.
The available bytes indicates less than half of the system's total physical
memory of
4 gig. Could this be a possible indication of a memory leak. What, if any
possible
solutions are available. Thanks to everyone for being there to help.Hi
If the Server has 4GB RAM, and is Enterprise Edition of Windows 2000, is the
/3GB flag set in boot.ini ?
If not, only 2GB of the possible 3GB is presented to applications. The other
1GB is always reserved for the OS.
If it is Standard Edition of Windows 2000, supported is only 2GB of the 4GB.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"coenzyme" wrote:

> I'm using a performance console system monitor to observe memory performan
ce
> of a Win 2K server, running 2K SQL server.
> The available bytes indicates less than half of the system's total physica
l
> memory of
> 4 gig. Could this be a possible indication of a memory leak. What, if any
> possible
> solutions are available. Thanks to everyone for being there to help.

Memory, Available Bytes

I'm using a performance console system monitor to observe memory performance
of a Win 2K server, running 2K SQL server.
The available bytes indicates less than half of the system's total physical
memory of
4 gig. Could this be a possible indication of a memory leak. What, if any
possible
solutions are available. Thanks to everyone for being there to help.
Hi
If the Server has 4GB RAM, and is Enterprise Edition of Windows 2000, is the
/3GB flag set in boot.ini ?
If not, only 2GB of the possible 3GB is presented to applications. The other
1GB is always reserved for the OS.
If it is Standard Edition of Windows 2000, supported is only 2GB of the 4GB.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"coenzyme" wrote:

> I'm using a performance console system monitor to observe memory performance
> of a Win 2K server, running 2K SQL server.
> The available bytes indicates less than half of the system's total physical
> memory of
> 4 gig. Could this be a possible indication of a memory leak. What, if any
> possible
> solutions are available. Thanks to everyone for being there to help.

Memory, Available Bytes

I'm using a performance console system monitor to observe memory performance
of a Win 2K server, running 2K SQL server.
The available bytes indicates less than half of the system's total physical
memory of
4 gig. Could this be a possible indication of a memory leak. What, if any
possible
solutions are available. Thanks to everyone for being there to help.Hi
If the Server has 4GB RAM, and is Enterprise Edition of Windows 2000, is the
/3GB flag set in boot.ini ?
If not, only 2GB of the possible 3GB is presented to applications. The other
1GB is always reserved for the OS.
If it is Standard Edition of Windows 2000, supported is only 2GB of the 4GB.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"coenzyme" wrote:
> I'm using a performance console system monitor to observe memory performance
> of a Win 2K server, running 2K SQL server.
> The available bytes indicates less than half of the system's total physical
> memory of
> 4 gig. Could this be a possible indication of a memory leak. What, if any
> possible
> solutions are available. Thanks to everyone for being there to help.sql

memory usage of sqlservr.exe

Hi,

Can we limit the memory usage of sqlservr.exe ?
I am using MSDE to run a 24x7 system and the sqlservr.exe takes around 200,000K (as shown in task manager). My concern is whether such usage would have adverse effect on other processes.

The system configuration is:
PIII-700MHz, 256MB RAM
MSDE 2000 over Win NT 4.0 (SP6)
Thanks in advance.

regards,
henryThere is a way to limit how much memory sql server can use in a given machine. But judging from the hardware configuration of your server, the machine is barely enough to run sql 2000. I don't know how much resource you need to run your database, but since you've mentioned 'other processes' in the box I would think that box has other things running. The slowdown could very well be caused by reaching the limit of your hardware capacities.

To set a fixed amount of memory

Expand a server group.

Right-click a server, and then click Properties.

Click the Memory tab.

Click Use a fixed memory size (MB), and then position the fixed memory slider.|||Hi Joe!

Thaks for your valuable input.

Yes, there is are several other exes running alongwith the msde.
Actually, i have not noticed slowdown in any process. But due to the varying memory consumption of the sqlmangr.exe, i am wondering whether the fixed memory option should be applied.

Please advise.

Thanks and regards,
henry|||The memory usage you see in Task Manager is simple the max of what you give to sql. SQL will allocate as much memory as you define in the Max Memory property. The actual usage of memory in sql is different. If you want to see the actual usage of memory by sql, run the performance monitor. If you don't see any slowdown, you can give it a try to lower the max memory.|||Hi!

I used the Performance monitor and compared the Private bytes with Dynamic memory and with fixed memory.
Except for the Private bytes and working set which get restricted in fixed memory case, i could not differentiate between the two options.
On reading the SQL online books, i found that the default dynamic setting is best for the system and i think i shall keep the setting as it is.

Thanks and regards,
henry|||Probably sounds stupid, but still, wouldn't hurt restarting that NT server once a week, even if it needs to be up 24x7. Could help improve overall performance. Generally they do this during Sunday night, when server usage is reduced.

Best regards!

memory usage and configuration...

How much memory is in your system in total, and what is
your drive configuration beyond the 6 disk RAID 5 set for
SQL Server? That may not be enough spindles, but I can't
say for sure. If you stick all of your SQL Server files
(system DBs, data/index, logs, tempdb) all on the same
stripe set, you are not buying yourself anything.
So if your goal as I saw is to put everything on the one
R5 stripe, I'd say wholeheartedly no, it is not an optimal
setup at all based on my experience.
However, you need to have done some system profiling to
tell you definitively.
I've 4gb of RAM
I want to control the usage of my hard drive to keep a good performance.
it's why I want to separate the tempdb database in 1 disk or 2 stripped
disks.
And I'll keep 4 disks in raid 5 for my data. for security reasons.
Maybe I can put my indexes on a separate disk.
Config 1:
Disk0: OS + Applications (10Gb only)
Disk0,1: Stripped - 22Gb + 32 Gb - tempdb - user db indexes - Swap file
Disk3,4,5,6: Raid 5 for system DB and user DB
Config 2:
Disk0: OS + Swap + Applications (10Gb only)
Disk0: tempdb (22gb)
Disk1: indexes (32gb)
Disk3,4,5,6: Raid 5 for system DB and user DB
each disk is a 32gb disk
what do you think about this?
which config is better?
or what is your recommendation?
Also, what is the swap file size required?
"Allan Hirt" <anonymous@.discussions.microsoft.com> a crit dans le message
de news:5ab501c474a8$a67500b0$a601280a@.phx.gbl...
> How much memory is in your system in total, and what is
> your drive configuration beyond the 6 disk RAID 5 set for
> SQL Server? That may not be enough spindles, but I can't
> say for sure. If you stick all of your SQL Server files
> (system DBs, data/index, logs, tempdb) all on the same
> stripe set, you are not buying yourself anything.
> So if your goal as I saw is to put everything on the one
> R5 stripe, I'd say wholeheartedly no, it is not an optimal
> setup at all based on my experience.
> However, you need to have done some system profiling to
> tell you definitively.

memory usage and configuration...

How much memory is in your system in total, and what is
your drive configuration beyond the 6 disk RAID 5 set for
SQL Server? That may not be enough spindles, but I can't
say for sure. If you stick all of your SQL Server files
(system DBs, data/index, logs, tempdb) all on the same
stripe set, you are not buying yourself anything.
So if your goal as I saw is to put everything on the one
R5 stripe, I'd say wholeheartedly no, it is not an optimal
setup at all based on my experience.
However, you need to have done some system profiling to
tell you definitively.I've 4gb of RAM
I want to control the usage of my hard drive to keep a good performance.
it's why I want to separate the tempdb database in 1 disk or 2 stripped
disks.
And I'll keep 4 disks in raid 5 for my data. for security reasons.
Maybe I can put my indexes on a separate disk.
Config 1:
Disk0: OS + Applications (10Gb only)
Disk0,1: Stripped - 22Gb + 32 Gb - tempdb - user db indexes - Swap file
Disk3,4,5,6: Raid 5 for system DB and user DB
Config 2:
Disk0: OS + Swap + Applications (10Gb only)
Disk0: tempdb (22gb)
Disk1: indexes (32gb)
Disk3,4,5,6: Raid 5 for system DB and user DB
each disk is a 32gb disk
what do you think about this?
which config is better?
or what is your recommendation?
Also, what is the swap file size required?
"Allan Hirt" <anonymous@.discussions.microsoft.com> a crit dans le message
de news:5ab501c474a8$a67500b0$a601280a@.phx.gbl...
> How much memory is in your system in total, and what is
> your drive configuration beyond the 6 disk RAID 5 set for
> SQL Server? That may not be enough spindles, but I can't
> say for sure. If you stick all of your SQL Server files
> (system DBs, data/index, logs, tempdb) all on the same
> stripe set, you are not buying yourself anything.
> So if your goal as I saw is to put everything on the one
> R5 stripe, I'd say wholeheartedly no, it is not an optimal
> setup at all based on my experience.
> However, you need to have done some system profiling to
> tell you definitively.

Memory Usage

How much memory can SQL Server 2000 Enterprise utilize when installed on an
x64 system with Windows Server 2003 Enterprise 64 bit?
Thanks in advance
Dan
512Gb on a 64bits system, but I don't know if this limit is for SQL 64bits +
Windows 64 Bits or SQL 32bits on Win 64Bits
the limit on a 32Bits system is 64Gb
read this:
http://download.microsoft.com/downlo...45/ChoosEd.doc
"DanR" <DanR@.discussions.microsoft.com> wrote in message
news:16491266-A2ED-4850-8592-096EA5828A4B@.microsoft.com...
> How much memory can SQL Server 2000 Enterprise utilize when installed on
> an
> x64 system with Windows Server 2003 Enterprise 64 bit?
> Thanks in advance
> Dan

Memory Usage

How much memory can SQL Server 2000 Enterprise utilize when installed on an
x64 system with Windows Server 2003 Enterprise 64 bit?
Thanks in advance
Dan512Gb on a 64bits system, but I don't know if this limit is for SQL 64bits +
Windows 64 Bits or SQL 32bits on Win 64Bits
the limit on a 32Bits system is 64Gb
read this:
ChoosEd.doc" target="_blank">http://download.microsoft.com/downl.../>
ChoosEd.doc
"DanR" <DanR@.discussions.microsoft.com> wrote in message
news:16491266-A2ED-4850-8592-096EA5828A4B@.microsoft.com...
> How much memory can SQL Server 2000 Enterprise utilize when installed on
> an
> x64 system with Windows Server 2003 Enterprise 64 bit?
> Thanks in advance
> Dan

Memory usage

I was on my friend's server earlier and I checked his system stats. Between sqlservr.exe and msftesql.exe he was using 2.9gb of ram. This is not a high traffic server so I felt that was just a wee bit high. What are some usage experiences you have seen? I have not worked with 2005 so I don't know what the usage should be like for a low to medium usage server. The guy that admins the db is kinda a moron anyway. He said he has said to the server admin it has been optimized, but I dont believe it one bit at 2.9gb of usage. Any comments would help.

PeteThis is a pretty common question from people who do not administer SQL Servers or even Exchange servers for that matter. SQL Server is designed to take up memory and not release it unless something else needs it. SQL Server will allow 4 - 10 MB of physical memory for other processes. If this is a dedicated server, it is nothing to worry about. He is actually getting something use out of all that memory.|||I'm sure for a server that needed that much use out of its memory it would be good, but for a server that has maybe 20 sites on it and a probably 8gb of traffic a month for sites that use the sql server 2.9gb is unacceptable to me. I have mysql running along side it which I admin and it is only using a quarter of what sql server is using.|||If the box is paging memory, then you can put a cap on SQL Server's consumption of memory. If not, I am not sure why you would bother.

Memory Usage

One of my friend's question:
"When I get a big result by a query (such as 1M of rows), the memory usage
of my system decreasing critically. What is the phenomenon that causes this
behaviour?"
Ok it is usual, because I can still observe the rows in the result pane
below. But, Although I close the pane, the mem.usage is still high.
I think it occurs because of the cached result in order to respond later
requests in a fast manner.
Can you please make a comment about the subject?
AND, I would like to learn if there is a way to release/flush this memory by
the Query Analyzer.That happens by design. It's not a flaw or memory leakage.
It is really not the Query Analyser that is "eating the memory", but
instead SQL Server that is behaving according to the Memory settings
established on Enterprise Manager.
Open EM, right click the server registration you wish to check and
click Properties. Move to the memory tab. It is recommended that the
server is configured to dynamically allocate memory.
SQL Server will use as much memory as it can. As other process may
start requesting memory, it will release it. Untill then, it will keep
his own memory usage. With queries being run on the Query Analyser, the
memory usage will keep increasing just short of having to start paging.
I know of no way to programatically release this memory. However, I
don't think this is an issue, since any other process that needs memory
will have it released by SQL Server.

Memory Usage

How much memory can SQL Server 2000 Enterprise utilize when installed on an
x64 system with Windows Server 2003 Enterprise 64 bit?
Thanks in advance
Dan512Gb on a 64bits system, but I don't know if this limit is for SQL 64bits +
Windows 64 Bits or SQL 32bits on Win 64Bits
the limit on a 32Bits system is 64Gb
read this:
http://download.microsoft.com/download/8/1/7/817bb6e6-9d97-4a5d-be8d-508256ae4045/ChoosEd.doc
"DanR" <DanR@.discussions.microsoft.com> wrote in message
news:16491266-A2ED-4850-8592-096EA5828A4B@.microsoft.com...
> How much memory can SQL Server 2000 Enterprise utilize when installed on
> an
> x64 system with Windows Server 2003 Enterprise 64 bit?
> Thanks in advance
> Dan

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 question day..

SQL Server Enterprise edition 2000 sp4+.2187, 32 bit
Windows Server 2003 R2, Enterprise, 64bit
40gb ram in the system, no other apps.
I'm looking for the optimal settings for this box with regards to Min/Max,
AWE, Boot.ini file etc.
Sp_configure shows that whoever built this box set AWE =1, min and max -
33000000 (32.6gb)
The properties of the server, memory tab show the memory fixed at the full
40gb
perfmon shows the SQL memory Manager:total server memory counter at
39973432kb (38gb).
Not sure which GUI to trust here.
Thanks!
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available./pae in boot.ini?
Set min memory a gig or so less than max.
watch pages/sec in perfmon occassionally to ensure not paging.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:%23lIXDm4XIHA.6140@.TK2MSFTNGP02.phx.gbl...
> SQL Server Enterprise edition 2000 sp4+.2187, 32 bit
> Windows Server 2003 R2, Enterprise, 64bit
> 40gb ram in the system, no other apps.
> I'm looking for the optimal settings for this box with regards to Min/Max,
> AWE, Boot.ini file etc.
> Sp_configure shows that whoever built this box set AWE =1, min and max -
> 33000000 (32.6gb)
> The properties of the server, memory tab show the memory fixed at the full
> 40gb
> perfmon shows the SQL memory Manager:total server memory counter at
> 39973432kb (38gb).
> Not sure which GUI to trust here.
> Thanks!
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
>|||In this situation...would you set max at the total ram on the box? Seems
like you should specifically allow some room for the O/S...but I'm just not
all that familiar with 64 bit architecture
there are two lines in the boot.ini (alternate startup options)...one with
/pae, one without. Anyway to tell which was used? The one without is the
default
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13pkgobj171ir65@.corp.supernews.com...
> /pae in boot.ini?
> Set min memory a gig or so less than max.
> watch pages/sec in perfmon occassionally to ensure not paging.
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
> news:%23lIXDm4XIHA.6140@.TK2MSFTNGP02.phx.gbl...
>> SQL Server Enterprise edition 2000 sp4+.2187, 32 bit
>> Windows Server 2003 R2, Enterprise, 64bit
>> 40gb ram in the system, no other apps.
>> I'm looking for the optimal settings for this box with regards to
>> Min/Max, AWE, Boot.ini file etc.
>> Sp_configure shows that whoever built this box set AWE =1, min and max -
>> 33000000 (32.6gb)
>> The properties of the server, memory tab show the memory fixed at the
>> full 40gb
>> perfmon shows the SQL memory Manager:total server memory counter at
>> 39973432kb (38gb).
>> Not sure which GUI to trust here.
>> Thanks!
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>>
>|||Note that the numbers for max/min server memory are in MB, not in KB.
> Sp_configure shows that whoever built this box set AWE =1, min and max -
> 33000000 (32.6gb)
So you are setting max/min server memory to ~33,000GB, which basically
leaves it up to the SQL Server instance to grab as much memory as it needs
minus some for the OS. On a box like this, in most cases, I'd just leave 4GB
for the OS and set the rest for max/min server memory.
Linchi
"Kevin3NF" wrote:
> SQL Server Enterprise edition 2000 sp4+.2187, 32 bit
> Windows Server 2003 R2, Enterprise, 64bit
> 40gb ram in the system, no other apps.
> I'm looking for the optimal settings for this box with regards to Min/Max,
> AWE, Boot.ini file etc.
> Sp_configure shows that whoever built this box set AWE =1, min and max -
> 33000000 (32.6gb)
> The properties of the server, memory tab show the memory fixed at the full
> 40gb
> perfmon shows the SQL memory Manager:total server memory counter at
> 39973432kb (38gb).
> Not sure which GUI to trust here.
> Thanks!
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
>
>|||:) I didn't build this box, and just noticed that someone set it to
32TB...too funny.
Nice catch, thanks. We had just set it to 36gb for min/max, with AWE
enabled.
Is /PAE necessary for 32 bit SQL on 64 bit Windows? I'm thinking yes.
Thanks,
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:F867817C-8307-4257-911B-ED5B9D51A5EC@.microsoft.com...
> Note that the numbers for max/min server memory are in MB, not in KB.
>> Sp_configure shows that whoever built this box set AWE =1, min and max -
>> 33000000 (32.6gb)
> So you are setting max/min server memory to ~33,000GB, which basically
> leaves it up to the SQL Server instance to grab as much memory as it needs
> minus some for the OS. On a box like this, in most cases, I'd just leave
> 4GB
> for the OS and set the rest for max/min server memory.
> Linchi
> "Kevin3NF" wrote:
>> SQL Server Enterprise edition 2000 sp4+.2187, 32 bit
>> Windows Server 2003 R2, Enterprise, 64bit
>> 40gb ram in the system, no other apps.
>> I'm looking for the optimal settings for this box with regards to
>> Min/Max,
>> AWE, Boot.ini file etc.
>> Sp_configure shows that whoever built this box set AWE =1, min and max -
>> 33000000 (32.6gb)
>> The properties of the server, memory tab show the memory fixed at the
>> full
>> 40gb
>> perfmon shows the SQL memory Manager:total server memory counter at
>> 39973432kb (38gb).
>> Not sure which GUI to trust here.
>> Thanks!
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>>|||> Is /PAE necessary for 32 bit SQL on 64 bit Windows? I'm thinking yes.
No need for /PAE.
Linchi
"Kevin3NF" wrote:
> :) I didn't build this box, and just noticed that someone set it to
> 32TB...too funny.
> Nice catch, thanks. We had just set it to 36gb for min/max, with AWE
> enabled.
> Is /PAE necessary for 32 bit SQL on 64 bit Windows? I'm thinking yes.
> Thanks,
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:F867817C-8307-4257-911B-ED5B9D51A5EC@.microsoft.com...
> > Note that the numbers for max/min server memory are in MB, not in KB.
> >
> >> Sp_configure shows that whoever built this box set AWE =1, min and max -
> >> 33000000 (32.6gb)
> >
> > So you are setting max/min server memory to ~33,000GB, which basically
> > leaves it up to the SQL Server instance to grab as much memory as it needs
> > minus some for the OS. On a box like this, in most cases, I'd just leave
> > 4GB
> > for the OS and set the rest for max/min server memory.
> >
> > Linchi
> >
> > "Kevin3NF" wrote:
> >
> >> SQL Server Enterprise edition 2000 sp4+.2187, 32 bit
> >> Windows Server 2003 R2, Enterprise, 64bit
> >> 40gb ram in the system, no other apps.
> >>
> >> I'm looking for the optimal settings for this box with regards to
> >> Min/Max,
> >> AWE, Boot.ini file etc.
> >>
> >> Sp_configure shows that whoever built this box set AWE =1, min and max -
> >> 33000000 (32.6gb)
> >>
> >> The properties of the server, memory tab show the memory fixed at the
> >> full
> >> 40gb
> >>
> >> perfmon shows the SQL memory Manager:total server memory counter at
> >> 39973432kb (38gb).
> >>
> >> Not sure which GUI to trust here.
> >>
> >> Thanks!
> >>
> >> --
> >>
> >> Kevin3NF
> >> SQL Server dude
> >>
> >> You want fries with that?
> >> http://kevin3nf.blogspot.com/
> >>
> >> I only check the newsgroups during work hours, M-F.
> >> Hit my blog and the contact links if necessary...I may be available.
> >>
> >>
> >>
> >>
> >>
>
>|||/PAE is an operating system flag, that changes the amount of directly
addressable memory for a 32-bit OS, for all applications. It has nothing
specific to do with SQL Server. So if Windows is 64-bit, there is no need
for /PAE.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:eFkqEi5XIHA.5980@.TK2MSFTNGP04.phx.gbl...
> :) I didn't build this box, and just noticed that someone set it to
> 32TB...too funny.
> Nice catch, thanks. We had just set it to 36gb for min/max, with AWE
> enabled.
> Is /PAE necessary for 32 bit SQL on 64 bit Windows? I'm thinking yes.
> Thanks,
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:F867817C-8307-4257-911B-ED5B9D51A5EC@.microsoft.com...
>> Note that the numbers for max/min server memory are in MB, not in KB.
>> Sp_configure shows that whoever built this box set AWE =1, min and max -
>> 33000000 (32.6gb)
>> So you are setting max/min server memory to ~33,000GB, which basically
>> leaves it up to the SQL Server instance to grab as much memory as it
>> needs
>> minus some for the OS. On a box like this, in most cases, I'd just leave
>> 4GB
>> for the OS and set the rest for max/min server memory.
>> Linchi
>> "Kevin3NF" wrote:
>> SQL Server Enterprise edition 2000 sp4+.2187, 32 bit
>> Windows Server 2003 R2, Enterprise, 64bit
>> 40gb ram in the system, no other apps.
>> I'm looking for the optimal settings for this box with regards to
>> Min/Max,
>> AWE, Boot.ini file etc.
>> Sp_configure shows that whoever built this box set AWE =1, min and max -
>> 33000000 (32.6gb)
>> The properties of the server, memory tab show the memory fixed at the
>> full
>> 40gb
>> perfmon shows the SQL memory Manager:total server memory counter at
>> 39973432kb (38gb).
>> Not sure which GUI to trust here.
>> Thanks!
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>>
>

Memory More than 2G ==> 3GB switch

All,
We have a dell system with 2 processors(4 by the technique of
hyperthreading) and 4 GB of RAM.
This system is fully dedicated to SQL 2000 sp 3a, hotfix 818.
I have added the /3GB switch into the boot.ini and set the memory of
SQL fixed to 3043 MB RAM.
The problem is the following :
I think that my server isn't using the 3 Gig of Memory for
applications because of 2 reasons
- the error initdata: Warning: Could not set working set size to
1353312 KB.
- sqlserveragent agentlog [310] 4 processor(s) and 2048 MB RAM
detected
I know that sqlserveragent is not the same as sqlserver but I expect
then that the sqlserveragent should detect 1GB or 3GB - 3 is most
likely- and not 2 GB.
2 GB should be normal if the /3GB switch isn't used.
Question:
How can i really check if the 3 GB switch is activated and working.
I have found a document at microsoft but that link doesnt clarify
because on a server and on an enterprise edition the pagedpoolsize is
the same .
http://www.microsoft.com/resources/d..._4gt_tools.asp
Hi
This feature will work on Windows 2000 DataCenter or Advenced Server
machines. Do you have SQL Server installed on one of them?
<romaric_decoene@.yahoo.com> wrote in message
news:e4d6707e.0405240241.706d25f1@.posting.google.c om...
> All,
> We have a dell system with 2 processors(4 by the technique of
> hyperthreading) and 4 GB of RAM.
> This system is fully dedicated to SQL 2000 sp 3a, hotfix 818.
> I have added the /3GB switch into the boot.ini and set the memory of
> SQL fixed to 3043 MB RAM.
> The problem is the following :
> I think that my server isn't using the 3 Gig of Memory for
> applications because of 2 reasons
> - the error initdata: Warning: Could not set working set size to
> 1353312 KB.
> - sqlserveragent agentlog [310] 4 processor(s) and 2048 MB RAM
> detected
> I know that sqlserveragent is not the same as sqlserver but I expect
> then that the sqlserveragent should detect 1GB or 3GB - 3 is most
> likely- and not 2 GB.
> 2 GB should be normal if the /3GB switch isn't used.
> Question:
> How can i really check if the 3 GB switch is activated and working.
> I have found a document at microsoft but that link doesnt clarify
> because on a server and on an enterprise edition the pagedpoolsize is
> the same .
>
http://www.microsoft.com/resources/d...003/all/techre
f/en-us/Default.asp?url=/resources/documentation/windowsServ/2003/all/techre
f/en-us/w2k3tr_4gt_tools.asp
|||What edition of the OS and SQL Server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<romaric_decoene@.yahoo.com> wrote in message news:e4d6707e.0405240241.706d25f1@.posting.google.c om...
> All,
> We have a dell system with 2 processors(4 by the technique of
> hyperthreading) and 4 GB of RAM.
> This system is fully dedicated to SQL 2000 sp 3a, hotfix 818.
> I have added the /3GB switch into the boot.ini and set the memory of
> SQL fixed to 3043 MB RAM.
> The problem is the following :
> I think that my server isn't using the 3 Gig of Memory for
> applications because of 2 reasons
> - the error initdata: Warning: Could not set working set size to
> 1353312 KB.
> - sqlserveragent agentlog [310] 4 processor(s) and 2048 MB RAM
> detected
> I know that sqlserveragent is not the same as sqlserver but I expect
> then that the sqlserveragent should detect 1GB or 3GB - 3 is most
> likely- and not 2 GB.
> 2 GB should be normal if the /3GB switch isn't used.
> Question:
> How can i really check if the 3 GB switch is activated and working.
> I have found a document at microsoft but that link doesnt clarify
> because on a server and on an enterprise edition the pagedpoolsize is
> the same .
>
http://www.microsoft.com/resources/d..._4gt_tools.asp
|||- sqlserveragent agentlog [310] 4 processor(s) and 2048 MB RAM
detected
SQL Agent can't see more than 2 GB regardless of what SQL Server can see but
incorrectly reports what it can see rather than the amount of memory on the
server and is independent of what SQL Server can address.
Do you have Advanced Server and SQL2000 EE ?
Have you specified the "set working set size" ?
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<romaric_decoene@.yahoo.com> wrote in message
news:e4d6707e.0405240241.706d25f1@.posting.google.c om...
> All,
> We have a dell system with 2 processors(4 by the technique of
> hyperthreading) and 4 GB of RAM.
> This system is fully dedicated to SQL 2000 sp 3a, hotfix 818.
> I have added the /3GB switch into the boot.ini and set the memory of
> SQL fixed to 3043 MB RAM.
> The problem is the following :
> I think that my server isn't using the 3 Gig of Memory for
> applications because of 2 reasons
> - the error initdata: Warning: Could not set working set size to
> 1353312 KB.
> - sqlserveragent agentlog [310] 4 processor(s) and 2048 MB RAM
> detected
> I know that sqlserveragent is not the same as sqlserver but I expect
> then that the sqlserveragent should detect 1GB or 3GB - 3 is most
> likely- and not 2 GB.
> 2 GB should be normal if the /3GB switch isn't used.
> Question:
> How can i really check if the 3 GB switch is activated and working.
> I have found a document at microsoft but that link doesnt clarify
> because on a server and on an enterprise edition the pagedpoolsize is
> the same .
>
http://www.microsoft.com/resources/d...003/all/techre
f/en-us/w2k3tr_4gt_tools.asp
|||We are running Windows 2003 Enterprise Server with SQL 2000 Enterprise
edition.
The working set size is set to 1
Does this mean that SQLserver agent can't see that the 3gb switch is
enabled and therefore suppose thats it is the standard 2Gb for
applications and 2 Gb for system? Will it then compete with sql server
service for memory or with the system ?
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message news:<#ChXD0bQEHA.3456@.TK2MSFTNGP11.phx.gbl>...
> - sqlserveragent agentlog [310] 4 processor(s) and 2048 MB RAM
> detected
> SQL Agent can't see more than 2 GB regardless of what SQL Server can see but
> incorrectly reports what it can see rather than the amount of memory on the
> server and is independent of what SQL Server can address.
> Do you have Advanced Server and SQL2000 EE ?
> Have you specified the "set working set size" ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> <romaric_decoene@.yahoo.com> wrote in message
> news:e4d6707e.0405240241.706d25f1@.posting.google.c om...
> http://www.microsoft.com/resources/d...003/all/techre
> f/en-us/w2k3tr_4gt_tools.asp
|||I would set the "set working set size" option to 0
exec sp_configure 'set working',0
reconfigure
and restart the server.
SQLAgent isn't large memory aware or compiled with the /3GB switch so it can
see the standard 2GB user mode address space that all normal win32 processes
can. It comptetes with SQL Server in the same sense that opening notepad on
the server does i.e. not much :-) it's all just virtual address space after
all.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<romaric_decoene@.yahoo.com> wrote in message
news:e4d6707e.0405250329.482e96a9@.posting.google.c om...
> We are running Windows 2003 Enterprise Server with SQL 2000 Enterprise
> edition.
> The working set size is set to 1
> Does this mean that SQLserver agent can't see that the 3gb switch is
> enabled and therefore suppose thats it is the standard 2Gb for
> applications and 2 Gb for system? Will it then compete with sql server
> service for memory or with the system ?
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:<#ChXD0bQEHA.3456@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
but[vbcol=seagreen]
the[vbcol=seagreen]
http://www.microsoft.com/resources/d...003/all/techre[vbcol=seagreen]

Memory More than 2G ==> 3GB switch

All,
We have a dell system with 2 processors(4 by the technique of
hyperthreading) and 4 GB of RAM.
This system is fully dedicated to SQL 2000 sp 3a, hotfix 818.
I have added the /3GB switch into the boot.ini and set the memory of
SQL fixed to 3043 MB RAM.
The problem is the following :
I think that my server isn't using the 3 Gig of Memory for
applications because of 2 reasons
- the error initdata: Warning: Could not set working set size to
1353312 KB.
- sqlserveragent agentlog [310] 4 processor(s) and 2048 MB RAM
detected
I know that sqlserveragent is not the same as sqlserver but I expect
then that the sqlserveragent should detect 1GB or 3GB - 3 is most
likely- and not 2 GB.
2 GB should be normal if the /3GB switch isn't used.
Question:
How can i really check if the 3 GB switch is activated and working.
I have found a document at microsoft but that link doesnt clarify
because on a server and on an enterprise edition the pagedpoolsize is
the same .
http://www.microsoft.com/resources/documentation/WindowsServ/2003/all/techref/en-us/Default.asp?url=/resources/documentation/windowsServ/2003/all/techref/en-us/w2k3tr_4gt_tools.aspHi
This feature will work on Windows 2000 DataCenter or Advenced Server
machines. Do you have SQL Server installed on one of them?
<romaric_decoene@.yahoo.com> wrote in message
news:e4d6707e.0405240241.706d25f1@.posting.google.com...
> All,
> We have a dell system with 2 processors(4 by the technique of
> hyperthreading) and 4 GB of RAM.
> This system is fully dedicated to SQL 2000 sp 3a, hotfix 818.
> I have added the /3GB switch into the boot.ini and set the memory of
> SQL fixed to 3043 MB RAM.
> The problem is the following :
> I think that my server isn't using the 3 Gig of Memory for
> applications because of 2 reasons
> - the error initdata: Warning: Could not set working set size to
> 1353312 KB.
> - sqlserveragent agentlog [310] 4 processor(s) and 2048 MB RAM
> detected
> I know that sqlserveragent is not the same as sqlserver but I expect
> then that the sqlserveragent should detect 1GB or 3GB - 3 is most
> likely- and not 2 GB.
> 2 GB should be normal if the /3GB switch isn't used.
> Question:
> How can i really check if the 3 GB switch is activated and working.
> I have found a document at microsoft but that link doesnt clarify
> because on a server and on an enterprise edition the pagedpoolsize is
> the same .
>
http://www.microsoft.com/resources/documentation/WindowsServ/2003/all/techre
f/en-us/Default.asp?url=/resources/documentation/windowsServ/2003/all/techre
f/en-us/w2k3tr_4gt_tools.asp|||What edition of the OS and SQL Server?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<romaric_decoene@.yahoo.com> wrote in message news:e4d6707e.0405240241.706d25f1@.posting.google.com...
> All,
> We have a dell system with 2 processors(4 by the technique of
> hyperthreading) and 4 GB of RAM.
> This system is fully dedicated to SQL 2000 sp 3a, hotfix 818.
> I have added the /3GB switch into the boot.ini and set the memory of
> SQL fixed to 3043 MB RAM.
> The problem is the following :
> I think that my server isn't using the 3 Gig of Memory for
> applications because of 2 reasons
> - the error initdata: Warning: Could not set working set size to
> 1353312 KB.
> - sqlserveragent agentlog [310] 4 processor(s) and 2048 MB RAM
> detected
> I know that sqlserveragent is not the same as sqlserver but I expect
> then that the sqlserveragent should detect 1GB or 3GB - 3 is most
> likely- and not 2 GB.
> 2 GB should be normal if the /3GB switch isn't used.
> Question:
> How can i really check if the 3 GB switch is activated and working.
> I have found a document at microsoft but that link doesnt clarify
> because on a server and on an enterprise edition the pagedpoolsize is
> the same .
>
http://www.microsoft.com/resources/documentation/WindowsServ/2003/all/techref/en-us/Default.asp?url=/resources/documentation/windowsServ/2003/all/techref/en-us/w2k3tr_4gt_tools.asp|||- sqlserveragent agentlog [310] 4 processor(s) and 2048 MB RAM
detected
SQL Agent can't see more than 2 GB regardless of what SQL Server can see but
incorrectly reports what it can see rather than the amount of memory on the
server and is independent of what SQL Server can address.
Do you have Advanced Server and SQL2000 EE ?
Have you specified the "set working set size" ?
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<romaric_decoene@.yahoo.com> wrote in message
news:e4d6707e.0405240241.706d25f1@.posting.google.com...
> All,
> We have a dell system with 2 processors(4 by the technique of
> hyperthreading) and 4 GB of RAM.
> This system is fully dedicated to SQL 2000 sp 3a, hotfix 818.
> I have added the /3GB switch into the boot.ini and set the memory of
> SQL fixed to 3043 MB RAM.
> The problem is the following :
> I think that my server isn't using the 3 Gig of Memory for
> applications because of 2 reasons
> - the error initdata: Warning: Could not set working set size to
> 1353312 KB.
> - sqlserveragent agentlog [310] 4 processor(s) and 2048 MB RAM
> detected
> I know that sqlserveragent is not the same as sqlserver but I expect
> then that the sqlserveragent should detect 1GB or 3GB - 3 is most
> likely- and not 2 GB.
> 2 GB should be normal if the /3GB switch isn't used.
> Question:
> How can i really check if the 3 GB switch is activated and working.
> I have found a document at microsoft but that link doesnt clarify
> because on a server and on an enterprise edition the pagedpoolsize is
> the same .
>
http://www.microsoft.com/resources/documentation/WindowsServ/2003/all/techref/en-us/Default.asp?url=/resources/documentation/windowsServ/2003/all/techre
f/en-us/w2k3tr_4gt_tools.asp|||We are running Windows 2003 Enterprise Server with SQL 2000 Enterprise
edition.
The working set size is set to 1
Does this mean that SQLserver agent can't see that the 3gb switch is
enabled and therefore suppose thats it is the standard 2Gb for
applications and 2 Gb for system? Will it then compete with sql server
service for memory or with the system ?
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message news:<#ChXD0bQEHA.3456@.TK2MSFTNGP11.phx.gbl>...
> - sqlserveragent agentlog [310] 4 processor(s) and 2048 MB RAM
> detected
> SQL Agent can't see more than 2 GB regardless of what SQL Server can see but
> incorrectly reports what it can see rather than the amount of memory on the
> server and is independent of what SQL Server can address.
> Do you have Advanced Server and SQL2000 EE ?
> Have you specified the "set working set size" ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> <romaric_decoene@.yahoo.com> wrote in message
> news:e4d6707e.0405240241.706d25f1@.posting.google.com...
> > All,
> >
> > We have a dell system with 2 processors(4 by the technique of
> > hyperthreading) and 4 GB of RAM.
> > This system is fully dedicated to SQL 2000 sp 3a, hotfix 818.
> > I have added the /3GB switch into the boot.ini and set the memory of
> > SQL fixed to 3043 MB RAM.
> >
> > The problem is the following :
> >
> > I think that my server isn't using the 3 Gig of Memory for
> > applications because of 2 reasons
> > - the error initdata: Warning: Could not set working set size to
> > 1353312 KB.
> > - sqlserveragent agentlog [310] 4 processor(s) and 2048 MB RAM
> > detected
> >
> > I know that sqlserveragent is not the same as sqlserver but I expect
> > then that the sqlserveragent should detect 1GB or 3GB - 3 is most
> > likely- and not 2 GB.
> > 2 GB should be normal if the /3GB switch isn't used.
> >
> > Question:
> >
> > How can i really check if the 3 GB switch is activated and working.
> > I have found a document at microsoft but that link doesnt clarify
> > because on a server and on an enterprise edition the pagedpoolsize is
> > the same .
> >
> http://www.microsoft.com/resources/documentation/WindowsServ/2003/all/techref/en-us/Default.asp?url=/resources/documentation/windowsServ/2003/all/techre
> f/en-us/w2k3tr_4gt_tools.asp|||I would set the "set working set size" option to 0
exec sp_configure 'set working',0
reconfigure
and restart the server.
SQLAgent isn't large memory aware or compiled with the /3GB switch so it can
see the standard 2GB user mode address space that all normal win32 processes
can. It comptetes with SQL Server in the same sense that opening notepad on
the server does i.e. not much :-) it's all just virtual address space after
all.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<romaric_decoene@.yahoo.com> wrote in message
news:e4d6707e.0405250329.482e96a9@.posting.google.com...
> We are running Windows 2003 Enterprise Server with SQL 2000 Enterprise
> edition.
> The working set size is set to 1
> Does this mean that SQLserver agent can't see that the 3gb switch is
> enabled and therefore suppose thats it is the standard 2Gb for
> applications and 2 Gb for system? Will it then compete with sql server
> service for memory or with the system ?
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:<#ChXD0bQEHA.3456@.TK2MSFTNGP11.phx.gbl>...
> > - sqlserveragent agentlog [310] 4 processor(s) and 2048 MB RAM
> > detected
> >
> > SQL Agent can't see more than 2 GB regardless of what SQL Server can see
but
> > incorrectly reports what it can see rather than the amount of memory on
the
> > server and is independent of what SQL Server can address.
> >
> > Do you have Advanced Server and SQL2000 EE ?
> > Have you specified the "set working set size" ?
> >
> > --
> > HTH
> >
> > Jasper Smith (SQL Server MVP)
> >
> > I support PASS - the definitive, global
> > community for SQL Server professionals -
> > http://www.sqlpass.org
> >
> >
> > <romaric_decoene@.yahoo.com> wrote in message
> > news:e4d6707e.0405240241.706d25f1@.posting.google.com...
> > > All,
> > >
> > > We have a dell system with 2 processors(4 by the technique of
> > > hyperthreading) and 4 GB of RAM.
> > > This system is fully dedicated to SQL 2000 sp 3a, hotfix 818.
> > > I have added the /3GB switch into the boot.ini and set the memory of
> > > SQL fixed to 3043 MB RAM.
> > >
> > > The problem is the following :
> > >
> > > I think that my server isn't using the 3 Gig of Memory for
> > > applications because of 2 reasons
> > > - the error initdata: Warning: Could not set working set size to
> > > 1353312 KB.
> > > - sqlserveragent agentlog [310] 4 processor(s) and 2048 MB RAM
> > > detected
> > >
> > > I know that sqlserveragent is not the same as sqlserver but I expect
> > > then that the sqlserveragent should detect 1GB or 3GB - 3 is most
> > > likely- and not 2 GB.
> > > 2 GB should be normal if the /3GB switch isn't used.
> > >
> > > Question:
> > >
> > > How can i really check if the 3 GB switch is activated and working.
> > > I have found a document at microsoft but that link doesnt clarify
> > > because on a server and on an enterprise edition the pagedpoolsize is
> > > the same .
> > >
> >
http://www.microsoft.com/resources/documentation/WindowsServ/2003/all/techref/en-us/Default.asp?url=/resources/documentation/windowsServ/2003/all/techre
> > f/en-us/w2k3tr_4gt_tools.asp

Memory More than 2G ==> 3GB switch

All,
We have a dell system with 2 processors(4 by the technique of
hyperthreading) and 4 GB of RAM.
This system is fully dedicated to SQL 2000 sp 3a, hotfix 818.
I have added the /3GB switch into the boot.ini and set the memory of
SQL fixed to 3043 MB RAM.
The problem is the following :
I think that my server isn't using the 3 Gig of Memory for
applications because of 2 reasons
- the error initdata: Warning: Could not set working set size to
1353312 KB.
- sqlserveragent agentlog [310] 4 processor(s) and 2048 MB RAM
detected
I know that sqlserveragent is not the same as sqlserver but I expect
then that the sqlserveragent should detect 1GB or 3GB - 3 is most
likely- and not 2 GB.
2 GB should be normal if the /3GB switch isn't used.
Question:
How can i really check if the 3 GB switch is activated and working.
I have found a document at microsoft but that link doesnt clarify
because on a server and on an enterprise edition the pagedpoolsize is
the same .
s/Default.asp?url=/resources/documentation/windowsServ/2003/all/techref/en-us/w2k3
tr_4gt_tools.asp" target="_blank">http://www.microsoft.com/resources/...r_4gt_tools.aspHi
This feature will work on Windows 2000 DataCenter or Advenced Server
machines. Do you have SQL Server installed on one of them?
<romaric_decoene@.yahoo.com> wrote in message
news:e4d6707e.0405240241.706d25f1@.posting.google.com...
> All,
> We have a dell system with 2 processors(4 by the technique of
> hyperthreading) and 4 GB of RAM.
> This system is fully dedicated to SQL 2000 sp 3a, hotfix 818.
> I have added the /3GB switch into the boot.ini and set the memory of
> SQL fixed to 3043 MB RAM.
> The problem is the following :
> I think that my server isn't using the 3 Gig of Memory for
> applications because of 2 reasons
> - the error initdata: Warning: Could not set working set size to
> 1353312 KB.
> - sqlserveragent agentlog [310] 4 processor(s) and 2048 MB RAM
> detected
> I know that sqlserveragent is not the same as sqlserver but I expect
> then that the sqlserveragent should detect 1GB or 3GB - 3 is most
> likely- and not 2 GB.
> 2 GB should be normal if the /3GB switch isn't used.
> Question:
> How can i really check if the 3 GB switch is activated and working.
> I have found a document at microsoft but that link doesnt clarify
> because on a server and on an enterprise edition the pagedpoolsize is
> the same .
>
http://www.microsoft.com/resources/...2003/all/techre
f/en-us/Default.asp?url=/resources/documentation/windowsServ/2003/all/techre
f/en-us/w2k3tr_4gt_tools.asp|||What edition of the OS and SQL Server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<romaric_decoene@.yahoo.com> wrote in message news:e4d6707e.0405240241.706d25f1@.posting.googl
e.com...
> All,
> We have a dell system with 2 processors(4 by the technique of
> hyperthreading) and 4 GB of RAM.
> This system is fully dedicated to SQL 2000 sp 3a, hotfix 818.
> I have added the /3GB switch into the boot.ini and set the memory of
> SQL fixed to 3043 MB RAM.
> The problem is the following :
> I think that my server isn't using the 3 Gig of Memory for
> applications because of 2 reasons
> - the error initdata: Warning: Could not set working set size to
> 1353312 KB.
> - sqlserveragent agentlog [310] 4 processor(s) and 2048 MB RAM
> detected
> I know that sqlserveragent is not the same as sqlserver but I expect
> then that the sqlserveragent should detect 1GB or 3GB - 3 is most
> likely- and not 2 GB.
> 2 GB should be normal if the /3GB switch isn't used.
> Question:
> How can i really check if the 3 GB switch is activated and working.
> I have found a document at microsoft but that link doesnt clarify
> because on a server and on an enterprise edition the pagedpoolsize is
> the same .
>
s/Default.asp?url=/resources/documentation/windowsServ/2003/all/techref/en-us/w2k3
tr_4gt_tools.asp" target="_blank">http://www.microsoft.com/resources/...r_4gt_tools.asp|||- sqlserveragent agentlog [310] 4 processor(s) and 2048 MB RAM
detected
SQL Agent can't see more than 2 GB regardless of what SQL Server can see but
incorrectly reports what it can see rather than the amount of memory on the
server and is independent of what SQL Server can address.
Do you have Advanced Server and SQL2000 EE ?
Have you specified the "set working set size" ?
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<romaric_decoene@.yahoo.com> wrote in message
news:e4d6707e.0405240241.706d25f1@.posting.google.com...
> All,
> We have a dell system with 2 processors(4 by the technique of
> hyperthreading) and 4 GB of RAM.
> This system is fully dedicated to SQL 2000 sp 3a, hotfix 818.
> I have added the /3GB switch into the boot.ini and set the memory of
> SQL fixed to 3043 MB RAM.
> The problem is the following :
> I think that my server isn't using the 3 Gig of Memory for
> applications because of 2 reasons
> - the error initdata: Warning: Could not set working set size to
> 1353312 KB.
> - sqlserveragent agentlog [310] 4 processor(s) and 2048 MB RAM
> detected
> I know that sqlserveragent is not the same as sqlserver but I expect
> then that the sqlserveragent should detect 1GB or 3GB - 3 is most
> likely- and not 2 GB.
> 2 GB should be normal if the /3GB switch isn't used.
> Question:
> How can i really check if the 3 GB switch is activated and working.
> I have found a document at microsoft but that link doesnt clarify
> because on a server and on an enterprise edition the pagedpoolsize is
> the same .
>
s/Default.asp?url=/resources/documentation/windowsServ/2003/all/techre" target="_blank">http://www.microsoft.com/resources/...2003/all/techre
f/en-us/w2k3tr_4gt_tools.asp|||We are running Windows 2003 Enterprise Server with SQL 2000 Enterprise
edition.
The working set size is set to 1
Does this mean that SQLserver agent can't see that the 3gb switch is
enabled and therefore suppose thats it is the standard 2Gb for
applications and 2 Gb for system? Will it then compete with sql server
service for memory or with the system ?
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message news:<#ChXD0bQEHA.3456@.TK2MSFTNG
P11.phx.gbl>...
> - sqlserveragent agentlog [310] 4 processor(s) and 2048 MB RAM
> detected
> SQL Agent can't see more than 2 GB regardless of what SQL Server can see b
ut
> incorrectly reports what it can see rather than the amount of memory on th
e
> server and is independent of what SQL Server can address.
> Do you have Advanced Server and SQL2000 EE ?
> Have you specified the "set working set size" ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> <romaric_decoene@.yahoo.com> wrote in message
> news:e4d6707e.0405240241.706d25f1@.posting.google.com...
> -us/Default.asp?url=/resources/documentation/windowsServ/2003/all/techre" target="_blank">http://www.microsoft.com/resources/...2003/all/techre
> f/en-us/w2k3tr_4gt_tools.asp|||I would set the "set working set size" option to 0
exec sp_configure 'set working',0
reconfigure
and restart the server.
SQLAgent isn't large memory aware or compiled with the /3GB switch so it can
see the standard 2GB user mode address space that all normal win32 processes
can. It comptetes with SQL Server in the same sense that opening notepad on
the server does i.e. not much :-) it's all just virtual address space after
all.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<romaric_decoene@.yahoo.com> wrote in message
news:e4d6707e.0405250329.482e96a9@.posting.google.com...
> We are running Windows 2003 Enterprise Server with SQL 2000 Enterprise
> edition.
> The working set size is set to 1
> Does this mean that SQLserver agent can't see that the 3gb switch is
> enabled and therefore suppose thats it is the standard 2Gb for
> applications and 2 Gb for system? Will it then compete with sql server
> service for memory or with the system ?
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:<#ChXD0bQEHA.3456@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
but[vbcol=seagreen]
the[vbcol=seagreen]
l=/resources/documentation/windowsServ/2003/all/techre" target="_blank">http://www.microsoft.com/resources/...2003/all/techre[vbcol=seagreen]

Memory leak...How to get memory usage for each connections?

Hi Guys,
I find there is memory leak in our SQL 2000+SP3 databass. We have a
client-server system, and the there are Jrun4.0 running JSP and someother
Java application talks to the database.
However, for only less than 40 users, the SQL 2000 memory usage will
increase consistantly until it use all the memory it can get ,about 1.7G
memory. Then eventually the SQL server process will hang in couple days.
I don't know whether JRun JSP stuff or Java application is the culprit, and
I am wondering if there is a way to monitor the memory usage of every
database connection.
I would love to know what you guys think!
Thanks a lot!The number of users has little or nothing to do with how much memory sql
server uses. It's more the amount of data your accessing and how you do it.
A single user can easily take up all the available ram. The process you
describe (except maybe for the hanging portion) is normal and by design.
See here: http://www.support.microsoft.com/?id=321363 SQL Server 7 &
2000 memory usage
This is not to say there isn't a memory leak somewhere but it is unlikely
from SQL Server. I would suggest you make sure your queries are properly
tuned and you might want to set an upper limit on the SQL Server memory and
see if that helps.
--
Andrew J. Kelly
SQL Server MVP
"Erik Miller" <bopew2000@.yahoo.com> wrote in message
news:7RNMa.46791$T85.5399682@.news1.telusplanet.net...
> Hi Guys,
> I find there is memory leak in our SQL 2000+SP3 databass. We have a
> client-server system, and the there are Jrun4.0 running JSP and someother
> Java application talks to the database.
> However, for only less than 40 users, the SQL 2000 memory usage will
> increase consistantly until it use all the memory it can get ,about 1.7G
> memory. Then eventually the SQL server process will hang in couple days.
> I don't know whether JRun JSP stuff or Java application is the culprit,
and
> I am wondering if there is a way to monitor the memory usage of every
> database connection.
> I would love to know what you guys think!
> Thanks a lot!
>|||Well.One thing is for sure, the SQL server hangs.
The application is quite complicated, so it is very hard for me to push
developers to tune every thing they wrote. I only want to find out if there
is way to find who takes the most memory or makes SQL server grab more
momory.
I did try to limit the max memory to 1.5 G. It hangs too.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23K%23kUzRQDHA.560@.TK2MSFTNGP10.phx.gbl...
> The number of users has little or nothing to do with how much memory sql
> server uses. It's more the amount of data your accessing and how you do
it.
> A single user can easily take up all the available ram. The process you
> describe (except maybe for the hanging portion) is normal and by design.
> See here: http://www.support.microsoft.com/?id=321363 SQL Server 7 &
> 2000 memory usage
> This is not to say there isn't a memory leak somewhere but it is unlikely
> from SQL Server. I would suggest you make sure your queries are properly
> tuned and you might want to set an upper limit on the SQL Server memory
and
> see if that helps.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Erik Miller" <bopew2000@.yahoo.com> wrote in message
> news:7RNMa.46791$T85.5399682@.news1.telusplanet.net...
> > Hi Guys,
> >
> > I find there is memory leak in our SQL 2000+SP3 databass. We have a
> > client-server system, and the there are Jrun4.0 running JSP and
someother
> > Java application talks to the database.
> >
> > However, for only less than 40 users, the SQL 2000 memory usage will
> > increase consistantly until it use all the memory it can get ,about 1.7G
> > memory. Then eventually the SQL server process will hang in couple days.
> >
> > I don't know whether JRun JSP stuff or Java application is the culprit,
> and
> > I am wondering if there is a way to monitor the memory usage of every
> > database connection.
> >
> > I would love to know what you guys think!
> >
> > Thanks a lot!
> >
> >
>|||Do you have other apps running on the same server as sql server? How about
extended sp's? The only real way to tell what is making it use more memory
is to run profiler and perfmon to see what is running when the buffers start
climbing. But if you run profiler traces you can easily tell what sp's are
taking large amounts of I/O, cpu etc. This will certainly give you a clue
as to what may be gobbling up ram in the buffer.
Maybe this will help:
http://www.mssqlserver.com/faq/troubleshooting-memoryleak.asp
Andrew J. Kelly
SQL Server MVP
"Erik Miller" <bopew2000@.yahoo.com> wrote in message
news:mpXMa.52170$Mc4.6537800@.news0.telusplanet.net...
> Well.One thing is for sure, the SQL server hangs.
> The application is quite complicated, so it is very hard for me to push
> developers to tune every thing they wrote. I only want to find out if
there
> is way to find who takes the most memory or makes SQL server grab more
> momory.
> I did try to limit the max memory to 1.5 G. It hangs too.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23K%23kUzRQDHA.560@.TK2MSFTNGP10.phx.gbl...
> > The number of users has little or nothing to do with how much memory sql
> > server uses. It's more the amount of data your accessing and how you do
> it.
> > A single user can easily take up all the available ram. The process you
> > describe (except maybe for the hanging portion) is normal and by design.
> > See here: http://www.support.microsoft.com/?id=321363 SQL Server 7
&
> > 2000 memory usage
> > This is not to say there isn't a memory leak somewhere but it is
unlikely
> > from SQL Server. I would suggest you make sure your queries are
properly
> > tuned and you might want to set an upper limit on the SQL Server memory
> and
> > see if that helps.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Erik Miller" <bopew2000@.yahoo.com> wrote in message
> > news:7RNMa.46791$T85.5399682@.news1.telusplanet.net...
> > > Hi Guys,
> > >
> > > I find there is memory leak in our SQL 2000+SP3 databass. We have a
> > > client-server system, and the there are Jrun4.0 running JSP and
> someother
> > > Java application talks to the database.
> > >
> > > However, for only less than 40 users, the SQL 2000 memory usage will
> > > increase consistantly until it use all the memory it can get ,about
1.7G
> > > memory. Then eventually the SQL server process will hang in couple
days.
> > >
> > > I don't know whether JRun JSP stuff or Java application is the
culprit,
> > and
> > > I am wondering if there is a way to monitor the memory usage of every
> > > database connection.
> > >
> > > I would love to know what you guys think!
> > >
> > > Thanks a lot!
> > >
> > >
> >
> >
>|||SQL using 1.7GB of memory is perfectly normal. the hanging is probably NOT
caused by memory usage. Have you checked for blocking?
"Erik Miller" <bopew2000@.yahoo.com> wrote in message
news:mpXMa.52170$Mc4.6537800@.news0.telusplanet.net...
> Well.One thing is for sure, the SQL server hangs.
> The application is quite complicated, so it is very hard for me to push
> developers to tune every thing they wrote. I only want to find out if
there
> is way to find who takes the most memory or makes SQL server grab more
> momory.
> I did try to limit the max memory to 1.5 G. It hangs too.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23K%23kUzRQDHA.560@.TK2MSFTNGP10.phx.gbl...
> > The number of users has little or nothing to do with how much memory sql
> > server uses. It's more the amount of data your accessing and how you do
> it.
> > A single user can easily take up all the available ram. The process you
> > describe (except maybe for the hanging portion) is normal and by design.
> > See here: http://www.support.microsoft.com/?id=321363 SQL Server 7
&
> > 2000 memory usage
> > This is not to say there isn't a memory leak somewhere but it is
unlikely
> > from SQL Server. I would suggest you make sure your queries are
properly
> > tuned and you might want to set an upper limit on the SQL Server memory
> and
> > see if that helps.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Erik Miller" <bopew2000@.yahoo.com> wrote in message
> > news:7RNMa.46791$T85.5399682@.news1.telusplanet.net...
> > > Hi Guys,
> > >
> > > I find there is memory leak in our SQL 2000+SP3 databass. We have a
> > > client-server system, and the there are Jrun4.0 running JSP and
> someother
> > > Java application talks to the database.
> > >
> > > However, for only less than 40 users, the SQL 2000 memory usage will
> > > increase consistantly until it use all the memory it can get ,about
1.7G
> > > memory. Then eventually the SQL server process will hang in couple
days.
> > >
> > > I don't know whether JRun JSP stuff or Java application is the
culprit,
> > and
> > > I am wondering if there is a way to monitor the memory usage of every
> > > database connection.
> > >
> > > I would love to know what you guys think!
> > >
> > > Thanks a lot!
> > >
> > >
> >
> >
>|||Thanks for all the hints here, appreciated !
I'll try to check them accordingly.
"Hal Berenson" <haroldb@.truemountainconsulting.com> wrote in message
news:Ou4J3sXQDHA.2432@.TK2MSFTNGP10.phx.gbl...
> The hang situation makes this quite different from the usual concerns
about
> SQL Server allocating very large amounts of memory. Likely culprits:
> - Extended Stored Procedures (XP) - If you have any user written XPs then
> they are a very likely cause of problems. For example, they don't use SQL
> Server's internal memory management, and there is nothing to prevent them
> from behaving badly.
> - OLE Automation - If you use this feature then you have some similar
> problems as XPs in that the environment the objects run in is "outside"
the
> SQL Server environment itself. The likeliness of a problem is much lower
> than an XP, but its still present.
> - Distributed Queries - The only OLE DB provider that really has been
tuned
> to run in the SQL Server address space, and stress tested in high
> transaction rate environments by the SQL Server group, is SQLOLEDB. Other
> OLE DB providers aren't concious of running in the SQL Server address
space
> and are more likely to misbehave in that environment. I'd particularly be
> wary of providers that are not supplied by Microsoft. Fortunately, you
can
> just make sure that AllowInProcess=0 for these OLE DB providers. (A more
> general recommendation would be to test with AllowInProcess=0 and then set
> AllowInProcess=1 when you are very confident that the provider is working
> well.)
> - Bug in SQL Server. If you rule out all the external items that could
> behave badly, then you tend to look at the possibility that you are
running
> into a bug in SQL Server. In that case, I'd place a support call with
> Microsoft. Debugging something so general can be very complex, and it
could
> take a very long time to do simply based on suggestions from folks in a
> newsgroup of what you should try.
> --
> Hal Berenson, SQL Server MVP
> True Mountain Group LLC
>
> "Erik Miller" <bopew2000@.yahoo.com> wrote in message
> news:mpXMa.52170$Mc4.6537800@.news0.telusplanet.net...
> > Well.One thing is for sure, the SQL server hangs.
> >
> > The application is quite complicated, so it is very hard for me to push
> > developers to tune every thing they wrote. I only want to find out if
> there
> > is way to find who takes the most memory or makes SQL server grab more
> > momory.
> >
> > I did try to limit the max memory to 1.5 G. It hangs too.
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:%23K%23kUzRQDHA.560@.TK2MSFTNGP10.phx.gbl...
> > > The number of users has little or nothing to do with how much memory
sql
> > > server uses. It's more the amount of data your accessing and how you
do
> > it.
> > > A single user can easily take up all the available ram. The process
you
> > > describe (except maybe for the hanging portion) is normal and by
design.
> > > See here: http://www.support.microsoft.com/?id=321363 SQL Server
7
> &
> > > 2000 memory usage
> > > This is not to say there isn't a memory leak somewhere but it is
> unlikely
> > > from SQL Server. I would suggest you make sure your queries are
> properly
> > > tuned and you might want to set an upper limit on the SQL Server
memory
> > and
> > > see if that helps.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Erik Miller" <bopew2000@.yahoo.com> wrote in message
> > > news:7RNMa.46791$T85.5399682@.news1.telusplanet.net...
> > > > Hi Guys,
> > > >
> > > > I find there is memory leak in our SQL 2000+SP3 databass. We have a
> > > > client-server system, and the there are Jrun4.0 running JSP and
> > someother
> > > > Java application talks to the database.
> > > >
> > > > However, for only less than 40 users, the SQL 2000 memory usage will
> > > > increase consistantly until it use all the memory it can get ,about
> 1.7G
> > > > memory. Then eventually the SQL server process will hang in couple
> days.
> > > >
> > > > I don't know whether JRun JSP stuff or Java application is the
> culprit,
> > > and
> > > > I am wondering if there is a way to monitor the memory usage of
every
> > > > database connection.
> > > >
> > > > I would love to know what you guys think!
> > > >
> > > > Thanks a lot!
> > > >
> > > >
> > >
> > >
> >
> >
>