Hints for a Faster System

Download and read the following VMWare document: http://www.vmware.com/files/pdf/solutions/SQL_Server_on_VMware-Best_Practices_Guide.pdf

An example

If the GN4 system uses the SQL standard, the OS limitation is that it can not use more than 4 CPUs. If in VMWare you assign 8 virtual sockets, the SQL won't use more than 4, because of the aforementioned OS limitation. Such setting, besides being wrong from the license point of view, can also cause slowing down of the entire VM, and in particular the slow spiking.

The solution is to assign 2 virtual sockets instead of 8, and 4 virtual core by socket. Therefore, the total usage will be 8 virtual core and 2 processors, that honors the SQL server license.

•SQL should always be configured to use less memory than the machine has allocated to leave room for OS functionality.  For example, on an VM with 8GB of RAM, 6-7GB should be allocated to SQL leaving 1-2GB for OS.

•Always grant the 'Perform Volume Maintenance Tasks' privilege to the SQL Server service account (typically 'Network Services') - see http://www.myTechMantra.com/LearnSQLServer/Database_Instant_File_Initialization_Feature.html for step-by-step instructions. This enables 'Instant file initialization' - see http://technet.microsoft.com/en-us/library/ms175935.aspx and http://blogs.msdn.com/b/sql_pfe_blog/archive/2009/12/23/how-and-why-to-enable-instant-file-initialization.aspx - speeding up file growth operations.

•All GN4 installs should include a daily maintenance plan that backs up database, rebuilds indexes, and cleans up old backup files.  This is built natively in SQL using built in system operations.  Optionally, you can also shrink the database during this plan but ensure that the rebuild of the indexes occurs AFTER the shrink operation.

•One of the most important configuration items in the GN4 system is to ensure that the hard drives that store the DB files have fast read/writes (15k RPM drives recommended).  Also, the temp DB, logs and primary DB files should all be on separate drives so that they don’t compete with each other.

•To take it a step further, when possible the temp, logs and primary DB should be stored on separate physical LUNS in the SAN, or handled by separate drive controllers with local storage.

•When using a virtual SQL server with SQL Server Standard, there are ways around the 4 processor limit.  For example, assigning 2 virtual processors with 4 cores each will yield 8 total and be permitted by the software.

•A load balanced GN4 browser only MS web farm have cookie/authentication issues that need worked out.

•Two Installations have deployed multiple IIS servers but are “load balanced” manually.  This is achieved by pointing certain segments of users to 1 of the multiple IIS servers.  This allows you to control how many users are connected to each IIS server.  All are connected to the same backend database and works well in production.

•Using compression in IIS is a tradeoff. If you have a site that has a low amount of bandwidth coming in to the building, then enabling dynamic compression will help use less bandwidth to outside users using GN4.  However, this will increase the amount of server resources used by the application.  If bandwidth is no concern, dynamic compression should be disabled as this will free up CPU and memory on the IIS server.  Also, a hybrid installation with an IIS application for outside users with compression on and a separate application with compression disabled for internal users could be useful in some cases.

•In current builds, due to the data model, the more items that are published to a destination the longer the publishing activity takes.  This is due to having to build all of the references during each publish action.  It only becomes noticeable after many objects have been published.  A recent bug was fixed that should make this operation faster on latest builds.  However, it is still necessary to export the publishing destinations on a regular basis, clear the referenced objects and reimport.  A suggestion was made to Marco T to make this action an optional part of the cleanup process if possible.

The system monitoring service / application monitors all external GN4 processes and sends alerts or runs scripts based on scheduled items, event log messages, cpu spikes, etc.  This is work in progress.