Odd memory stats

Question

Odd memory stats

DataAnalyst011

SSCarpal Tunnel

Points: 4035
More actions
July 25, 2018 at 3:47 pm

#372521

I'm troubleshooting a 2016 SQL instance that is reportedly getting slower. An odd stat I don't understand is Target and Total Server Memory. They are both at 2.37GB right now. I checked, and max memory for the instance is at 12287MB. PLE is at 218, but I've read that this isn't always indicative of memory pressure. I don't see any Memory Grants Pending. Perhaps this all means that memory is fine. But it just looked odd. Since I'm new to the admin side of things I thought I would check to see if anyone had any thoughts.
Here is some info from DBCC MEMORYSTATUS
Process/System Counts Value
Available Physical Memory 1135857664
Available Virtual Memory 140709368369152
Available Paging File 1094410240
Working Set 2761015296
Percent of Committed Memory in WS 100
Page Faults 119928219
System physical memory high 0
System physical memory low 0
Process physical memory low 0
Process virtual memory low 0
Memory Manager KB
VM Reserved 26562648
VM Committed 2683784
Locked Pages Allocated 0
Large Pages Allocated 0
Emergency Memory 1024
Emergency Memory In Use 16
Target Committed 2683544
Current Committed 2683784
Pages Allocated 2312056
Pages Reserved 0
Pages Free 23360
Pages In Use 1466768
Page Alloc Potential 2035896
NUMA Growth Phase 2
Last OOM Factor 0
Last OS Error 0

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

Target and Total Server Memory are frequently identical after the server has been running for a bit. SQL Server will consume the memory available and then hold on to it. So these measures don't tell you anything about performance. Instead of looking at these counters, I'd suggest starting with wait statistics. That is going to more quickly tell you exactly what and why things are running slowly on the server. Some waits are not useful, so filtering them in advance is a good idea. I'd recommend using Paul Randal's scripts.

"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

DataAnalyst011 SSCarpal Tunnel Points: 4035 More actions · Answer 2

Thanks very much, Grant. I'm so new to the admin side that its hard to know where to start. But one name I always recognize is Paul's. I was actually up at 2AM reading his stuff on rebuilds vs reorgs. Off to read again...

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 3

You literally can not go wrong using Paul Randal, and his team, as a guide.

Best of luck on figuring things out. Come back here as you need help. Everyone tries hard to be useful.

"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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

From the numbers, I'm going to guess you have external memory pressure. Something outside of SQL is consuming memory, and so SQL cannot increase its memory usage. That would be why target server memory is rather low.
Check what else is using memory on that server,

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DataAnalyst011 SSCarpal Tunnel Points: 4035 More actions · Answer 5

Thanks, Gail. We found Windows Updates choking a bunch of RAM on that box. It looks like it was hung. This has been cleared, and we rebooted the box. Things are slowly starting to look better.

DataAnalyst011 SSCarpal Tunnel Points: 4035 More actions · Answer 6

Things are looking much better. Obviously SQL didn't have enough memory to function very well. I guess the thing that bewilders me is that Memory Grants Pending and those sort of stats were not high. The highest wait types were CXPACKET and PAGEIOLATCH_SH. I think the latter can be indicative of memory issues, but I didn't see anything else that indicated SQL wanted more memory. Does anyone have any advice or direction on how to tell if SQL actually is under memory pressure? Thanks again for all the help so far and especially the links.

DataAnalyst011 SSCarpal Tunnel Points: 4035 More actions · Answer 7

Users are still reporting slowness. I reset the wait stats yesterday so I could get a better picture of what is going on after the changes. Below is the current picture. I've been reading a bunch about CXPACKET waits, and noticed this server was setup with a Cost Threshold for Parallelism set to 5. Additionally, I noticed Max Degree of Parallelism is set to 4. I checked the server and it has 3 processors. (This is a VM environment.)

WaitType	Wait_S	Resource_S	Signal_S	WaitCount	Percentage	AvgWait_S	AvgRes_S
CXPACKET	18013.18	16237.99	1775.2	4628176	61.72	0.0039	0.0035
PAGEIOLATCH_SH	4449.7	4397.15	52.55	712285	15.25	0.0062	0.0062
SOS_SCHEDULER_YIELD	1870.99	4.37	1866.62	4741169	6.41	0.0004	0
ASYNC_IO_COMPLETION	788.22	788.2	0.02	29	2.7	27.1799	27.1794
WRITELOG	708.22	619.44	88.78	396366	2.43	0.0018	0.0016
BACKUPBUFFER	687.2	628.88	58.32	80000	2.35	0.0086	0.0079
BACKUPIO	665.35	616.81	48.54	84904	2.28	0.0078	0.0073
ASYNC_NETWORK_IO	564.95	515.3	49.65	168525	1.94	0.0034	0.0031

Brent Ozar recommends starting Cost Threshold for Parallelism at 50 and adjusting from there. Are there other factors I should check first though? And would the MAXOP setting potentially cause issues?

Thanks again for any help or direction.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 8

I've got several blog posts on the cost threshold for parallelism. First, we need to make sure you're on the service packs and CUs from the fall (at least, probably best to get on the latest). They changed the behavior of CXPACKET waits. Prior to that update, CXPACKETS didn't mean much. Now, it does. So, what version are you on?

Then, you can set it to a number. 50 is OK as a ballpark for an OLTP system. A couple of my blog posts show how to get a more accurate number for your system. You might want to try those out.

In addition to looking at waits (IO may also play a factor), you may want to capture query metrics and identify poor performers. In 2016, Query Store is the quick and easy way to do that.

"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

DataAnalyst011 SSCarpal Tunnel Points: 4035 More actions · Answer 9

Hi Grant - thanks a bunch for the links. I've already started working through them. It does look like this instance is behind on service packs. I will make that the priority.