March 10, 2016 at 7:32 am
SQL Server is the only significant application running on a server with 96GB of memory. Max memory is set at 82GB, database has around half a terabyte of data in it.
After running under load for a reasonably long period (a few weeks), perfmon says that the Total Server Memory is at 28GB.
SELECT SUM(single_pages_kb + multi_pages_kb + awe_allocated_kb) FROM sys.dm_os_memory_clerks = ~6GB
SELECT COUNT(*) FROM sys.dm_os_buffer_descriptors = ~2.8 million * 8K pages = ~22GB
Page life expectancy = 2.8 million
Memory grants pending = 0
Free list stalls = 0
Lazy writes/s = 2.0 average (max 45)
None of that seem to imply any serious memory pressure, so I don't quite grasp why the additional memory isn't getting consumed for the buffer cache. If I run a query like:
set statistics io on;
select count(*) from <huge log table> where <entry date> < <yesterday>
I would expect it to have physical reads initially, and then all logical reads afterward. But that isn't the case; physical reads are low, but still present. Also, people who monitor our SAN tell me that our read to write ratio on the data volume is around 90/10 (logs and temp DB are elsewhere); if SQL was able to cache everything it could possibly ever need in memory, I would have expected the ratio to be far less skewed.
Am I expecting too much from the cache? Am I missing somewhere that I can look to determine what is preventing the buffer cache from growing?
March 10, 2016 at 2:31 pm
What is the Windows Server Edition?
Ex. Windows Server 2003 with Service Pack 2 (SP2), Standard Edition supports a max of 32 GB of RAM
If you want to force stuff in the buffer you can start doing select * from a bunch of tables...
March 10, 2016 at 3:43 pm
Windows Server 2008R2
SQL Server 2008R2 SP1 (10.50.2817)
Thanks for the links. I had already read the first, and the second doesn't seem to apply to my OS.
>If you want to force stuff in the buffer you can start doing select * from a bunch of tables...
One would think so, and I've done that, generating lots of IO, but no appreciable change to memory use. Also, I would have expected after a successful query that the IO stats would report a dramatically lower number of physical reads on the second run (maybe zero), yet it does not.
Ultimately, I guess I'm just baffled by why perfmon is commonly reporting 400 read IOPS on a volume essentially dedicated to a SQL user database, and the Resource Monitor attributes 95-99% of the overall disk activity to SQL Server, yet the buffer cache doesn't seem to be growing. I expected a read IO to be a cache miss (which would then add the page to the cache, which should grow the cache unless memory is a problem).
What am I missing here?
March 10, 2016 at 4:06 pm
March 10, 2016 at 4:36 pm
Buffer cache hit ratio is around 99%
Page life expectancy is over 2.8 million
Lazy writes/s is around 2-3 average, peaking around 50 occasionally
>Could just be over provisioned RAM, and you're only seeing the lazy writer doing its thing.
Does the lazy writer generate tons of read traffic? (e.g.: read the whole extent to write a page in it -- FYI, the block size on the disk is currently 4K with plans to change to 64K)
After the lazy writer finishes, is that page retained in the cache, or does it need to be retrieved again from disk?
This server has a history of awful performance during peak season (which starts in a couple of months). It is not under excessive load now, so while the memory might be overkill at the moment, I'm not sure it is in general -- regardless, it was something "easy" to throw at the server at some previous point to try to solve the problem. What I was actually trying to figure out was whether additional memory was justified, and from everything I can see, it is not.
But I got distracted from that whole thread of thinking by the apparent disconnect between lots of read IO and a buffer cache that claims it isn't growing, or evicting anything, which implied to me that it also wasn't storing anything new, which doesn't make sense to me if there is lots of read IO happening.
I'll read the counters article.
March 10, 2016 at 5:19 pm
nhansen pcc (3/10/2016)
Max memory is set at 82GB, database has around half a terabyte of data in it.
It doesn't mean that your applications access this half a terabyte data on a regular basis.
nhansen pcc (3/10/2016)
After running under load for a reasonably long period (a few weeks), perfmon says that the Total Server Memory is at 28GB.SELECT SUM(single_pages_kb + multi_pages_kb + awe_allocated_kb) FROM sys.dm_os_memory_clerks = ~6GB
SELECT COUNT(*) FROM sys.dm_os_buffer_descriptors = ~2.8 million * 8K pages = ~22GB
You may want to try the script I attached instead.
nhansen pcc (3/10/2016)
Page life expectancy = 2.8 million
On average a page stays in a buffer pool for about a month. What else do you want from memory?
nhansen pcc (3/10/2016)
If I run a query like:set statistics io on;
select count(*) from <huge log table> where <entry date> < <yesterday>
I would expect it to have physical reads initially, and then all logical reads afterward. But that isn't the case; physical reads are low, but still present.
This query will only load a small portion of one of the indexes into the memory, which actually might be in the pool already.
The reason you may see the physical reads is because SQL Server can sometimes read more data than it actually needed (read-ahead reads), and under normal circumstances you don't want to interfere with this process.
March 11, 2016 at 7:47 am
Have you checked target server memory?
March 11, 2016 at 8:45 am
@alex Suprun
Nice script. Site needs a +1 option.
>What else do you want from memory?
Ultimately, I'd like to trim some of the read IOs if possible, since the SAN folks are telling me that 90% of my traffic is read IOs.
I'm sure it is far more complex than this, but at a high level, I thought that pretty much every page SQL read from the disk went straight to the cache before it even got used. How can it be reading so much data (1200 read IOPS) without changing its cache usage?
I guess since there don't seem to be any red flags anywhere, I have to assume that the traffic is legitimate.
@steve-2 Jones
Yes, Target Server memory is 82GB to match the memory cap set in SQL Server.
March 11, 2016 at 10:58 am
1200 read IOPS isn't much.
Here's what my new build looks like:
-----------------------------------------------------------------------
CrystalDiskMark 3.0.4 x64 (C) 2007-2015 hiyohiyo
Crystal Dew World : http://crystalmark.info/
-----------------------------------------------------------------------
* MB/s = 1,000,000 byte/s [SATA/300 = 300,000,000 byte/s]
Sequential Read : 642.707 MB/s
Sequential Write : 682.445 MB/s
Random Read 512KB : 575.853 MB/s
Random Write 512KB : 530.748 MB/s
Random Read 4KB (QD=1) : 37.071 MB/s [ 9050.6 IOPS]
Random Write 4KB (QD=1) : 29.611 MB/s [ 7229.2 IOPS]
Random Read 4KB (QD=32) : 336.114 MB/s [ [highlight]82059.1 IOPS[/highlight]]
Random Write 4KB (QD=32) : 315.271 MB/s [ [highlight]76970.5 IOPS[/highlight]]
Test : 1000 MB [T: 0.1% (0.1/150.0 GB)] (x5)
Date : 2016/03/07 12:06:44
OS : Windows Server 2012 R2 Server Standard (full installation) [6.3 Build 9600] (x64)
Single non-ssd disks are up into the 400's these days, some single SSDS get upwards of 50K IOPS
March 11, 2016 at 12:17 pm
Interesting stats. Our network admins have graphs that show the system pulling 12K read IOPS; the 1200 read IOPS was just what perfmon was saying at the moment.
But, whether 12 hundred or 12 million, those IOs are going somewhere. If they aren't going into the buffer cache, where are they going?
Is there some way for me to ask SQL Server to give me a profile of the IOs that it is doing, what data was involved, and what prompted the IO? I can see at the total requested data at the file level with ::fn_virtualfilestats(NULL,NULL), and I can query that data periodically to see the amount of data read over time, but I can't tell what caused the read. Is there some way to divide this out into read requests that might not be eligible for caching (like reading it for a backup or something)?
March 11, 2016 at 12:33 pm
You can some read/write info from sys.dm_exec_query_stats
There's also dm_io_virtual_file_stats (replaces the fn_virtualfilestats)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply