Use of RAM by SQL Server

[boligoma]

The RAM usage for the process sqlserv.exe in the task manager of windows takes around 1.5GB to 1.7GB when there's no jobs, queries or dts running.

If i restart the SQL Server service, the memory consumes 60 MB of memory, but, when i run any query, dts, or job the memory starts to grow until 1.5GB or more.

Somebody have any idea of what is happening?

 

[TitleistDBA]

SQL server will alocate the largest memory pool posible to prevent page swapping. You can set a max and min memory but it's not recommended.

http://www.acushnetcompany.com

 

[boligoma]

can this affect the performance of queries petitions, dts, or jobs?

 

[TitleistDBA]

No, In fact this will help your performance. The less page swapping the better performance will be. Keep in mind that SQL server will manage its memory dynamically. The reason you see it using 60mg on startup is because it doesn't need any more than that. It will take memory as it needs it from the memory pool and deallocate it if it doesn't need it.

http://www.acushnetcompany.com

 

[Tessai2003]

Hello,

How can you configure manually the use of RAM by your SQL server then ?

I say manually because for some reason I cannot use grafical interface :-(

Thanks for your help,

Tessai

 

[TitleistDBA]

sp_configure "show advanced option", 1
go
sp_configure "max server memory" 512
go
RECONFIGURE WITH OVERRIDE
go
If you run this from query analyzer you can set your max or min memory options. To set the min option change max to min.

http://www.acushnetcompany.com

 

[TitleistDBA]

I forgot to mention that the number 512 is the max ammount of mem used. You will have to change that number to the ammount you want to assign.

http://www.acushnetcompany.com

 

[Tessai2003]

:-D

Actually I'm like er... a dummy in SQL and I used it for my ePolicy anti-virus server...

So it's not really a server I think... the process is though sqlsrvr.exe but I do not see any server management tools.. There is only the icon next to my time wich shows me that it is running but that's all ...

And by the way I tried to find a "sp_configure" somewhere but I did not find it...

Sorry for the misunderstanding

 

[TitleistDBA]

Sorry, I should have been more clear. You need to run those commands from query analyzer. But you will need system admin rights to do so. Do you know what drive sql is installed on? I would look for query analyzer in the following directory.

c:\program fils\microsofy sql server\80\tools

Might I ask why you need to change the default memory setting? It is really not recommended. If you don't allocate enough memory you will get allot of page swapping and your querys and jobs could slow down.

http://www.acushnetcompany.com

 

[Tessai2003]

Dear,

Actually I do not see the query analyzer, do you have the name of that thing (.exe) ? for the sql drive... have no idea of what it could be :-(

If I want to modify the RAM it's because it is taking all my RAM... quite annoying if I want to work with my ePolicy Manager... it's not needed to take all my RAM by the way there are not so much queries..

Thanks,

Tessai

 

[TitleistDBA]

look for isqlw.exe

http://www.acushnetcompany.com

 

[Tessai2003]

Erf... I do not have that isqlw.exe

Is there any other way to modify that settings trough a special file ?

Or can I use another of our SQL servers to connect to mine trough the network ?

Thanks again

 

[TitleistDBA]

You can connect to your server from any computer or server that has client tools installed.
If you know how and have the option use enterprise manager to change your memory settings. Do you know how to register your server in Enterprise Manager? Do you know where the memory options are? If not let me know and I can walk you through it.

http://www.acushnetcompany.com

 

[zhavic]

You can also use 'isql.exe' tool, ( this is instaled everywhere, where sql server is installed I thing )


run it with 'isql.exe -U sa' ( instead of 'sa' you can use another login name, but that has admin rights )

than it ask you for password - type it
than type commands, that 'TitleistDBA' posts
after that type 'exit'

for more options of 'isql.exe' type 'isql.exe /?'

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[Tessai2003]

Hello,

The problem is that I don't have so much tools on my server... my software is using a client engine and I have no isql.exe .. ?

Is there no specific file that contains the RAM information ?

Thnanks a lot

 

[TitleistDBA]

No, SQL stores is config information in the master database. So you need the tools to change it. Do you have a install disk? Can you install the tools?

http://www.acushnetcompany.com

 

[Tessai2003]

Err bear with me I don't know a thing in the SQL world ... :-/

Those tools can be installed from a sql server 2000 CD that's it ? without having to modify something in he existing database client ?

Thanks

 

[Glyndwr]

Tessai2003 ,

you can, if you wish query the db using vbscipt, following example will show you , providing you have sufficient db privelegs, you can easily modify this to update values also

Set cn = CreateObject ("adodb.connection")
Set rs = CreateObject ("adodb.recordset")

cn.ConnectionString ="provider=sqloledb;data source=myServer\myInstance;initial catalog=pubs;trusted_connection=yes"
cn.Open 

Set rs = cn.Execute ("exec sp_configure")
For Each fld In rs.Fields 
   	fldlist = fldlist & fld.name & Chr(9)
Next
WScript.Echo fldlist
Do While Not rs.eof 
   collist= ""
   For Each col In rs.Fields 
   	collist = collist & col & Chr(9)
   next
	WScript.Echo collist
	rs.MoveNext 
Loop
	


Set rs = Nothing
Set cn = nothing

Glyndwr

 

[TitleistDBA]

Yes, If you get the SQL 2000 CD you can install the tools on your workstation. Then register your sql server.

http://www.acushnetcompany.com