Check to be sure the “Application Performance” for your server is set to “None”. This ensures that any foreground applications you run on your server will not get a higher priority than SQL Server. To find this setting, go to the “Control Panel”, click on the “System” icon, then click on the “Performance” tab. [6.5, 7.0, 2000]
*****
Check to be sure the “Optimization” for your server is set to “Maximize Throughput for Network Applications.” This will ensure that NT Server allocates more RAM to SQL Server than to its file cache. To find this setting, to go the “Control Panel, click on the “Network” icon, then click on the Services tab, then click on “Server”, and then click on “Properties”. [6.5, 7.0, 2000]
*****
Assuming that SQL Server is located on a dedicated server, the location of the PAGEFILE.SYS is not critical. This is because SQL Server does not normally do much paging on its own. If you do notice that your SQL Server is paging regularly, then it needs to be tuned appropriately so that paging is virtually stopped. Generally, leave the PAGEFILE.SYS file on the same drive as the operating system.
If your server is paging on a dedicated SQL Server, the most likely cause of this is that you are not allowing SQL Server to dynamically allocate RAM on its own. Check how you have configured the “Memory” tab under the SQL Server “Properties” of your server. It should ideally be set to “Dynamically configure SQL Server memory”. [7.0, 2000]
*****
Since the PAGEFILE.SYS is not used much by SQL Server, and only barely used by NT (on a dedicated SQL Server), you don’t have to have a huge PAGEFILE.SYS file. Microsoft recommends that the PAGEFILE.SYS file be set to 1.5 times the amount of physical RAM. The exact amount you need depends on what additional SQL Services you may be running. For example, if you are running Full-Text Search service, Microsoft recommends that your PAGEFILE.SYS file be 3 times physical RAM.
Microsoft’s recommendations are a good starting point, but the best way to size the PAGEFILE.SYS is to monitor how much of it is used during production using the Performance Monitor Page File Object: % Usage counter, and then resize the PAGEFILE.SYS with a minimum size just slightly larger than the amount that is actually being used (based on the Performance Monitor counter), and with a maximum size of 50MB larger than the minimum size.
The PAGEFILE.SYS setting can be viewed and changed by going to the “Control Panel”, selecting the “Performance” tab, and then clicking on the “Virtual Memory” button. If you change the virtual memory settings, you will have to reboot your server for the new settings to go into affect. [7.0, 2000] Updated 4-18-2001
*****
NTFS-formatted partitions should not exceed 80% of their capacity. For example, if you have a 20GB drive, it should never be fuller than 16GB. Why? NTFS needs room to work, and when you exceed 80% capacity, NTFS become less efficient and I/O can suffer for it. You may want to create a SQL Server alert to notify you when your arrays exceed 80% of their capacity so you can take immediate action to correct the problem. [6.5, 7.0, 2000]
*****
Remove all unessential services and network protocols from your SQL Server. These can include, but are not limited to: the web server service, FTP server service, Gopher, SMTP, WINS, DHCP, Alerter, Clipboard Server, Messenger, Network DDE, Directory Replicator, Schedule, Spooler. It also includes unused network protocols, such as DLC, AppleTalk, NWLink, and NetBEUI. Each one you remove frees up RAM and CPU cycles, making them available for SQL Server. Of course, if you really need one or more of these services or protocols, then don’t disable or unload them. The ones listed above aren’t required for a dedicated SQL Server. [6.5, 7.0, 2000]
*****
If your server has plenty of RAM (and SQL Server is not currently experiencing any memory bottlenecks), consider telling the operating system that you don’t want pageable drivers and system code located in the Windows Executive to be paged to disk. By doing this, you reduce some paging on your server and speed up some internal operating system processes. This works for all versions of Windows NT Server 4.0 and Windows 2000.
To do this, you must manually modify the registry. What you need to do is to change the DisablePagingExecutive entry from 0 to 1 in this registry subkey: HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSession ManagerMemory Management While Microsoft’s instructions on how to do this don’t mention whether or not you should reboot your server after making this change, it is probably a good idea. If your SQL Server does not have “excess” memory, then don’t use this technique. [6.5, 7.0, 2000]
*****
Configure NT Server 4.0 to be a member server, not a Primary Domain Controller (PDC) or a Backup Domain Controller (BDC). The task of being a Domain Controller drains away resources from SQL Server. [6.5, 7.0, 2000]
*****
Don’t put SQL Server program, database, or log files on compressed NTFS partitions. The performance is terrible. In fact, make it a rule not to use NTFS compression for any files other than rarely accessed archive data. [6.5, 7.0, 2000]