April 12, 2011 at 1:11 pm
we are running sql server 2005 X64 standard edition. Total server memory is 64 gb and sql is assigned 44 gb. Locked pages is also enabled with correct configuration for 64 bit std edition. All of sudden i see that under task manager sqlserver.exe is using only 500mb? It was showing 45 gb until yesterday. I know task manager is not the right tool but why would it change all of sudden? Rebooting didn't help at all, still shows 500 mb. And yes application is running terribly slow than normal. I am totally lost, where do i look for issues?
April 12, 2011 at 1:17 pm
Hi,
what shows the task manager. How much memory is free of the physical memory? Because the sql server would only take physical memory and not page file memory.
Is the server is virtualized? Then perhaps the host system can't give more memory.
Greetings
Patrick Fiedler
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 12, 2011 at 1:54 pm
April 12, 2011 at 7:50 pm
Patrick_Fiedler (4/12/2011)
Hi,what shows the task manager. How much memory is free of the physical memory? Because the sql server would only take physical memory and not page file memory.
Is the server is virtualized? Then perhaps the host system can't give more memory.
Greetings
Patrick Fiedler
Thanks . No sql server is not VM'd. It is standalone.Total server memory is 64 gb and there is cap of 42 gb on sql server. From task manager 'Total Memory 66054796 , Available 7304728'.
April 13, 2011 at 2:37 am
Hey Icon, recently rebooted? I'm going with Occam's Razor here.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 13, 2011 at 2:47 am
Do not use task manager to check SQL's memory usage. It does not show accurate values if you are using locked pages, as the post that Nils referenced explains.
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
April 13, 2011 at 3:14 am
Perhaps some changes to sql server are made?
Look at the instance reports -> server dashboard and then non-default configurations.
Execute following statements to get details of the memory usage:
-- Buffer cache hit ratio
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [BufferCacheHitRatio]
FROM (SELECT *, 1 x FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio') a
CROSS JOIN
(SELECT *, 1 x FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base') b
-- Memory Manager
select*
fromsys.dm_os_performance_counters
whereobject_name like '%:Memory Manager%'
and post the results.
Greetings
Patrick Fiedler
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 13, 2011 at 9:31 am
GilaMonster (4/13/2011)
Do not use task manager to check SQL's memory usage. It does not show accurate values if you are using locked pages, as the post that Nils referenced explains.
That makes sense. Thanks
April 13, 2011 at 10:53 am
Patrick_Fiedler (4/13/2011)
Perhaps some changes to sql server are made?Look at the instance reports -> server dashboard and then non-default configurations.
Execute following statements to get details of the memory usage:
-- Buffer cache hit ratioSELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [BufferCacheHitRatio]
FROM (SELECT *, 1 x FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio') a
CROSS JOIN
(SELECT *, 1 x FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base') b
-- Memory Manager
select*
fromsys.dm_os_performance_counters
whereobject_name like '%:Memory Manager%'
and post the results.
Greetings
Patrick Fiedler
Thanks. Attached are the results please take a look
April 14, 2011 at 7:15 am
First, has sql server the AWE flag enabled although it is a 64-bit machine?
I have looked at the results:
Target Server Memory and Total Server Memory is the same -> that's good -> sql server has enough memory and he has 53 GB allocated
Memory Grants Outstanding should be lower i think -> that would explain the performance issues
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 14, 2011 at 12:37 pm
NO AWE is not enabled.I have enabled locked pages in memory though. Could this be an issue?
April 14, 2011 at 1:03 pm
No, usually not.
Have you checked the disk performance of the sql server? Wait time? Read/Write Bytes / sec? Reads/Writes / sec?
So we should be sure what the source of the performance issue is.
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 14, 2011 at 1:22 pm
With any 64-bit install of sqlserver you must set the max server memory configuration setting for the instance! or it will eat up all your ram.
Set it to an acceptable amount of MB to support your db instance.
Because of your lock pages in memory grant, it will not release the allocated memory to other services requesting ram.
Any other instances on that same box/node ?
The restart scenario surely makes sense.
Are there any other services using the same account that is used for your sqlinstance ? (LPIM !!)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 14, 2011 at 1:25 pm
@alzdba: But could this be the reason if the sql server has performance issues? The server has 7 GB free physical memory.
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 14, 2011 at 1:29 pm
ALZDBA (4/14/2011)
With any 64-bit install of sqlserver you must set the max server memory configuration setting for the instance! or it will eat up all your ram.Set it to an acceptable amount of MB to support your db instance.
Because of your lock pages in memory grant, it will not release the allocated memory to other services requesting ram.
Any other instances on that same box/node ?
The restart scenario surely makes sense.
Are there any other services using the same account that is used for your sqlinstance ? (LPIM !!)
Yes max memory is set to 48 gb out of 64 gb. Yes the same account is used for all sql srvcs(agent,ssis,fte) ?
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply