December 17, 2012 at 10:25 am
Hi,
We have a SQL 2005 instance that's the back end DB for our company helpdesk system. Performance of late has been sluggish and upon investigation i've noticed the sqlservr.exe process is only using 125Mb of memory.
The server has 6GB and sql is configured to use up to 4GB of that so i'm at a loss why it's not growing past 125Mb ?
The process is however clocking a lot of CPU at 50-90%
Any ideas ?
TIA,
M
December 17, 2012 at 11:23 am
Don't use Task Manager to check SQL's memory usage. It doesn't report correctly.
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
December 17, 2012 at 11:25 am
Thanks Gail - what's the recommended way ?
December 17, 2012 at 11:26 am
Performance Monitor. Total Server memory counter (it's a SQL counter)
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
December 18, 2012 at 2:55 am
Thanks Gail - it's showing as 4194304 (ie 4Gb) in perfmon
The instance has AWE enabled, is the view this is better to have on than off ?
December 18, 2012 at 3:12 am
Whilst i was in perform i noticed the pages/sec was quite spiky with peaks of around 1500/sec.
Is this on the high side or normal server performance ?
December 18, 2012 at 3:21 am
mjwlufc (12/18/2012)
The instance has AWE enabled, is the view this is better to have on than off ?
32 bit or 64 bit SQL?
Whilst i was in perform i noticed the pages/sec was quite spiky with peaks of around 1500/sec.
Which pages/sec counter? (there's more than one of them and they mean very, very different things)
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
December 18, 2012 at 3:33 am
It's this version
Microsoft SQL Server 2005 - 9.00.5057.00 (Intel X86) Mar 25 2011 13:50:04 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
The VM has 6Gb or RAM with SQL capped at 4GB
The memory objects i'm lookin at are pages /sec and page faults/sec
page faults/sec is spiking up to 15000 - seems very high.
And advise on improving the situation is much appreciated 🙂
December 18, 2012 at 3:38 am
mjwlufc (12/18/2012)
The memory objects i'm lookin at are pages /sec and page faults/sec
Which pages/sec? Under which section? There are multiple pages/sec counters in different sections and they mean different things.
Since you're using 32 bit SQL, having AWE on is required to access more than 2 GB of memory.
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
December 18, 2012 at 4:11 am
Gail if i was a little unclear.
Both the paging counters were selected from the 'memory' performance object.
December 18, 2012 at 4:28 am
Ok, So Memory/pages\sec
That's the count of hard page faults (page faults/sec are soft page faults). That's to do with pages that are in virtual memory but not physical memory (ie are in the page file)
SQL shouldn't be using the page file at all, unless it's getting paged to disk by the OS, so those will be from apps other than SQL on the box.
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 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply