Hi!
I have an issue with sql server 2005. It weems that the memory usage grows
forever and never decreases. If I get users on they use a certain amount of
memory. If they all get off the system, the memory usage never goes down. If
they get back on it then grows. Eventually the machine starts to thrash
because it runs out of physical memory. What's up with that?
Joe
Joe D. wrote:
> Hi!
> I have an issue with sql server 2005. It weems that the memory usage grows
> forever and never decreases. If I get users on they use a certain amount of
> memory. If they all get off the system, the memory usage never goes down. If
> they get back on it then grows. Eventually the machine starts to thrash
> because it runs out of physical memory. What's up with that?
> Joe
That's normal behavior for SQL Server - it caches data pages in memory
to minimize disk I/O, and won't release memory unless the O/S requests
it. Part of your job, as the admin, is to balance the memory use
between SQL and the O/S to prevent that "thrashing".
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||The first half of the description is more or less the expected behavior, and
not a problem. But the following is not the expected behavior:
> Eventually the machine starts to thrash because it runs out of physical memory.
Could you be more specific about 'thrash' and 'runs out of physical memory'?
Linchi
"Joe D." wrote:
> Hi!
> I have an issue with sql server 2005. It weems that the memory usage grows
> forever and never decreases. If I get users on they use a certain amount of
> memory. If they all get off the system, the memory usage never goes down. If
> they get back on it then grows. Eventually the machine starts to thrash
> because it runs out of physical memory. What's up with that?
> Joe
|||Yes...
It appears that it starts using swap space. The CPU load jumps and the
machine slows to a crawl. If I get everyone off and restart the instance,
all gets back to normal. This normally takes about 4 - 5 hours to degrade to
the point of restart.
Joe
"Linchi Shea" wrote:
[vbcol=seagreen]
> The first half of the description is more or less the expected behavior, and
> not a problem. But the following is not the expected behavior:
>
> Could you be more specific about 'thrash' and 'runs out of physical memory'?
> Linchi
> "Joe D." wrote:
|||The expected behavior is for SQL Server to release memory when the
system becomes memory constrained, just as it is expected to keep
grabbing more when it is available. However for situations like you
describe SQL Server has a configuration setting to limit the total
memory it will use, and it sounds like you need to use it to allow
enough memory for whatever else is on the system.
Roy Harvey
Beacon Falls, CT
On Wed, 24 Jan 2007 12:01:02 -0800, Joe D.
<JoeD@.discussions.microsoft.com> wrote:
>Yes...
>It appears that it starts using swap space. The CPU load jumps and the
>machine slows to a crawl. If I get everyone off and restart the instance,
>all gets back to normal. This normally takes about 4 - 5 hours to degrade to
>the point of restart.
>Joe
|||This is a very common problem and I wish the OS and SQL groups would address
it. SQL Server will hold onto all memory until there is 5 MB of free
memory. It appears as if the disk cache is included in the free memory and
is usually much larger than 5 MB. Therefore, SQL Server never releases
memory. The disk cache will swap the SQL buffer out of memory to disk in
order to make more room for the disk cache if the disk cache is getting too
small. This means that SQL Server will NEVER release memory as long as you
have swap space left. Your only option is to set the max memory of SQL
server to a lower amount so that there is reserved free space for the
OS/disk cache.
I've experienced this myself numerous times including on a 32 GB 4 way
server running the 64 bit version of SQL 2005. This was a box for a data
warehouse and the users complained that it sometimes was very slow. Looking
at the max cache size, it was sometimes using 4 GB of RAM for the disk
cache, but SQL was using 30 GB of RAM (30 + 4 isn't 32). I had to set the
max memory of SQL to 27 GB and their performance improved dramatically.
You can use "Memory:Cache Bytes Peak" to see the max amount of memory used
for cache and "Memory:Cache Bytes" to see the current amount. You can also
look for the committed bytes + cache being higher than physical memory.
This indicates that you are having memory pressure and probably need to
adjust something.
Thanks,
Stephen Mills
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:D305F0C7-355A-461E-8E1A-1C2B2413E87D@.microsoft.com...[vbcol=seagreen]
> The first half of the description is more or less the expected behavior,
> and
> not a problem. But the following is not the expected behavior:
>
> Could you be more specific about 'thrash' and 'runs out of physical
> memory'?
> Linchi
> "Joe D." wrote:
Showing posts with label users. Show all posts
Showing posts with label users. Show all posts
Monday, March 19, 2012
Memory usage grows and grows
Hi!
I have an issue with sql server 2005. It weems that the memory usage grows
forever and never decreases. If I get users on they use a certain amount of
memory. If they all get off the system, the memory usage never goes down. If
they get back on it then grows. Eventually the machine starts to thrash
because it runs out of physical memory. What's up with that'
JoeJoe D. wrote:
> Hi!
> I have an issue with sql server 2005. It weems that the memory usage grows
> forever and never decreases. If I get users on they use a certain amount o
f
> memory. If they all get off the system, the memory usage never goes down.
If
> they get back on it then grows. Eventually the machine starts to thrash
> because it runs out of physical memory. What's up with that'
> Joe
That's normal behavior for SQL Server - it caches data pages in memory
to minimize disk I/O, and won't release memory unless the O/S requests
it. Part of your job, as the admin, is to balance the memory use
between SQL and the O/S to prevent that "thrashing".
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The first half of the description is more or less the expected behavior, and
not a problem. But the following is not the expected behavior:
> Eventually the machine starts to thrash because it runs out of physical memory.[/v
bcol]
Could you be more specific about 'thrash' and 'runs out of physical memory'?
Linchi
"Joe D." wrote:
[vbcol=seagreen]
> Hi!
> I have an issue with sql server 2005. It weems that the memory usage grows
> forever and never decreases. If I get users on they use a certain amount o
f
> memory. If they all get off the system, the memory usage never goes down.
If
> they get back on it then grows. Eventually the machine starts to thrash
> because it runs out of physical memory. What's up with that'
> Joe|||Yes...
It appears that it starts using swap space. The CPU load jumps and the
machine slows to a crawl. If I get everyone off and restart the instance,
all gets back to normal. This normally takes about 4 - 5 hours to degrade t
o
the point of restart.
Joe
"Linchi Shea" wrote:
[vbcol=seagreen]
> The first half of the description is more or less the expected behavior, a
nd
> not a problem. But the following is not the expected behavior:
>
> Could you be more specific about 'thrash' and 'runs out of physical memory
'?
> Linchi
> "Joe D." wrote:
>|||The expected behavior is for SQL Server to release memory when the
system becomes memory constrained, just as it is expected to keep
grabbing more when it is available. However for situations like you
describe SQL Server has a configuration setting to limit the total
memory it will use, and it sounds like you need to use it to allow
enough memory for whatever else is on the system.
Roy Harvey
Beacon Falls, CT
On Wed, 24 Jan 2007 12:01:02 -0800, Joe D.
<JoeD@.discussions.microsoft.com> wrote:
>Yes...
>It appears that it starts using swap space. The CPU load jumps and the
>machine slows to a crawl. If I get everyone off and restart the instance,
>all gets back to normal. This normally takes about 4 - 5 hours to degrade
to
>the point of restart.
>Joe|||This is a very common problem and I wish the OS and SQL groups would address
it. SQL Server will hold onto all memory until there is 5 MB of free
memory. It appears as if the disk cache is included in the free memory and
is usually much larger than 5 MB. Therefore, SQL Server never releases
memory. The disk cache will swap the SQL buffer out of memory to disk in
order to make more room for the disk cache if the disk cache is getting too
small. This means that SQL Server will NEVER release memory as long as you
have swap space left. Your only option is to set the max memory of SQL
server to a lower amount so that there is reserved free space for the
OS/disk cache.
I've experienced this myself numerous times including on a 32 GB 4 way
server running the 64 bit version of SQL 2005. This was a box for a data
warehouse and the users complained that it sometimes was very slow. Looking
at the max cache size, it was sometimes using 4 GB of RAM for the disk
cache, but SQL was using 30 GB of RAM (30 + 4 isn't 32). I had to set the
max memory of SQL to 27 GB and their performance improved dramatically.
You can use "Memory:Cache Bytes Peak" to see the max amount of memory used
for cache and "Memory:Cache Bytes" to see the current amount. You can also
look for the committed bytes + cache being higher than physical memory.
This indicates that you are having memory pressure and probably need to
adjust something.
Thanks,
Stephen Mills
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:D305F0C7-355A-461E-8E1A-1C2B2413E87D@.microsoft.com...[vbcol=seagreen]
> The first half of the description is more or less the expected behavior,
> and
> not a problem. But the following is not the expected behavior:
>
> Could you be more specific about 'thrash' and 'runs out of physical
> memory'?
> Linchi
> "Joe D." wrote:
>
I have an issue with sql server 2005. It weems that the memory usage grows
forever and never decreases. If I get users on they use a certain amount of
memory. If they all get off the system, the memory usage never goes down. If
they get back on it then grows. Eventually the machine starts to thrash
because it runs out of physical memory. What's up with that'
JoeJoe D. wrote:
> Hi!
> I have an issue with sql server 2005. It weems that the memory usage grows
> forever and never decreases. If I get users on they use a certain amount o
f
> memory. If they all get off the system, the memory usage never goes down.
If
> they get back on it then grows. Eventually the machine starts to thrash
> because it runs out of physical memory. What's up with that'
> Joe
That's normal behavior for SQL Server - it caches data pages in memory
to minimize disk I/O, and won't release memory unless the O/S requests
it. Part of your job, as the admin, is to balance the memory use
between SQL and the O/S to prevent that "thrashing".
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The first half of the description is more or less the expected behavior, and
not a problem. But the following is not the expected behavior:
> Eventually the machine starts to thrash because it runs out of physical memory.[/v
bcol]
Could you be more specific about 'thrash' and 'runs out of physical memory'?
Linchi
"Joe D." wrote:
[vbcol=seagreen]
> Hi!
> I have an issue with sql server 2005. It weems that the memory usage grows
> forever and never decreases. If I get users on they use a certain amount o
f
> memory. If they all get off the system, the memory usage never goes down.
If
> they get back on it then grows. Eventually the machine starts to thrash
> because it runs out of physical memory. What's up with that'
> Joe|||Yes...
It appears that it starts using swap space. The CPU load jumps and the
machine slows to a crawl. If I get everyone off and restart the instance,
all gets back to normal. This normally takes about 4 - 5 hours to degrade t
o
the point of restart.
Joe
"Linchi Shea" wrote:
[vbcol=seagreen]
> The first half of the description is more or less the expected behavior, a
nd
> not a problem. But the following is not the expected behavior:
>
> Could you be more specific about 'thrash' and 'runs out of physical memory
'?
> Linchi
> "Joe D." wrote:
>|||The expected behavior is for SQL Server to release memory when the
system becomes memory constrained, just as it is expected to keep
grabbing more when it is available. However for situations like you
describe SQL Server has a configuration setting to limit the total
memory it will use, and it sounds like you need to use it to allow
enough memory for whatever else is on the system.
Roy Harvey
Beacon Falls, CT
On Wed, 24 Jan 2007 12:01:02 -0800, Joe D.
<JoeD@.discussions.microsoft.com> wrote:
>Yes...
>It appears that it starts using swap space. The CPU load jumps and the
>machine slows to a crawl. If I get everyone off and restart the instance,
>all gets back to normal. This normally takes about 4 - 5 hours to degrade
to
>the point of restart.
>Joe|||This is a very common problem and I wish the OS and SQL groups would address
it. SQL Server will hold onto all memory until there is 5 MB of free
memory. It appears as if the disk cache is included in the free memory and
is usually much larger than 5 MB. Therefore, SQL Server never releases
memory. The disk cache will swap the SQL buffer out of memory to disk in
order to make more room for the disk cache if the disk cache is getting too
small. This means that SQL Server will NEVER release memory as long as you
have swap space left. Your only option is to set the max memory of SQL
server to a lower amount so that there is reserved free space for the
OS/disk cache.
I've experienced this myself numerous times including on a 32 GB 4 way
server running the 64 bit version of SQL 2005. This was a box for a data
warehouse and the users complained that it sometimes was very slow. Looking
at the max cache size, it was sometimes using 4 GB of RAM for the disk
cache, but SQL was using 30 GB of RAM (30 + 4 isn't 32). I had to set the
max memory of SQL to 27 GB and their performance improved dramatically.
You can use "Memory:Cache Bytes Peak" to see the max amount of memory used
for cache and "Memory:Cache Bytes" to see the current amount. You can also
look for the committed bytes + cache being higher than physical memory.
This indicates that you are having memory pressure and probably need to
adjust something.
Thanks,
Stephen Mills
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:D305F0C7-355A-461E-8E1A-1C2B2413E87D@.microsoft.com...[vbcol=seagreen]
> The first half of the description is more or less the expected behavior,
> and
> not a problem. But the following is not the expected behavior:
>
> Could you be more specific about 'thrash' and 'runs out of physical
> memory'?
> Linchi
> "Joe D." wrote:
>
Friday, March 9, 2012
memory usage
Hi,
On a webserver I have a small sharepoint-application with SQL Server 2005
Express:
As users click through the web-app I can easily see that the amount of the
memory usage by the SQL Server is constantly increasing. When the application
is idle or all web-users are away from the application, the memory usage of
SQL Server remains on the last level without decreasing. Why?
Can we decrease the memory usage by SQL Server as soon as "nothing happens"
on the wep-application?
Can we decrease this memory usage permanently for example every minute?
Thanks for your help.
regard
PatrickSQL Server caches data and index pages after reading them from disk, for performance reasons.
Decreasing memory usage and hence removing this from cache would defeat caching in the first place.
You can set a max server memory (see sp_configure).
Have a look at:
INF: SQL Server Memory Usage
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
http://www.mssqlserver.com/faq/troubleshooting-memoryleak.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Patrick D." <PatrickD@.discussions.microsoft.com> wrote in message
news:C4E886E0-3010-4D64-A3AE-16CCBC28D68E@.microsoft.com...
> Hi,
> On a webserver I have a small sharepoint-application with SQL Server 2005
> Express:
> As users click through the web-app I can easily see that the amount of the
> memory usage by the SQL Server is constantly increasing. When the application
> is idle or all web-users are away from the application, the memory usage of
> SQL Server remains on the last level without decreasing. Why?
> Can we decrease the memory usage by SQL Server as soon as "nothing happens"
> on the wep-application?
> Can we decrease this memory usage permanently for example every minute?
> Thanks for your help.
> regard
> Patrick|||This isn't an area I've gotten into much, but I would bet that the increased
memory is in the buffer cache - the place in memory where SQL Server is
keeping the pages read from disk.
The reason for this is that a data page that was requested from disk, is
very likely to be requested again and by keeping it in the buffer cache
(memory) a future request for it can be done much faster.
Now, the problem I've always had is: how to I allocate as much memory as I
can to the buffer cache, not how do I minimize, or clear it. However, I know
that 'sp_configure' controls these type of things and that I've seen a
reference to something that will clear the cache (for testing).
Search BOL for "Buffer Management" and read it. Then, look at the page for
"DBCC DROPCLEANBUFFERS".
Jay
"Patrick D." <PatrickD@.discussions.microsoft.com> wrote in message
news:C4E886E0-3010-4D64-A3AE-16CCBC28D68E@.microsoft.com...
> Hi,
> On a webserver I have a small sharepoint-application with SQL Server 2005
> Express:
> As users click through the web-app I can easily see that the amount of the
> memory usage by the SQL Server is constantly increasing. When the
> application
> is idle or all web-users are away from the application, the memory usage
> of
> SQL Server remains on the last level without decreasing. Why?
> Can we decrease the memory usage by SQL Server as soon as "nothing
> happens"
> on the wep-application?
> Can we decrease this memory usage permanently for example every minute?
> Thanks for your help.
> regard
> Patrick
On a webserver I have a small sharepoint-application with SQL Server 2005
Express:
As users click through the web-app I can easily see that the amount of the
memory usage by the SQL Server is constantly increasing. When the application
is idle or all web-users are away from the application, the memory usage of
SQL Server remains on the last level without decreasing. Why?
Can we decrease the memory usage by SQL Server as soon as "nothing happens"
on the wep-application?
Can we decrease this memory usage permanently for example every minute?
Thanks for your help.
regard
PatrickSQL Server caches data and index pages after reading them from disk, for performance reasons.
Decreasing memory usage and hence removing this from cache would defeat caching in the first place.
You can set a max server memory (see sp_configure).
Have a look at:
INF: SQL Server Memory Usage
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
http://www.mssqlserver.com/faq/troubleshooting-memoryleak.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Patrick D." <PatrickD@.discussions.microsoft.com> wrote in message
news:C4E886E0-3010-4D64-A3AE-16CCBC28D68E@.microsoft.com...
> Hi,
> On a webserver I have a small sharepoint-application with SQL Server 2005
> Express:
> As users click through the web-app I can easily see that the amount of the
> memory usage by the SQL Server is constantly increasing. When the application
> is idle or all web-users are away from the application, the memory usage of
> SQL Server remains on the last level without decreasing. Why?
> Can we decrease the memory usage by SQL Server as soon as "nothing happens"
> on the wep-application?
> Can we decrease this memory usage permanently for example every minute?
> Thanks for your help.
> regard
> Patrick|||This isn't an area I've gotten into much, but I would bet that the increased
memory is in the buffer cache - the place in memory where SQL Server is
keeping the pages read from disk.
The reason for this is that a data page that was requested from disk, is
very likely to be requested again and by keeping it in the buffer cache
(memory) a future request for it can be done much faster.
Now, the problem I've always had is: how to I allocate as much memory as I
can to the buffer cache, not how do I minimize, or clear it. However, I know
that 'sp_configure' controls these type of things and that I've seen a
reference to something that will clear the cache (for testing).
Search BOL for "Buffer Management" and read it. Then, look at the page for
"DBCC DROPCLEANBUFFERS".
Jay
"Patrick D." <PatrickD@.discussions.microsoft.com> wrote in message
news:C4E886E0-3010-4D64-A3AE-16CCBC28D68E@.microsoft.com...
> Hi,
> On a webserver I have a small sharepoint-application with SQL Server 2005
> Express:
> As users click through the web-app I can easily see that the amount of the
> memory usage by the SQL Server is constantly increasing. When the
> application
> is idle or all web-users are away from the application, the memory usage
> of
> SQL Server remains on the last level without decreasing. Why?
> Can we decrease the memory usage by SQL Server as soon as "nothing
> happens"
> on the wep-application?
> Can we decrease this memory usage permanently for example every minute?
> Thanks for your help.
> regard
> Patrick
Memory question for the gurus
Hi Guys, first time posting here. My boss has a Lotus Notes application accessing SQL server with about 60 users. I have a custom VB app with 40+ users. Our SQL Server has
2 gigabyte of memory on it. Is this too low? My boss is expecting to have 300+ users on his Notes app when he rolls it out to our other branches. What would be the ideal amount of memory for 300+ users?Depends: will the 300+ users access the data simultaneously? 24/7? How much data will they transfer? How big is the database, and how is it used by the application? Are there any agreements with users concerning availablility/performance? What's acceptable to them?|||My boss has a Lotus Notes application accessing SQL server with about 60 users.
Nooooooooooooooooooooooooooooooooooooooo
AHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH
In the name of GOD WHY?
Got a lot of left over Notus Lotes developers who haven't made the jump?
Notestrix? Notespump? How are they talking to SQL Server?
OH, the volume of users and the amount of memory isn't the issue.
How big is the database?|||Hi Guys,
Im not sure if you guys are referring to the size of the database file. Anyway the database that Notes is accessing is 13 gigabytes as of today and the database my VB app is using is 200 mb. I just ran performance monitor on the server and it says 90 MB of memory available and over 440 pages fault per second. It looks like SQL is taking up over 1.6 gig of memory. Yikes!|||You can perform an assessment using PERFMON while sQL server is in working state, which gives you full information.|||Lotus Notues and a 13gig database...hmmmm
Is he calling stored procedures or is everything in the application layer?
I'd be curious as to how it performs...
Oh, and SQL will grab as much memory as it needs...that's a good thing...
This is a dedicated SQL Server box...right?|||Yeah, it is a dedicated server box. We are using Lotus Notes through Citrix and I believe we have about 4 servers dedicated to Lotus Notes. His application is running fine so far but Im concern about the available memory on our SQL server (and espcecially since it started crashing and rebooting about once a week). The reason the SQL database Notes is accessing is fairly big is because of document archiving. I have a custom APP that produces customer statements and invoices in postcript format that we send to our customers. These documents then get archived each night in SQL server. The Notes App allows our customer reps to quickly find and view these documents. We send thousands and thousands of documents each month so this database is going to grow quickly. What do you guys think? Throw more memory in there? Im not a SQL DBA so I have no idea if 2 gig of memory is enough to handle 100+ users and like I said, it will be 300+ soon.|||There is no such thing as too much memory for MS-SQL! You can safely get that notion out of your head ;)
A lot depends on the architecture, how the Notes users are accessing the database, how your VB app works, etc.
Being the wild man that I am, I usually start my MS-SQL boxes at 8 Gb, then let somebody try to talk me down. I almost always manage to convince them there is no point in saving a few hundred dollars on RAM that would save them at least 10 hours of overtime each month.
-PatP|||Thanks Pat. I've convinced my boss to order more memory for the server. I'll see if I can get 8 gigs like you mentioned :D|||See...the thing of it is, is that Pat didn't ask you what version you're running or what the OS is...
This is kind of important|||See...the thing of it is, is that Pat didn't ask you what version you're running or what the OS is...
This is kind of important
Is that because Pat has a business on the side building bargain basement desktops?
;)|||See...the thing of it is, is that Pat didn't ask you what version you're running or what the OS is...
This is kind of importantAnd not only that, it also depends on whether the box will take 8GB or not. It's nice to sit there and say: "Yeah, memory is cheap!" Sure, what about a box itself? Maybe you can afford only the one expandable to 6GB? Where are you gonna put the other 2? In your ear?|||Maybe you can afford only the one expandable to 6GB? Where are you gonna put the other 2? In your ear?
Dude! that is so funny! "In his ear"?! .. I almost made a mess with the cup of cofee on my desk.|||See...the thing of it is, is that Pat didn't ask you what version you're running or what the OS is...
This is kind of importantGood point... Sometimes I miss details like that.
If jmondia is running NT 3.51 or earlier, then 8 Gb is a problem, since the OS has problems addressing that much memory. The same is true if they are running SQL 6.5 or earlier, although there used to be work-arounds for those problems from Micrsoft Professional Support Services.
I assumed that anyone planning to run 300+ simultaneous users would be running on server grade hardware (which by my definition has to support at least 8 Gb of RAM), with at least Windows 2000 and SQL 7. I shouldn't have taken those things for granted. Based on jmondia's response, it looks like I was safe making those assumptions though.
-PatP|||Man, I wish you were around when we were running our HMO on a 4-way with 4GB maxed out with 6.5 and NT (4.0 though, 3.51 wouldn't have taken it) PSS participated in setting up this server, all the specs were met...where did you get this idea that PSS would come up with a workaround for 6.5 to recognize even 2/3 of 8GB of RAM? Man, my veins (as Lindman once noticed) are about to pop even imagining this! We would have been all set with 8GB! Dreaming again?|||Dreaming again?A good TAM helps a lot.
-PatP
2 gigabyte of memory on it. Is this too low? My boss is expecting to have 300+ users on his Notes app when he rolls it out to our other branches. What would be the ideal amount of memory for 300+ users?Depends: will the 300+ users access the data simultaneously? 24/7? How much data will they transfer? How big is the database, and how is it used by the application? Are there any agreements with users concerning availablility/performance? What's acceptable to them?|||My boss has a Lotus Notes application accessing SQL server with about 60 users.
Nooooooooooooooooooooooooooooooooooooooo
AHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH
In the name of GOD WHY?
Got a lot of left over Notus Lotes developers who haven't made the jump?
Notestrix? Notespump? How are they talking to SQL Server?
OH, the volume of users and the amount of memory isn't the issue.
How big is the database?|||Hi Guys,
Im not sure if you guys are referring to the size of the database file. Anyway the database that Notes is accessing is 13 gigabytes as of today and the database my VB app is using is 200 mb. I just ran performance monitor on the server and it says 90 MB of memory available and over 440 pages fault per second. It looks like SQL is taking up over 1.6 gig of memory. Yikes!|||You can perform an assessment using PERFMON while sQL server is in working state, which gives you full information.|||Lotus Notues and a 13gig database...hmmmm
Is he calling stored procedures or is everything in the application layer?
I'd be curious as to how it performs...
Oh, and SQL will grab as much memory as it needs...that's a good thing...
This is a dedicated SQL Server box...right?|||Yeah, it is a dedicated server box. We are using Lotus Notes through Citrix and I believe we have about 4 servers dedicated to Lotus Notes. His application is running fine so far but Im concern about the available memory on our SQL server (and espcecially since it started crashing and rebooting about once a week). The reason the SQL database Notes is accessing is fairly big is because of document archiving. I have a custom APP that produces customer statements and invoices in postcript format that we send to our customers. These documents then get archived each night in SQL server. The Notes App allows our customer reps to quickly find and view these documents. We send thousands and thousands of documents each month so this database is going to grow quickly. What do you guys think? Throw more memory in there? Im not a SQL DBA so I have no idea if 2 gig of memory is enough to handle 100+ users and like I said, it will be 300+ soon.|||There is no such thing as too much memory for MS-SQL! You can safely get that notion out of your head ;)
A lot depends on the architecture, how the Notes users are accessing the database, how your VB app works, etc.
Being the wild man that I am, I usually start my MS-SQL boxes at 8 Gb, then let somebody try to talk me down. I almost always manage to convince them there is no point in saving a few hundred dollars on RAM that would save them at least 10 hours of overtime each month.
-PatP|||Thanks Pat. I've convinced my boss to order more memory for the server. I'll see if I can get 8 gigs like you mentioned :D|||See...the thing of it is, is that Pat didn't ask you what version you're running or what the OS is...
This is kind of important|||See...the thing of it is, is that Pat didn't ask you what version you're running or what the OS is...
This is kind of important
Is that because Pat has a business on the side building bargain basement desktops?
;)|||See...the thing of it is, is that Pat didn't ask you what version you're running or what the OS is...
This is kind of importantAnd not only that, it also depends on whether the box will take 8GB or not. It's nice to sit there and say: "Yeah, memory is cheap!" Sure, what about a box itself? Maybe you can afford only the one expandable to 6GB? Where are you gonna put the other 2? In your ear?|||Maybe you can afford only the one expandable to 6GB? Where are you gonna put the other 2? In your ear?
Dude! that is so funny! "In his ear"?! .. I almost made a mess with the cup of cofee on my desk.|||See...the thing of it is, is that Pat didn't ask you what version you're running or what the OS is...
This is kind of importantGood point... Sometimes I miss details like that.
If jmondia is running NT 3.51 or earlier, then 8 Gb is a problem, since the OS has problems addressing that much memory. The same is true if they are running SQL 6.5 or earlier, although there used to be work-arounds for those problems from Micrsoft Professional Support Services.
I assumed that anyone planning to run 300+ simultaneous users would be running on server grade hardware (which by my definition has to support at least 8 Gb of RAM), with at least Windows 2000 and SQL 7. I shouldn't have taken those things for granted. Based on jmondia's response, it looks like I was safe making those assumptions though.
-PatP|||Man, I wish you were around when we were running our HMO on a 4-way with 4GB maxed out with 6.5 and NT (4.0 though, 3.51 wouldn't have taken it) PSS participated in setting up this server, all the specs were met...where did you get this idea that PSS would come up with a workaround for 6.5 to recognize even 2/3 of 8GB of RAM? Man, my veins (as Lindman once noticed) are about to pop even imagining this! We would have been all set with 8GB! Dreaming again?|||Dreaming again?A good TAM helps a lot.
-PatP
Wednesday, March 7, 2012
Memory problem
I have an accounting program that is loaded on a server
and have 7 users. The program seem to have a memory leak.
The has server 1 gig of memory after the program runs for
a few hours the memory usage climes from 150meg used to
870meg, used all of witch is used by the sql instance. I
then have to have eveyone get out of the program stop the
sql server then restart it. Is there any thing out there
that can help me(beside buying a new accounting program
that is writen well),some type of auto resource recycling,
or auto restarting the sql instance with out causing the
users any to be down.
thank
Daniel Barnaby
Network Tech
Belton School District #124
Hi,
I guess that the SQL Server is configured to use Dynamic Memory
Allocations.
SQL Server memory will continue to grow as long as there is no RAM pressure
in
the machine. As long as SQL Server feels some pressure on memory pool, it
will
start to release memory for other applications use or to avoid swapping.
Try
setting the Min Server and Max Server memory to a know value then run the
same
applications. You should see the server memory grow to that limit and then
maintain on that level.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
and have 7 users. The program seem to have a memory leak.
The has server 1 gig of memory after the program runs for
a few hours the memory usage climes from 150meg used to
870meg, used all of witch is used by the sql instance. I
then have to have eveyone get out of the program stop the
sql server then restart it. Is there any thing out there
that can help me(beside buying a new accounting program
that is writen well),some type of auto resource recycling,
or auto restarting the sql instance with out causing the
users any to be down.
thank
Daniel Barnaby
Network Tech
Belton School District #124
Hi,
I guess that the SQL Server is configured to use Dynamic Memory
Allocations.
SQL Server memory will continue to grow as long as there is no RAM pressure
in
the machine. As long as SQL Server feels some pressure on memory pool, it
will
start to release memory for other applications use or to avoid swapping.
Try
setting the Min Server and Max Server memory to a know value then run the
same
applications. You should see the server memory grow to that limit and then
maintain on that level.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
Memory problem
I have an accounting program that is loaded on a server
and have 7 users. The program seem to have a memory leak.
The has server 1 gig of memory after the program runs for
a few hours the memory usage climes from 150meg used to
870meg, used all of witch is used by the sql instance. I
then have to have eveyone get out of the program stop the
sql server then restart it. Is there any thing out there
that can help me(beside buying a new accounting program
that is writen well),some type of auto resource recycling,
or auto restarting the sql instance with out causing the
users any to be down.
thank
Daniel Barnaby
Network Tech
Belton School District #124
hi Daniel,
"Daniel Barnaby" <anonymous@.discussions.microsoft.com> ha scritto nel
messaggio news:57e701c42d34$9e0bcfa0$a301280a@.phx.gbl...
> I have an accounting program that is loaded on a server
> and have 7 users. The program seem to have a memory leak.
> The has server 1 gig of memory after the program runs for
> a few hours the memory usage climes from 150meg used to
> 870meg, used all of witch is used by the sql instance. I
> then have to have eveyone get out of the program stop the
> sql server then restart it. Is there any thing out there
> that can help me(beside buying a new accounting program
> that is writen well),some type of auto resource recycling,
> or auto restarting the sql instance with out causing the
> users any to be down.
you must first understand memory usage by SQL Server...
in few words, SQL Server organize it's memory allocation in two distinct
regions, the "buffer pool" (BPool) and the "memory to leave" (MemToLeave)
regions. (I'm excluding use of AWE in order to provide easy understanding)
so... the BPool is the primary region SQL Server uses for it's internal
matter, while MemToLeave consists of the virtual memory space within the 1gb
user mode address space and the memory not used by the BPool.
when SQL Server starts, it begins calculating the upper limit the BPool can
reach... if no MaxMemory is set, this value will be set to the amount of the
physical memory or the size of the user mode address space (1gb) , minus the
size of the MemToLeave, whichever is less..
by default, MemToLeave is set to 384mb, 128mb of them are for worker thread
stacks and 256mb for allocation outside the BPool, such as memory for OLE-DB
providers, in process COM objects space and memory requirements and so on..
when MaxMemory value is explicitally set, this upper limit will only address
BPool region needs...
so only the address space of BPool pages is limited by this configuration
value, while SQL Server memory requirememts outside BPool allocation are not
limited this way...
but you are not limiting the resource, so SQL Server can reclaim that memory
(up to 1 gb, in our example) for it's uses, and it will perhaps release it
only under pressure by the OS claiming for additional memory, if it's the
case, else it will maintain that memory in order to cache pages and
execution plans...
it's quite a normal behaviour...
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
and have 7 users. The program seem to have a memory leak.
The has server 1 gig of memory after the program runs for
a few hours the memory usage climes from 150meg used to
870meg, used all of witch is used by the sql instance. I
then have to have eveyone get out of the program stop the
sql server then restart it. Is there any thing out there
that can help me(beside buying a new accounting program
that is writen well),some type of auto resource recycling,
or auto restarting the sql instance with out causing the
users any to be down.
thank
Daniel Barnaby
Network Tech
Belton School District #124
hi Daniel,
"Daniel Barnaby" <anonymous@.discussions.microsoft.com> ha scritto nel
messaggio news:57e701c42d34$9e0bcfa0$a301280a@.phx.gbl...
> I have an accounting program that is loaded on a server
> and have 7 users. The program seem to have a memory leak.
> The has server 1 gig of memory after the program runs for
> a few hours the memory usage climes from 150meg used to
> 870meg, used all of witch is used by the sql instance. I
> then have to have eveyone get out of the program stop the
> sql server then restart it. Is there any thing out there
> that can help me(beside buying a new accounting program
> that is writen well),some type of auto resource recycling,
> or auto restarting the sql instance with out causing the
> users any to be down.
you must first understand memory usage by SQL Server...
in few words, SQL Server organize it's memory allocation in two distinct
regions, the "buffer pool" (BPool) and the "memory to leave" (MemToLeave)
regions. (I'm excluding use of AWE in order to provide easy understanding)
so... the BPool is the primary region SQL Server uses for it's internal
matter, while MemToLeave consists of the virtual memory space within the 1gb
user mode address space and the memory not used by the BPool.
when SQL Server starts, it begins calculating the upper limit the BPool can
reach... if no MaxMemory is set, this value will be set to the amount of the
physical memory or the size of the user mode address space (1gb) , minus the
size of the MemToLeave, whichever is less..
by default, MemToLeave is set to 384mb, 128mb of them are for worker thread
stacks and 256mb for allocation outside the BPool, such as memory for OLE-DB
providers, in process COM objects space and memory requirements and so on..
when MaxMemory value is explicitally set, this upper limit will only address
BPool region needs...
so only the address space of BPool pages is limited by this configuration
value, while SQL Server memory requirememts outside BPool allocation are not
limited this way...
but you are not limiting the resource, so SQL Server can reclaim that memory
(up to 1 gb, in our example) for it's uses, and it will perhaps release it
only under pressure by the OS claiming for additional memory, if it's the
case, else it will maintain that memory in order to cache pages and
execution plans...
it's quite a normal behaviour...
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Monday, February 20, 2012
Memory leak or performance issue?
Dear all
My some users are complaining that when they restart their sql server
it works good for some time and then as time pass performance degrades.
Again they resrat and it works fine and as time goes it becomes slow.
Why when I restart it works fine and as time goes it becomes slow?
Is there any memory leak or any other problem for this?
Regards
AmishHi,
What is databaser server configuration hw and sw ?
- Windows version ?
- SQL Server version and SP ?
- How many memory do you want ?
- How many processors do you want ?
** * Esta msg foi útil pra você ? Então marque-a como tal. ***
Regards,
Rodrigo Fernandes
"amish" wrote:
> Dear all
> My some users are complaining that when they restart their sql server
> it works good for some time and then as time pass performance degrades.
> Again they resrat and it works fine and as time goes it becomes slow.
> Why when I restart it works fine and as time goes it becomes slow?
> Is there any memory leak or any other problem for this?
>
> Regards
> Amish
>
My some users are complaining that when they restart their sql server
it works good for some time and then as time pass performance degrades.
Again they resrat and it works fine and as time goes it becomes slow.
Why when I restart it works fine and as time goes it becomes slow?
Is there any memory leak or any other problem for this?
Regards
AmishHi,
What is databaser server configuration hw and sw ?
- Windows version ?
- SQL Server version and SP ?
- How many memory do you want ?
- How many processors do you want ?
** * Esta msg foi útil pra você ? Então marque-a como tal. ***
Regards,
Rodrigo Fernandes
"amish" wrote:
> Dear all
> My some users are complaining that when they restart their sql server
> it works good for some time and then as time pass performance degrades.
> Again they resrat and it works fine and as time goes it becomes slow.
> Why when I restart it works fine and as time goes it becomes slow?
> Is there any memory leak or any other problem for this?
>
> Regards
> Amish
>
Memory leak or performance issue?
Dear all
My some users are complaining that when they restart their sql server
it works good for some time and then as time pass performance degrades.
Again they resrat and it works fine and as time goes it becomes slow.
Why when I restart it works fine and as time goes it becomes slow?
Is there any memory leak or any other problem for this?
Regards
AmishHi,
What is databaser server configuration hw and sw ?
- Windows version ?
- SQL Server version and SP ?
- How many memory do you want ?
- How many processors do you want ?
** * Esta msg foi útil pra você ? Ent?o marque-a como tal. ***
Regards,
Rodrigo Fernandes
"amish" wrote:
> Dear all
> My some users are complaining that when they restart their sql server
> it works good for some time and then as time pass performance degrades.
> Again they resrat and it works fine and as time goes it becomes slow.
> Why when I restart it works fine and as time goes it becomes slow?
> Is there any memory leak or any other problem for this?
>
> Regards
> Amish
>
My some users are complaining that when they restart their sql server
it works good for some time and then as time pass performance degrades.
Again they resrat and it works fine and as time goes it becomes slow.
Why when I restart it works fine and as time goes it becomes slow?
Is there any memory leak or any other problem for this?
Regards
AmishHi,
What is databaser server configuration hw and sw ?
- Windows version ?
- SQL Server version and SP ?
- How many memory do you want ?
- How many processors do you want ?
** * Esta msg foi útil pra você ? Ent?o marque-a como tal. ***
Regards,
Rodrigo Fernandes
"amish" wrote:
> Dear all
> My some users are complaining that when they restart their sql server
> it works good for some time and then as time pass performance degrades.
> Again they resrat and it works fine and as time goes it becomes slow.
> Why when I restart it works fine and as time goes it becomes slow?
> Is there any memory leak or any other problem for this?
>
> Regards
> Amish
>
Subscribe to:
Posts (Atom)