Memory

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Memory

13 posts, Page 1 of 2

1 2 »»

Memory

Rate Topic

Display Mode

Topic Options

Author

Message

Nita Reddy

Posted Thursday, January 08, 2009 10:04 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, June 13, 2013 12:20 PM
Points: 423, Visits: 317

I have SQL 2005 64 bit Enterprise edition. I have set the memory to default.

SQL server is consuming 7 GB of Ram out of 10GB of ram.

I Did not check the 'AWE to allocate memory' (though it is 64 bit do I need to check that button on memory setting)

What should I do to esculate the problem I have to restart the SQL but again after 1 day the memory came up to 7 GB.

Thanks
Nita

Post #632583

DBA-640728

Posted Thursday, January 08, 2009 10:06 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, June 14, 2013 9:59 AM
Points: 498, Visits: 1,734

hi , how are you checking how much of RAM your sql server is consuming?

Post #632588

A.J. Wilbur

Posted Thursday, January 08, 2009 10:12 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 12:46 PM
Points: 1,002, Visits: 512

SQL Server is designed to use as much RAM as is available if you leave it on the default memory settings. Using 7 GB out of 10 GB is not a problem nessecarly. If you want to restrict how much memory SQL Server uses then change the memory config via sp_configure.

A.J.
DBA with an attitude

Post #632598

Nita Reddy

Posted Thursday, January 08, 2009 10:12 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, June 13, 2013 12:20 PM
Points: 423, Visits: 317

Through Task Manager and through DBCC Memorystatus

Post #632599

Nita Reddy

Posted Thursday, January 08, 2009 10:18 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, June 13, 2013 12:20 PM
Points: 423, Visits: 317

How much I should configure min and max so that SQL server won't utilise no more then 2 GB of memory

Post #632604

A.J. Wilbur

Posted Thursday, January 08, 2009 10:33 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 12:46 PM
Points: 1,002, Visits: 512

Set the minimum to whatever you like (0 would even work).

Set the max to 2048

A.J.
DBA with an attitude

Post #632632

Nita Reddy

Posted Thursday, January 08, 2009 10:41 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, June 13, 2013 12:20 PM
Points: 423, Visits: 317

Thanks

Post #632645

Nicholas Cain

Posted Thursday, January 08, 2009 7:58 PM

SSCrazy

Group: General Forum Members
Last Login: Friday, June 07, 2013 12:02 PM
Points: 2,007, Visits: 6,043

Why would you put 10GB of memory in a server and restrict SQL to 2GB? Unless you have a whole load of other things running on there.

Shamless self promotion - read my blog http://sirsql.net

Post #633036

Paresh Prajapati

Posted Thursday, January 08, 2009 10:24 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 323, Visits: 464

DBA (1/8/2009)

hi , how are you checking how much of RAM your sql server is consuming?

use below :

DBCC MEMORYSTATUS

------------------

--Host wise

select hostname,count(spid) as Total_Connection,sum(isnull(memusage,0)) as MemUsage from sys.sysprocesses
group by hostname order by count(spid) desc
----------
--Database wise

select db_name(dbid) as DatabaseName,count(spid) as Total_Connection,sum(isnull(memusage,0)) as MemUsage from sys.sysprocesses
group by db_name(dbid) order by count(spid) desc
--------

select
a.name,
connections = (select
count(*)
from
master..sysprocesses b
where
a.dbid = b.dbid),
blocked_users = (select
count(*)
from
master..sysprocesses b
where
a.dbid = b.dbid and
blocked <> 0),
total_memory = isnull((select sum(memusage)
from
master..sysprocesses b
where
a.dbid = b.dbid),0),
total_io = isnull((select sum(physical_io)
from
master..sysprocesses b
where
a.dbid = b.dbid),0),
total_cpu = isnull((select sum(cpu)
from
master..sysprocesses b
where
a.dbid = b.dbid),0),
total_waittime = isnull((select sum(waittime)
from
master..sysprocesses b
where
a.dbid = b.dbid),0),
dbccs = isnull((select count(*)
from
master..sysprocesses b
where
a.dbid = b.dbid and
upper(b.cmd) like '%DBCC%'),0),
bcp_running = isnull((select count(*)
from
master..sysprocesses b
where
a.dbid = b.dbid and
upper(b.cmd) like '%BCP%'),0),
backup_restore_running = isnull((select count(*)
from
master..sysprocesses b
where
a.dbid = b.dbid and
upper(b.cmd) like '%BACKUP%' or
upper(b.cmd) like '%RESTORE%'),0)
from
master.dbo.sysdatabases a

-----------------------------------

select DB = a.instance_name,
'DBCC Logical Scans' = a.cntr_value,
'Transactions/sec' = (select d.cntr_value
from
master..sysperfinfo d
where
d.object_name = a.object_name and
d.instance_name = a.instance_name and
d.counter_name = 'Transactions/sec'),
'Active Transactions' = (select case when i.cntr_value < 0 then 0
else i.cntr_value end
from
master..sysperfinfo i
where
i.object_name = a.object_name and
i.instance_name = a.instance_name and
i.counter_name = 'Active Transactions'),
'Bulk Copy Rows' = (select b.cntr_value
from
master..sysperfinfo b
where
b.object_name = a.object_name and
b.instance_name = a.instance_name and
b.counter_name = 'Bulk Copy Rows/sec'),
'Bulk Copy Throughput'= (select c.cntr_value
from
master..sysperfinfo c
where
c.object_name = a.object_name and
c.instance_name = a.instance_name and
c.counter_name = 'Bulk Copy Throughput/sec'),
'Log Cache Reads' = (select e.cntr_value
from
master..sysperfinfo e
where
e.object_name = a.object_name and
e.instance_name = a.instance_name and
e.counter_name = 'Log Cache Reads/sec'),
'Log Flushes' = (select f.cntr_value
from
master..sysperfinfo f
where
f.object_name = a.object_name and
f.instance_name = a.instance_name and
f.counter_name = 'Log Flushes/sec'),
'Log Growths' = (select g.cntr_value
from
master..sysperfinfo g
where
g.object_name = a.object_name and
g.instance_name = a.instance_name and
g.counter_name = 'Log Growths'),
'Log Shrinks' = (select h.cntr_value
from
master..sysperfinfo h
where
h.object_name = a.object_name and
h.instance_name = a.instance_name and
h.counter_name = 'Log Shrinks')
from
master..sysperfinfo a
where
a.object_name like '%Databases%'

_____________________________________________________________________________________________________________
Paresh Prajapati

+919924626601
http://paresh-sqldba.blogspot.com/
LinkedIn | Tweet Me | FaceBook | Brijj

Post #633082

nreddy.mail

Posted Thursday, January 08, 2009 10:38 PM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, August 14, 2010 1:10 PM
Points: 1, Visits: 25

If its Dedicated Server, then leave 2 GB to OS & rest can be made use by SQL Server.

Post #633095

« Prev Topic | Next Topic »

13 posts, Page 1 of 2

1 2 »»

Permissions