August 3, 2010 at 7:28 am
Hi there,
We've got a 64bit SQL2005 server, and it has been allocated max 16GB memory.
What perfmon counters do I need to capture to determine how much of this 16GB sql server is using?
We need to know becuase we want to reduce the RAM allocation for SQL on that box, and therefore need to know how much of that 16GB allocation is uses.
Im aware that SQL will take all the RAM that it is allocated, but what I want to know is how much of that it is actually using, and how much less RAM allocation it could get away with.
Cheers!
August 3, 2010 at 1:35 pm
The memory allocation depends on the load that SQL Server is going to handle. This article[/url] explains it better.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
August 3, 2010 at 2:51 pm
Try this query:
select
[OBJECT_NAME]= left(quotename(rtrim(a.[OBJECT_NAME]))+
quotename(rtrim(a.[COUNTER_NAME]))+
case
when rtrim(a.[INSTANCE_NAME]) = '' then ''
else quotename(rtrim(a.[INSTANCE_NAME])) end
,60),
[Memory GB]= convert(decimal(10,3),round((a.[CNTR_VALUE]+0.000)/(1024.000*1024.000),3)),
[Memory MB]= convert(decimal(10,3),round((a.[CNTR_VALUE]+0.000)/(1024.000),3))
from
master.dbo.sysperfinfo a
where
a.[OBJECT_NAME] in ('SQLServer:Memory Manager')and
a.[COUNTER_NAME] in ('Target Server Memory (KB)','Total Server Memory (KB)' )
Results:
OBJECT_NAME Memory GB Memory MB
------------------------------------------------------------ ------------ ------------
[SQLServer:Memory Manager][Target Server Memory (KB)] 14.813 15168.000
[SQLServer:Memory Manager][Total Server Memory (KB)] 14.813 15168.000
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply