October 5, 2011 at 4:11 am
Hi all,
How to analyze below query output?
Top 10 consumers of memory from Buffer Pool:
SELECT TOP (10) type, sum(single_pages_kb) AS [SPA Mem, Kb]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC
output:
type SPA Mem, Kb
------------------------------------------------------------ --------------------
CACHESTORE_SQLCP 64992
CACHESTORE_OBJCP 2880
MEMORYCLERK_SQLGENERAL 1920
MEMORYCLERK_SOSNODE 1544
OBJECTSTORE_SNI_PACKET 1336
USERSTORE_SCHEMAMGR 1208
CACHESTORE_SYSTEMROWSET 1000
MEMORYCLERK_SQLSTORENG 800
USERSTORE_DBMETADATA 800
USERSTORE_TOKENPERM 752
(10 row(s) affected)
October 5, 2011 at 5:18 am
what do you want to know?
Adam Zacks-------------------------------------------Be Nice, Or Leave
October 5, 2011 at 5:20 am
attached is the output from on of the server which has memory issues.
will this output give any picture about the issue?
October 5, 2011 at 5:22 am
no attachement
Adam Zacks-------------------------------------------Be Nice, Or Leave
October 5, 2011 at 5:41 am
Can you see the attachment now?
October 5, 2011 at 5:57 am
Ok, that info was in your origenal post. What are you trying to analyse?
Adam Zacks-------------------------------------------Be Nice, Or Leave
October 5, 2011 at 7:38 am
What problem are you trying to diagnose?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 5, 2011 at 7:41 am
CPU is 100% most of the time even when users are not using the server
October 5, 2011 at 7:42 am
Or are you trying to determine what this says about your SQL Server instance? It's not a bad question, just not sure what you mean. One thing to look at as well is what the total memory available to the instance is.
October 5, 2011 at 7:44 am
And maybe also monitor compiles
http://sqlinthewild.co.za/index.php/2010/07/27/hit-and-miss/
http://sqlinthewild.co.za/index.php/2010/08/31/come-and-gone/
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 5, 2011 at 7:45 am
That's correct I want to know if the output I've attached is alright for the server or it's bad and some tweaking is needed?
October 5, 2011 at 7:47 am
If the CPU is 100%, I'd focus there, not on memory. Memory pressure can evidence itself with increased I/O as more stuff gets flushed to disk more frequently, but it's usually not evidence in memory.
I'd try collecting information about what things are causing the users to wait. This is in the wait statistics and in the queues. Start with the waits:
SELECT * FROM sys.dm_os_wait_stats AS dows
You can also get a good idea of what is taking up the most CPU by querying sys.dm_exec_requests. That will also show you if you have blocking. All these are better uses for your time if you're trying to fix 100% CPU.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 6, 2011 at 2:50 am
Thank you all for guiding me in the right direction
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply