Is everything identical down to the driver level? Regards,
*ASB* *http://XeeMe.com/AndrewBaker <http://xeeme.com/AndrewBaker>* *Providing Expert Technology Consulting Services for the SMB market…* * GPG: *1AF3 EEC3 7C3C E88E B0EF 4319 8F28 A483 A182 EF3A On Thu, Feb 25, 2016 at 3:27 PM, Kennedy, Jim <[email protected]> wrote: > 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] <[email protected]> > on behalf of Frank Ress <[email protected]> > > *Sent:* Thursday, February 25, 2016 12:51 PM > *To:* [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] <[email protected]>] *On > Behalf Of *Sean Martin > *Sent:* Thursday, February 25, 2016 12:10 PM > *To:* [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]> > 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 > > > > ** 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. >
