June 27, 2014 at 6:45 am
Hi
My SQL box has 34 GB RAM and SQL instance is set to use max memory of 26GB, leaving 8 GB for OS. There is no other application running on this box apart from SQL. Since couple of weeks , the task manager has started showing 100% physical memory usage. But If I go to processes and add all memory usage, it is not more than 15 GB including SQL server. Nothing else is running on server. If I restart it, it again reaches to 99-100% in few hours. Hardly 140 MB is shown as free by task manager and perfmon.
This is causing my system to respond pretty slow.
Can you folks throw some hints what might be causing this? What are the things I can check for high memory usage? Is there anything that can actually tell me how exactly all the memory is being used(I don't trust task manager 100% 🙂
thanks
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
June 27, 2014 at 6:50 am
Don't use Task Manager to check SQL Server's memory usage, it can show 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
June 27, 2014 at 7:04 am
Ok Gail. Thanks for this recommendation. But is it possible that it shows the total physical memory used also as wrong? Is there a sure shot way to check the total memory usage on a box?
thanks
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
June 27, 2014 at 7:31 am
you don't say the version of OS in use but don't use the free memory value in task manager, look at available memory.
Use perfmon counters to check SQL memory usage
---------------------------------------------------------------------
June 27, 2014 at 8:04 am
ya, I checked the available memory as well and it was some 190 MB. So it is pretty less given that box has 32GB total RAM. SQL Server is taking some 10GB (this might be wrong but it is set not to more than 24 GB). So I am clueless what exactly is taking the 6GB memory which I left for OS.The processes shown by task manager are not taking more than 1 Gb all combined.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
June 27, 2014 at 8:21 am
The total memory should be fine, but to be sure rather use Perfmon. Available MB for free memory, SQL's Total Server Memory for what it's using
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
June 27, 2014 at 8:31 am
190MB is low but you are not out of memory. As Gail says use those two perfmon counters, to see what else is consuming memory use process:private bytes:all instances. Run it for a day or so at 5 min intervals
---------------------------------------------------------------------
June 27, 2014 at 8:36 am
Are you running any CLR?
Are you running SSIS on this box?
Are you running SSRS on this box?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 27, 2014 at 9:09 am
Ok, thanks. I'll try these 2 counters of perfmon.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
June 27, 2014 at 9:09 am
SSIS and SSRS are not there. But I'll check for CLR. Thanks for pointing.
SQLRNNR (6/27/2014)
Are you running any CLR?Are you running SSIS on this box?
Are you running SSRS on this box?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
June 27, 2014 at 9:10 am
only two?
---------------------------------------------------------------------
June 29, 2014 at 10:18 pm
Hi
I have already tried disk queue length, processor time, disk read and write, peges/sec etc...but didn't try these 2 counters. Hence mentioned 2 🙂
george sibbald (6/27/2014)
only two?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
June 30, 2014 at 10:26 am
S_Kumar_S (6/29/2014)
I have already tried disk queue length, processor time, disk read and write, peges/sec etc...
Well.... those are interesting choices of counters. Do you know how (or how not) to interpret those?
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