January 8, 2009 at 10:04 am
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
January 8, 2009 at 10:06 am
hi , how are you checking how much of RAM your sql server is consuming?
January 8, 2009 at 10:12 am
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
January 8, 2009 at 10:12 am
Through Task Manager and through DBCC Memorystatus
January 8, 2009 at 10:18 am
How much I should configure min and max so that SQL server won't utilise no more then 2 GB of memory
January 8, 2009 at 10:33 am
Set the minimum to whatever you like (0 would even work).
Set the max to 2048
A.J.
DBA with an attitude
January 8, 2009 at 10:41 am
Thanks
January 8, 2009 at 7:58 pm
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.
January 8, 2009 at 10:24 pm
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%'
January 8, 2009 at 10:38 pm
If its Dedicated Server, then leave 2 GB to OS & rest can be made use by SQL Server.
January 10, 2009 at 5:40 am
AWE is not required, not available, on a 64bit system. It was included as a workaround to solve the maximum 2GB address space available to 32bit processes.
To limit your SQL Server, in your SQL Server Instance properties, goto the Memory tab and enter the maximum amount of RAM you wish to use in KB.
If you want to ensure that SQL Server will manage the memory it is using and not respond to Windows requests for paging, enable the Lock Pages in Memory privilege for the service account that is running the SQL Server Database Engine service.
--
Andrew Hatfield
July 14, 2010 at 3:17 pm
How do you gauge the memusage in sysprocesses? how many pages in KB?
July 14, 2010 at 3:21 pm
Please post your question in a new thread. Thank you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply