Knowledge Article
FootPrints 12.X - 20.XX - How to Configure Initial Setup of SQL Server
How to Configure Initial Setup of SQL Server for use with FootPrints 12.X - 20.XX
FootPrints
Any version of Footprints 12.1.X and 20.XX
Description: The following information pertains to FootPrints Service Core 12.0/12.1 and 20.XX (including Service Packs/hotfixes). This article provides information regarding configuration of MS SQL Server for optimal use with FootPrints Service Core 12.1.X and 20.XX Information: SQL Server Initial Setup There are some setting options used in the setup of SQL Server. This falls into two categories: settings whose defaults should always be changed, and settings that should never be changed. These recommendations should be followed unless you have experienced staff familiar with your hardware and software setup that have other recommendations. In addition to these settings, regular database maintenance is required. It is especially important to reindex the database tables after an install or upgrade, and after any major addition of users or data to the system. In addition, it is suggested that indexes be rebuilt on a regular basis. It is also important to implement a regular database backup plan, including not only full database backups but regular transaction log backups as well. General Information Settings that should always be changed SQL Server Max Memory If you notice that the system is slow, and SQL Server is above the recommended amount, then setting max memory will prevent SQL Server from using an excessive amount of memory. Proactively setting the Maximum server memory (in MB) (see image below) will help prevent performance issues with the server. Setting the value prior to installing FootPrints 12 and placing the system into production will help eliminate problems later. A rule of thumb for setting memory is as follows (2nd column is a calculation that can be performed in MS Excel):
http://www.brentozar.com/blitz/max-memory/ For setting a slightly larger value, reference this link: http://sqlblog.com/blogs/tibor_karaszi/archive/2014/03/06/setting-max-server-memory.aspx Additionally, a google search of "SQL Server Max Memory 2014" (substitute 2014 with the current product year or version of MS SQL Server in your environment)to find advice from other administrators. Optimize for Ad Hoc Workloads This setting should be set to TRUE for FootPrints. It avoids excessive memory use for queries that only execute a single time. The FootPrints application searches are ad-hoc queries. Max Degree of Parallelism This option limits the number of threads that a single SQL query running on a single database connection can use. For the FootPrints application, occasional deadlocks may occur when a rogue query tries to run in parallel and it locks many other threads. This is especially true when running on Virtual Machines. This is a normal finding on an OLTP web application that may have 250 connections or more to the database. One connection uses all the processors for a short time. When this happens, normally it is fine, but every once in a while a parallel query gets deadlocked and then becomes an 'idle blocker' connection that must be killed. Therefore, we recommend disabling parallel query by setting to 1, or at least setting it to half the number of cores available to SQL Server. How to Set Server Level Advanced Properties
Values/Settings that should never be changed In the vast majority of cases, it is recommended to not change settings on the "Processors" section of "Server Properties" per the image below: Database Compatibility Level in SQL Server Ensure that the Database Compatibility Level of your database is set to the highest support by the application System Requirements. The following blog post can help with understanding of this setting. https://www.sqlskills.com/blogs/glenn/database-compatibility-level-in-sql-server/ |
Attachment(s):