Sorry, premature send. http://blogs.technet.com/b/clinth/archive/2013/10/16/tracking-page-file-reads-and-writes.aspx
From: [email protected] [mailto:[email protected]] On Behalf Of Kennedy, Jim Sent: Thursday, February 25, 2016 3:26 PM To: [email protected] Subject: RE: [NTSysADM] Memory fun Fire up MS's Sysinternals proc monitor and see what is writing to the page files. From: [email protected] [mailto:[email protected]] On Behalf Of Jesse Rink Sent: Thursday, February 25, 2016 3:23 PM To: [email protected] Subject: Re: [NTSysADM] Memory fun That's all great. but I think you missed the point that SQL isn't using up all the memory on the server. Of the 16GB of memory on the server, nearly half of that or more is readily available and not being used by SQL. It's not like SQL is starved for memory, or using up all available memory... And the other thing being, the other servers which all have the same SQL services and basically the same databases (along with identical hardware), do not exhibit the same issue with the Page File Usage I mentioned below... Jesse Rink Source One Technology, Inc. HP Partner 262 993 2231 ** Please visit our blog! http://www.sourceonetechnology.com/blog/ ________________________________ From: [email protected]<mailto:[email protected]> <[email protected]<mailto:[email protected]>> on behalf of Frank Ress <[email protected]<mailto:[email protected]>> Sent: Thursday, February 25, 2016 12:51 PM To: [email protected]<mailto:[email protected]> Subject: RE: [NTSysADM] Memory fun By default, SQL Server will tend to take as much memory as it needs, and it won't return any of it until a SQL restart (usually a server restart, of course). So you could find your OS starved for memory if SQL wants a lot - and one poorly-tuned query could make SQL want a LOT! You should configure the database to limit itself to some max (and probably min) memory amount. Leave at least 4-5 GB for Windows (and other processes), so on a 20G box, tell SQL to use at most 15 or 16G. My SQL servers don't do anything but database, so I generally give them at least 25% of the available memory from the get-go. So, for 20G, max of 15 or 16, a min of 4 or 5. Great. How do you do that? The syntax basics are pretty consistent from release to release. I don't profess to know the details by rote. Here's a chunk of T-SQL that I've used for SQL 2008 and 2008R2 servers. Sorry, minimal comments. But a little Google-fu will get you the background on anything that's not obvious, and you can check SQL BOL (Books on Line) for version-specific syntax changes while you're at it. Note that there are example max memory commands for a 32G and a 20G server. (The '--' is a comment in T-SQL.) HTH Frank sp_configure 'show advanced options', 1 --You can't do the rest of these commands without enabling this... reconfigure GO SELECT * FROM sys.dm_os_process_memory GO --Depending on server memory, use one or the other of the following commands, or roll your own for other memory sizes sp_configure 'max server memory', 26624 --26624/1024 = 26 GB, for 32GB memory reconfigure GO sp_configure 'max server memory', 16384 --16384/1024 = 16 GB, for 20GB memory reconfigure GO sp_configure 'min server memory', 4096 reconfigure GO sp_readerrorlog DBCC memorystatus From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Sean Martin Sent: Thursday, February 25, 2016 12:10 PM To: [email protected]<mailto:[email protected]> Subject: Re: [NTSysADM] Memory fun I'm not proficient with SQL by any means, but it may be worth checking how SQL is configured to leverage physical memory. Perhaps it's capped at the amount of physical memory it can use? - Sean On Thu, Feb 25, 2016 at 6:39 AM, Jesse Rink <[email protected]<mailto:[email protected]>> wrote: This might be a fun one to discuss... I'm not even sure this is problematic or no big deal at all. I'm leaning towards no big deal. I bring it up because the behavior is unlike any other server I have (though that doesn't necessarily mean it's wrong/bad). I have a physical server running Windows 2012 R2 with 24GB of physical memory. Paging file is set to be managed by the OS and is currently 4.58GB in size. The server is not heavily used. What's strange is, the server's paging file usage is constantly upwards of 90% -- which is unlike any other servers I manage, including servers that are running on the -same- hardware spec, -same applications-, etc. The server in question with the 90%+ paging file usage is a bit of an anomaly. Few things to note... 1. The server's Committed Bytes is always hovering around 12GB or so... which is way below the Commit Limit (I think is 28GB or so, (physical memory + virtual memory)). So that seems to be completely normal. 2. The server's Page Faults/sec and Pages/sec are nearly identical with other servers I have that are serving up the same roles/functions and with the same hardware specs. So that also seems completely normal. 3. The server's Available Memory is always around 50%. So that also seems to be completely normal. 4. Because of the 90% Paging File Usage, the server's c: drive "Disk Write Time %" is noticeably higher (averaging around 20% utilization) than other servers (which average around 3% utilization), but it's obviously not high enough that it's affecting performance. I'd also expect to see higher Disk Write Time % with a more heavily utilized Page File anyways. 5. The server's Paging File Usage hovers around 90% (a reboot will lower it temporarily but will return back to 90% after 2-3 days) continually. The other servers I have that are serving up the same roles/functions and with the same hardware specs usually show around 0-10% Paging File Usage. Big difference. 6. The paging file itself, seems to be used, as far as process/bytes go, by SQL (there's a very -small- app using SQL on the box) and also by Java. However, nearly identical servers with the same apps (and SQL too) and hardware specs, don't show the same high Paging File Usage results. All in all, there's seemingly nothing wrong, but it bugs me that the Page File Usage on this particular server is so high. It SEEMS like perhaps there's just something wonky with the SQL and/or Java apps on this particular box that are using the Pagefile so much versus numerous other similar servers... I guess I'm curious... Would any else bother spending the time to figure out what's going on with the Pagefile? Ha. Like I said, there's no real performance hits or problems so maybe it'd be a waste of time, except for figuring it out for curiosity's sake. Jesse Rink Source One Technology, Inc. HP Partner 262 993 2231<tel:262%20993%202231> ** Please visit our blog! http://www.sourceonetechnology.com/blog/ ________________________________ This communication is for the use of the intended recipient only. It may contain information that is privileged and confidential. If you are not the intended recipient of this communication, the disclosure, copying, distribution or use hereof is prohibited. If you have received this communication in error, please advise me by return e-mail or by telephone and then delete it immediately.
