October 9, 2013 at 7:44 pm
Hello All,
Responses will be highly appreciated.
I have a problem since 4 weeks on SQL Server 2005 Cluster environment.
We have Prod box with SQL Server 2005 64 bit on Win 2003, 64bit.
Its a dedicated box, but we have 6 applications hits the instance.
Total RAM = 36 GB
SQL Server Min Memory = 0
SQL Server Max Memory = 32 GB
2 weeks back I recycled SQL Services and failed over to node B.
(I think recycling the services is not the solution every time)
Now B node also with the same utilization 98% Memory of total.
If I see in Task Manager Total Memory utilization is 98%..
SQL Server Memory usage is only 4 GB, in Task Manager Performance.
I tried researching in some areas, why this became strange in a period of 30 days.
Task manager : only 4 GB usage by SQL Server service
Perfmon : pages/sec - very normal (not even 20)
Page Life Expectancy (PLE) Counter
object_name counter_name cntr_value
SQLServer:Buffer Manager Page life expectancy 29950
Memory visible to the Operating System
Physical Memory_MB Physical Memory_GB Virtual Memory GB
--------------------------------------- --------------------------------------- ---------------------------------------
36861 36 8192
Buffer Pool Usage at the Moment
BPool_Committed_MB BPool_Commit_Tgt_MB BPool_Visible_MB
--------------------------------------- --------------------------------------- ---------------------------------------
32764.000000 32764.000000 32764.000000
Total Memory used by SQL Server Buffer Pool as reported by Perfmon counters
Mem_KB Mem_MB Mem_GB
-------------------- --------------------------------------- ---------------------------------------
33550336 32764.000000 31.996093750
Memory needed as per current Workload for SQL Server instance
Mem_KB Mem_MB Mem_GB
-------------------- --------------------------------------- ---------------------------------------
33550336 32764.000000 31.996093750
===============================================================================================
Buffer top % for each DB
db_name db_buffer_pagesdb_buffer_MBdb_buffer_percent
DBA_Aust 2029819 15857 48.401
DBA_Trans_Log 713903 5577 17.023
DBA_AMNS 336962 2632 8.035
DBA_Master 161839 1264 3.859
tempdb 129825 1014 3.096
===============================================================================================
Finally I realized that Buffer pool is filling very fast and it increased heavy.
Can anyone please help me, how to fix this issues ?
Any settings to be done etc.
Cheers,
- Win
"Dont Judge a Book by its Cover"
October 10, 2013 at 3:53 am
- Win (10/9/2013)
Finally I realized that Buffer pool is filling very fast and it increased heavy.Can anyone please help me, how to fix this issues ?
Any settings to be done etc.
max memory setting here ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 10, 2013 at 7:28 pm
Buffer Pool Usage at the Moment
BPool_Committed_MB BPool_Commit_Tgt_MB BPool_Visible_MB
--------------------------------------- --------------------------------------- ---------------------------------------
32764.000000 32764.000000 32764.000000
Based on the above findings, I could determine its buffer pool issue.
But, I am confused of task Manager SQL Server service memory usage, very less.
Is there any other way to determine and check, or can we bring down the Memory usage when server is not busy ?
Cheers,
- Win
"Dont Judge a Book by its Cover"
October 11, 2013 at 2:32 am
Don't use Task Manager to check SQL's memory usage, it shows incorrect values.
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
October 11, 2013 at 2:46 am
Thanks for your reply..
Yes, I dont depend on Task Manager.
Hence I verified all the possible areas and now its time to fix the issues.
Could you please help me with any suggestions ?
Cheers,
- Win
"Dont Judge a Book by its Cover"
October 11, 2013 at 4:36 am
What, specifically, are the issues?
Buffer pool should fill fast, that's by design (SQL will ramp up buffer pool usage after start up)
SQL will use as much memory as it can, that's by design.
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 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply