0

I get periodic slows where the CPU goes high for a period of time which I can occasionally simulate via a query. It almost seems like it happens when the data falls out of cache which seems to happen more than I'd expect. The ram stays at 3.1GB of 3.5GB. Yes, that's probably too low but the system had been running for a couple years without much issue until recently (not sure if over period of time or a specific event). I'm trying to decide if we need to pay for more ram, CPU, or disk IO to our hosting provider. Would low ram cause CPU to go high? CPU: AMD Opteron Processor 4171 HE 2.10 GHz

Begin Edit It looks like sqlserv process is not causing the high cpu but aahStorage.exe is. I believe this is the process that retrieves data from proprietary files on disk for Wonderware System Platform. So I'm not sure if it's slow Disk IO, low memory available, etc. causing it. End Edit

I created the following query

SELECT
REPLACE(counter_name, '(KB)', '(MB)'),
CASE WHEN counter_name LIKE '%(KB)%' THEN CAST((cntr_value/1024) as varchar(1000)) + ' MB' ELSE CAST(cntr_value as varchar(1000)) END
FROM
(
    select TOP 1000 
    ('MemoryMgr: ' + counter_name) as counter_name,
    cntr_value
    FROM sys.dm_os_performance_counters
    WHERE object_name='SQLServer:Memory Manager'
    UNION ALL
    select TOP 1000 
    ('BufferMgr: ' + counter_name) as counter_name,
    cntr_value
    FROM sys.dm_os_performance_counters
    WHERE object_name='SQLServer:Buffer Manager'
) sub1

And have the following results;

MemoryMgr: External benefit of memory     0
MemoryMgr: Connection Memory (MB)         2 MB
MemoryMgr: Database Cache Memory (MB)     485 MB
MemoryMgr: Free Memory (MB)               128 MB
MemoryMgr: Granted Workspace Memory (MB)  0 MB
MemoryMgr: Lock Memory (MB)               2 MB
MemoryMgr: Lock Blocks Allocated          12328
MemoryMgr: Lock Owner Blocks Allocated    12703
MemoryMgr: Lock Blocks                    6751
MemoryMgr: Lock Owner Blocks              6855
MemoryMgr: Maximum Workspace Memory (MB)  725 MB
MemoryMgr: Memory Grants Outstanding      0
MemoryMgr: Memory Grants Pending          0
MemoryMgr: Optimizer Memory (MB)          1 MB
MemoryMgr: Reserved Server Memory (MB)    0 MB
MemoryMgr: SQL Cache Memory (MB)          11 MB
MemoryMgr: Stolen Server Memory (MB)      1226 MB
MemoryMgr: Log Pool Memory (MB)           1 MB
MemoryMgr: Target Server Memory (MB)      2000 MB
MemoryMgr: Total Server Memory (MB)       1841 MB
BufferMgr: Buffer cache hit ratio         1121
BufferMgr: Buffer cache hit ratio base    1121
BufferMgr: Page lookups/sec               7048998623
BufferMgr: Free list stalls/sec           0
BufferMgr: Database pages                 62196
BufferMgr: Target pages                   7176192
BufferMgr: Integral Controller Slope      21
BufferMgr: Lazy writes/sec                8896
BufferMgr: Readahead pages/sec            961391
BufferMgr: Page reads/sec                 1090105
BufferMgr: Page writes/sec                1308925
BufferMgr: Checkpoint pages/sec           1204609
BufferMgr: Background writer pages/sec    0
BufferMgr: Page life expectancy           134774

I'm having a hard time interpreting the results. Any help is appreciated. Thanks

closed as off-topic by LowlyDBA, hot2use, Mr.Brownstone, Vérace, Max Vernon Apr 16 at 19:17

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – LowlyDBA, hot2use, Mr.Brownstone, Vérace, Max Vernon
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Note that Total Server Memory is less than Target Server Memory. You presumably have your SQL Server instance set to use no more than 2000 MB of memory; it's currently using less than that. This either means that your server hasn't needed that much memory since the last restart, or that the system has made it give up some memory at some point, and it hasn't needed to grab it back yet. I'd capture this data at regular points during the day, and see if the total server memory ever goes down. If it does, then other activity on your system is taking memory away from SQL Server. – RDFozz Mar 1 '18 at 19:52
  • @RDFozz you are correct I should have specified it has always been set to 2GB Max. It hasn't been restarted recently. I'll continue to monitor. – vdidxho Mar 1 '18 at 20:09
  • @RDFozz I was checking some other things on the server and my last query returned MemoryMgr: Target Server Memory (MB) 1741 MB MemoryMgr: Total Server Memory (MB) 1741 MB – vdidxho Mar 1 '18 at 20:55
  • What are the wait stats when it slows down, what is the server waiting on? – Greg Mar 2 '18 at 2:05
  • Because these are counters, most of these are primarily useful when you take multiple samples and examine the deltas between samples. From there you can trend what's occurring on the server. I'd recommend using Perfmon, though I suppose you can use the DMV and take multiple samples in a loop. If you post those then maybe we can help you more. – swasheck Mar 2 '18 at 4:14
1

Converting my comments to answer ....

Total Physical RAM = 3583, Virtual = 3583, (1) 21GB, (1) 13GB, (8) 4GB, a handful of smaller ones plus 17GB proprietary file storage. The power plan is currently Balanced.

Based on your above comment, with a mere 3.5GB RAM, you are running dbs totaling the size of 65+ GB. There will be resource starvation on the database server.

Increase the RAM close enough to the size of db and change the power plan to high performance.

Then monitor your db server with sp_whoisactive or SQL-Server-First-Responder-Kit and start tuning when you have data.

Not the answer you're looking for? Browse other questions tagged or ask your own question.