An Often Overlooked Windows Setting for the DBA

Great tip, Jason. I've added it to my sql server build sheet to remember to set it for new servers going forward.

Thanks!

Great tip, Jason. I've added it to my sql server build sheet to remember to set it for new servers going forward.

Thanks!

Good information, thanks.

hiya,

where is this setting in windows 2008 ?

cheers,
rich

Not sure about Win2k8. Will research.

Jason,

Your post is well timed, as I am about to address this issue on a number of DB servers that I “inherited”. The specific configuration of the full set of DB servers is mixed, with some using “Maximize data throughput for file sharing” and others using “Maximize data throughput for network applications”. I plan to change them all to use “Maximize data throughput for network applications”.

Your comment that “Maximize data throughput for network applications” is on by default may not be accurate. I believe the default setting is “Maximize data throughput for file sharing”, probably under the assumption that more servers get deployed as file servers versus application servers. I’m not sure if the “Manage My Server” dialog (that allows the administrator to change the server roles – file, web, application, etc.) changes this setting or its default value. If changes in server roles do change this setting, perhaps that is where there might be confusion on the “default value” for this configuration setting.

I reviewed the SQL 2008 Books Online link you referenced and noted that the text in your post was directly quoted from that link. I find some discrepancies in that text:

- The setting you really want to avoid is “Maximize data throughput for file sharing”, and Books Online says so in the beginning of this section:

To optimize system memory use for SQL Server, you should limit the amount of memory that is used by the system for file caching. To limit the file system cache, make sure that Maximize data throughput for file sharing is not selected.

- The next part tells you how to confirm the current setting and how and what to change, if needed:

To check the current setting on your operating system
1. Click Start, then click Control Panel, double-click Network Connections, and then double-click Local Area Connection.
2. On the General tab, click Properties, select File and Printer Sharing Microsoft Networks, and then click Properties.
3. If Maximize data throughput for network applications is selected, choose any other option, click OK, and then close the rest of the dialog boxes.

The confusing part is that the initial description tells you to avoid the setting of “Maximize data throughput for file sharing”, but the step-by-step change instructions tells you to avoid the setting of “Maximize data throughput for network applications” – going so far as to say that any other setting would be OK, which would include “Maximize data throughput for file sharing” (even though it said earlier to avoid using this setting). I find this description inconsistent, and very possibly wrong.

- The section within that Books Online page is titled “Maximize Data Throughput for Network Applications”, yet the section seems to be saying not to use this setting. Yet another contradiction, if you ask me. Usually, a section heading like this would be for a recommended setting, and not for a “recommended against / avoid” setting.

A different page on the SQL 2008 Books Online says it a bit differently, with some of the same wording, but a key difference in emphasizing that “Maximize data throughput for file sharing” as the “bad” setting to avoid and that the other three settings are better, possibly to different degrees:

http://msdn.microsoft.com/en-us/library/ms191471.aspx

The ending part of this reference is a bit wishy-washy, saying to choose either “Maximize data throughput for file sharing” or “Maximize data throughput for network applications” (after already having said that “Maximize data throughput for file sharing” is not good). Either take a position, or don’t bother to say anything! I wonder if we have a case of some rookie Microsoft documentation person(s) not writing or proof-reading their content as thoroughly or consistently as possible (in both Books Online pages).

The following link documents the registry settings (LargeSystemCache and Size) behind the four possible settings, and better describes the differences between the “Maximize data throughput for network applications” and “Maximize data throughput for file sharing” settings, although it stops short of better describing the differences and potential benefits of the “Minimize memory used” and “Balance” settings.

http://technet.microsoft.com/en-us/library/cc784562.aspx

The description for the two settings for the LargeSystemCache registry setting:

1: Establishes a large system cache working set that can expand to physical memory, minus 4 MB, if needed. The system allows changed pages to remain in physical memory until the number of available pages drops to approximately 250. This setting is recommended for most computers running Windows Server 2003 on large networks.

0: Establishes a standard size file-system cache of approximately 8 MB. The system allows changed pages to remain in physical memory until the number of available pages drops to approximately 1,000. This setting is recommended for servers running applications that do their own memory caching, such as Microsoft SQL Server, and for applications that perform best with ample memory, such as Internet Information Services (IIS).


This description says that the “file server” setting of 1 will maintain a large file system cache of up to all physical memory less 4 MB (possibly to the detriment of other applications needed said memory). In contrast, the “application server” setting of 0 uses a maximum file system cache setting of 8 MB, leaving the rest of physical memory available to other applications that may better use that memory (SQL Server and IIS are specifically named).

It seems that LargeSystemCache is set to 1 only if “Maximize data throughput for file sharing” is configured, and to 0 if any of the other three settings is configured (“Maximize data throughput for network applications”, “Minimize memory used”, or “Balance”), again supporting the argument that “Maximize data throughput for file sharing” is “bad” and the other three are probably better.

I want to use a preferred setting, and based on my readings of these references, I believe the preferred setting to be “Maximize data throughput for network applications” (for lack of better description on the potential benefits of “Minimize memory used” or “Balance”). I find the descriptive document on LargeSystemCache more convincing than articles or recommendations that only says “setting X is better than setting Y” but never tell you why or justify their position. I may later find documentation that further describes the other possible settings (“Minimize memory used” or “Balance”) and these findings may change my opinion, but it will be based on compelling description or evidence.

It appears that I am not the only person finding these Books Online references conflicting. In a response to a post from Brent Ozar on SQL Server setup preparation (http://www.brentozar.com/archive/2008/03/sql-server-2005-setup-checklist-part-1-before-the-install/), Flora references two SQL 2005 Books Online references (possibly the same to as we referenced here, but for SQL 2005 versus SQL 2008).

Do these additional references affect your opinion about the first SQL 2008 Books Online page or your position on the recommended settings?


Scott R.

You raise some great points. As does Rich, since this setting is not visible through the GUI in Win2008. I am going to do some research and probe internal contacts but I am guess file sharing is the worst option until I hear better.

Comments from the following blog entry: http://www.pythian.com/news/1764/log-buffer-141-a-carnival-of-the-vanities-for-dbas