• RSS
  • Twitter
  • FaceBook

Welcome to ISAserver.org

Forums | Register | Login | My Profile | Inbox | RSS RSS icon | My Subscription | My Forums | Address Book | Member List | Search | FAQ | Ticket List | Log Out

sqlsvr is a memory hog

Users viewing this topic: none

Logged in as: Guest
  Printable Version
All Forums >> [ISA Server 2004 Firewall] >> Logging and Reporting >> sqlsvr is a memory hog Page: [1]
Login
Message << Older Topic   Newer Topic >>
sqlsvr is a memory hog - 23.Dec.2004 2:43:00 PM   
iwannabfishn

 

Posts: 68
Joined: 21.Jun.2004
Status: offline
I log to the msde database for ISA 2004. I noticed that the SQL server process is eating up about 450MB of memory. Is this normal? Is there anything I can do to lessen this? I do not want to add more memory unless I have to. Thanks!
Post #: 1
RE: sqlsvr is a memory hog - 20.Jan.2005 11:54:00 AM   
millrise

 

Posts: 5
Joined: 7.Feb.2004
From: UK
Status: offline
You can stop sql server hogging memory, but your going to need some client tools to do it. You can do this easily by using SQL Enterprise manager. This comes with all retail versions of sql server. Memory allocation is dynamic out of the box. That means the SQL server engine determines that your database requires that much memory to function. Be warned setting a memory allocation that is too low will cause the server to slow down significantly when responding to read requests. I'd recommend logging the requests to a separate SQL server and stopping the sql serevr service on your ISA server.

(in reply to iwannabfishn)
Post #: 2
RE: sqlsvr is a memory hog - 21.Jan.2005 1:32:00 AM   
Collide

 

Posts: 25
Joined: 16.Nov.2001
Status: offline
You can add more memory to the system but MSDE will probably eat that up too. I have an ISA server with 2GB of memory and the sqlservr process would consume 1.7GB after only 3 days of uptime. There's obviously a memory leak problem with MSDE. I have uninstalled it until MS comes out with a fix or a patch.

(in reply to iwannabfishn)
Post #: 3
RE: sqlsvr is a memory hog - 22.Jan.2005 1:35:00 AM   
grinn253

 

Posts: 76
Joined: 12.Jul.2004
From: Seattle
Status: offline
Just for reference, we have a 4GB ISA system with the /3GB switch utilized, and the sqlservr process consumes ~1.8 GBs routinely. Doesn't bother us much though cuz 2.2 GBs are still available. [Wink]

Laters!
Edgardo

(in reply to iwannabfishn)
Post #: 4
RE: sqlsvr is a memory hog - 22.Jan.2005 6:47:00 AM   
Collide

 

Posts: 25
Joined: 16.Nov.2001
Status: offline
So I guess the solution is to put 4GB of memory in every ISA firewall. Nice. [Wink]

(in reply to iwannabfishn)
Post #: 5
RE: sqlsvr is a memory hog - 5.Feb.2005 5:15:00 PM   
liuty2006

 

Posts: 88
Joined: 30.Jun.2004
Status: offline
try this one -- netfee
http://www.netfeesoftware.com/NetFee/Index.htm

(in reply to iwannabfishn)
Post #: 6
RE: sqlsvr is a memory hog - 17.Feb.2005 4:56:00 PM   
myxiplx

 

Posts: 136
Joined: 16.Mar.2001
Status: offline
First of all apologies for bouncing an old thread, but I've just had this problem myself and may have found a solution.

There is a 'max server memory' option in SQL and MSDE, with the default being 2147483647Mb! However it is possible to change this using the osql utility installed with MSDE. I haven't run this long enough on our server to test, but believe it will work according to Microsoft's documentation.

It appears that by default SQL Server, and MSDE will grab as much memory as they can since they assume that they are on a dedicated database server. MS seem to imply that the 'max server memory' parameter should be set if the server isn't exclusively a SQL server here. Microsoft also have more details on the max and min memory parameters.

I then found this page with details of the scripts to configure these paramaters, and this knowledgebase article explaining how to use osql. With these two articles, it is possible to reset the 'max server memory' parameter, with the change taking effect immediately.

You will need to know the SQL instance name to use osql and this can be found in the Services list. In my case, the service was named MSSQL$MSFW and MSFW is the instance name.

Two scripts are shown below, one to check your server's max memory setting, and one to change the value.

To view the memory on your server, copy the following commands to a text file (eg. c:\checkmemory.sql):
code:
USE master
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

USE master
EXEC sp_configure 'max server memory (MB)'

USE master
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE

You can now check the 'max server memory' setting on your server by running the following from a command prompt on the server:
code:
osql -E -S servername\MSFW -i c:\checksqlmemory.sql

To change the max memory size, use a script similar to the following (this sets it to 256Mb):

code:
USE master
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

USE master
EXEC sp_configure 'max server memory (MB)', 256
RECONFIGURE WITH OVERRIDE

USE master
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE

Again, save that to a text file (eg 'c:\setservermemory.sql') and run it with the command:

code:
osql -E -S servername\MSFW -i c:\setchecksqlmemory.sql

Ross

(in reply to iwannabfishn)
Post #: 7
RE: sqlsvr is a memory hog - 3.Mar.2005 1:28:00 PM   
eneto

 

Posts: 9
Joined: 1.Mar.2005
Status: offline
How to tell how much memory should de MSDE be truncated to ?

Regards

(in reply to iwannabfishn)
Post #: 8
RE: sqlsvr is a memory hog - 3.May2005 5:10:00 PM   
xistential

 

Posts: 3
Joined: 2.May2005
From: Indy
Status: offline
Thanx, I am trying your script now. I set my max to 128mb though.

(in reply to iwannabfishn)
Post #: 9
RE: sqlsvr is a memory hog - 5.May2005 1:17:00 AM   
tshinder

 

Posts: 50013
Joined: 10.Jan.2001
From: Texas
Status: offline
quote:
Originally posted by myxiplx:
First of all apologies for bouncing an old thread, but I've just had this problem myself and may have found a solution.

There is a 'max server memory' option in SQL and MSDE, with the default being 2147483647Mb! However it is possible to change this using the osql utility installed with MSDE. I haven't run this long enough on our server to test, but believe it will work according to Microsoft's documentation.

It appears that by default SQL Server, and MSDE will grab as much memory as they can since they assume that they are on a dedicated database server. MS seem to imply that the 'max server memory' parameter should be set if the server isn't exclusively a SQL server here. Microsoft also have more details on the max and min memory parameters.

I then found this page with details of the scripts to configure these paramaters, and this knowledgebase article explaining how to use osql. With these two articles, it is possible to reset the 'max server memory' parameter, with the change taking effect immediately.

You will need to know the SQL instance name to use osql and this can be found in the Services list. In my case, the service was named MSSQL$MSFW and MSFW is the instance name.

Two scripts are shown below, one to check your server's max memory setting, and one to change the value.

To view the memory on your server, copy the following commands to a text file (eg. c:\checkmemory.sql):
code:
USE master
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

USE master
EXEC sp_configure 'max server memory (MB)'

USE master
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE

You can now check the 'max server memory' setting on your server by running the following from a command prompt on the server:
code:
osql -E -S servername\MSFW -i c:\checksqlmemory.sql

To change the max memory size, use a script similar to the following (this sets it to 256Mb):

code:
USE master
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

USE master
EXEC sp_configure 'max server memory (MB)', 256
RECONFIGURE WITH OVERRIDE

USE master
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE

Again, save that to a text file (eg 'c:\setservermemory.sql') and run it with the command:

code:
osql -E -S servername\MSFW -i c:\setchecksqlmemory.sql

Ross

Hi Ross,

Great stuff!
Thanks!
Tom

(in reply to iwannabfishn)
Post #: 10
RE: sqlsvr is a memory hog - 27.Jun.2005 4:51:00 AM   
jparnell

 

Posts: 5
Joined: 19.Jan.2005
From: England
Status: offline
Hi, I'm sorry to add to this again, but I'm also having sqlsrv.exe memory problems on our ISA 2004 server. I want to use the above script to limit the sqlsvr.exe memory usage to 256MB - i'm a little cautious though because I only have a live server to do this on.
Can anyone confirm the above script works ok, and what happens when the sqlsvr.exe memory usage reaches it's limit - are there any side effects?Thanks

(in reply to iwannabfishn)
Post #: 11
RE: sqlsvr is a memory hog - 27.Jun.2005 3:50:00 PM   
iwannabfishn

 

Posts: 68
Joined: 21.Jun.2004
Status: offline
I can confirm that the script from "myxiplx" will work because I tried it on our live server and we have had no noticable effect. We have run it like this for a couple of months. I am sure the solution that Tom Shinder found will also work. It seems like the same script. The way I did it you had to install the enterprise manager from the full SQL server and Tom's way uses a utility built into the MSDE version.

(in reply to iwannabfishn)
Post #: 12
RE: sqlsvr is a memory hog - 19.Jul.2005 10:54:00 AM   
rino01

 

Posts: 69
Joined: 1.Jul.2005
From: Stockholm / Sweden
Status: offline
Well it works if you don't have this problem as i have.

The MSDE instance in SQL Server Service Manager appears as "Not Connected" after you install ISA Server 2004

http://support.microsoft.com/default.aspx?scid=kb;en-us;840554

So I can't connect to the SQL service and use the scripts. Does anyone know if the SQL Enterprise Interface will work even if the osql won't?

(in reply to iwannabfishn)
Post #: 13
RE: sqlsvr is a memory hog - 19.Jul.2005 11:50:00 AM   
LLigetfa

 

Posts: 2187
Joined: 10.Aug.2004
From: fort frances.on.ca
Status: offline
I had the same issue mentioned in the KB and had no problem to connect by providing the server and instance name.

(in reply to iwannabfishn)
Post #: 14
RE: sqlsvr is a memory hog - 20.Jul.2005 2:39:00 AM   
rino01

 

Posts: 69
Joined: 1.Jul.2005
From: Stockholm / Sweden
Status: offline
Ow ok, strange. I get a error everytime i try.

Here is what i do.

osql -E -S MSSQL$MSFW\MSFW -i c:\temp\checkmemory.sql

Error message:

[DBNETLIB]SQL Server does not exist or access denied.
[DBNETLIB]ConnectionOpen <Connect<>>

That is what i get. I dont understand what i do wrong then [Frown]

[ July 20, 2005, 02:41 AM: Message edited by: Rickardn ]

(in reply to iwannabfishn)
Post #: 15
RE: sqlsvr is a memory hog - 1.Aug.2005 3:45:00 PM   
ClintD

 

Posts: 1848
Joined: 26.Jan.2001
From: Keller, TX
Status: offline
Change this...

quote:
osql -E -S MSSQL$MSFW\MSFW -i c:\temp\checkmemory.sql
to this...

quote:
osql -E -S %YourISAServer'sHostname%\MSFW -i c:\temp\checkmemory.sql


[ August 01, 2005, 03:45 PM: Message edited by: ClintD ]

(in reply to iwannabfishn)
Post #: 16
RE: sqlsvr is a memory hog - 17.Oct.2005 4:06:00 PM   
Guest
Hello:
If you don't want to use OSQL.EXE or the SQL Query Analyzer to modify these memory settings, there's an easier-to-use script for this at www.ISAscripts.org named "ISA_MSDE_Max_Memory.vbs".

(in reply to iwannabfishn)
  Post #: 17
RE: sqlsvr is a memory hog - 24.Oct.2005 1:55:00 PM   
brettski

 

Posts: 1
Joined: 24.Oct.2005
From: Chicago, IL
Status: offline
Yes, SQL is a pig, but I think there is an underlying reason why SQL is taking all of this memory. I setup a database for my System Administrator to log ISA activity and noticed that the create table scripts he sent me didn't have any primary keys defined.

Without a primary key, MSSQL must do a full table scan each time it needs to access a record. Worse yet, is there are two defined indexes on the table. Without a primary key present, each time there is an insert (which is the majority of the transactions here) the engine has to scan the entire table to determin where to put the new record. This is extremely CPU intensive and uses up memory. Specially as the table grows.

For our table, FirewallLog and WebProxyLog I created a 'dummy' key called xKey with a type of integer and an identity field (auto increment). I then made this column the Primary Key.

Once we have more data in our logging database, I will see if I can create a true primary key from the data itself, which is always a better practice and more efficient.

Another thing to consider is to set transaction logging for the database to simple. I doubt that any of you will be rebuilding the database from transactions logs, so why spend the cpu cycles creating them?

Happy hacking,

Brettski

(in reply to iwannabfishn)
Post #: 18

Page:   [1] << Older Topic    Newer Topic >>
All Forums >> [ISA Server 2004 Firewall] >> Logging and Reporting >> sqlsvr is a memory hog Page: [1]
Jump to:

New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts