December 30, 2008 at 12:35 pm
Hi,
We have SQL Server 2005 EE x64 a\a\p cluster setup on windows 2003 EE x64.
Node1 has i insatnce
Node2 passive
Node3 Has 4 insatnces.
We 16 GB RAM on all nodes.
On node1, we have shareponit databases.So share pont appication is ruuning in node1 default instance.
In this, SQL Server is always taking 15GB memory, from 8am to 5pm and also from 5pm to 8am. When no users are using after off peak hours also it is taking 15GB .
So could you plz advice what are items I need to collect from SQL Profiles to know what actually taking these resources after off peak hours and peak hrs too.
Thank You
December 30, 2008 at 12:54 pm
oh dear madhu you are in a pickle arent you.
what do you have min and max memory set to on node 1?
have you thought about setting up a sql trace to see what is hitting the database and maybe tie this in with a perfmon session at the same time
the important thing is not to panic and create multiple posts across the forums, it makes it harder for people to help you (and usually unwilling too 😉 )
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 30, 2008 at 1:02 pm
Let's be fair, the max and min might not be the same.
It's not like anything else on the server is going to ask for memory, so it's just going to hang on to it as long as possible.
At this point the entire instance is probably pinned.
December 30, 2008 at 1:16 pm
I still did not set the min and Max memory values. They are set to default values.Because sql server itself taking 15GB if I reduce the max value to 14GB leaving 2 GB to windows there will be memory pressure right? So before setting the min and max values I would like to know what are process taking the resources all the time.
December 30, 2008 at 1:28 pm
Just curious, what's the CPU usage and the disk i/o? if both are pinned then you have a major issue. If both are not pinned then I don't see anything wrong. I normally tune SQL to take MOST of the RAM on a SQL server. I leave maybe 2-3GB for the OS and the rest for SQL. I set the starting for 50% of th ram and max up to (2GB or 3GB less than the amount of physical RAM). during the day if SQL uses the MAX I set it will not release the RAM for the OS but will reuse the space for itself.
December 30, 2008 at 1:38 pm
Sorry, I should have clarified the "pinned" reference. It was a semi-sarcastic comment, something that puts any SQL skills I have to shame.
December 30, 2008 at 2:11 pm
What you describe is completely normal behavior for SQL Server. When SQL Server allocates memory, it does not give it back unless there is pressure from the OS.
Why are you concerned about this? Are you seeing some performance issue?
December 30, 2008 at 4:23 pm
also remembering from another of your posts,
did you set the "lock pages in memory" policy on this server?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 30, 2008 at 11:04 pm
madhu.arda (12/30/2008)
Hi,We have SQL Server 2005 EE x64 a\a\p cluster setup on windows 2003 EE x64.
Node1 has i insatnce
Node2 passive
Node3 Has 4 insatnces.
We 16 GB RAM on all nodes.
On node1, we have shareponit databases.So share pont appication is ruuning in node1 default instance.
In this, SQL Server is always taking 15GB memory, from 8am to 5pm and also from 5pm to 8am. When no users are using after off peak hours also it is taking 15GB .
So could you plz advice what are items I need to collect from SQL Profiles to know what actually taking these resources after off peak hours and peak hrs too.
Thank You
I think u are very clear which database or host taking more memory , 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%'
December 30, 2008 at 11:12 pm
hi i want to know that whateever memusage this query shown is that in mb or in kb can u please tell me that
Raj Acharya
December 31, 2008 at 1:23 am
raj acharya (12/30/2008)
hi i want to know that whateever memusage this query shown is that in mb or in kb can u please tell me that
it is there page in memory.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply