The installation of Microsoft SQL Server is very straight forward with usually little changes to be made as most of the default options are what most users will need. Some applications require these default options to be changed in order to perform at optimal levels. Kronos and Microsoft recommend the following configuration changes be made to your SQL Server instance to ensure optimal performance of the Workforce Central Suite:
|Changing the max degree of parallellism (MAXDOP) to 1||This will limit the number of processors to use in a parallel plan execution thus allowing multiple CPUs to service many simultaneous threads for multiple users.|
|Set the default Index Fill Factor to 75 percent||When an index is created or rebuilt, each page is filled with data according the the index fill factor setting. It is important to set this value correctly or you will get database fragmentation more frequently.|
|Disable the Auto Update Statistics Setting||In order to control what and when processes execute on SQL Server, Kronos recommends disabling this feature and manually updating statistics instead|
|Enable Backup Compression *||Backing up a large database can require a significant amount of time and a large amount of disk space for the back file(s). Introduced in SQL Server 2008 backup compression can compress the backup as it’s written out, so it requires less storage, less disk I/O, and less time to complete|
|Enable Transparent Data Encryption *||Transparent data encryption (TDE) helps protect data that is residing on a backup tape or disk. TDE performs real-time I/O encryption and decryption of the data and log files by using a database encryption key (DEK).|
* If you are using Microsoft SQL Server 2008 (or 2008 R2) Enterprise Edition or greater
Using the “non-default” best practices discussed in this post for SQL Server can help optimize performance of Workforce Central and can help you avoid and minimize problems.
Check out the Kronos and SQL Server Best Practices white paper for more information
For Kronos and Microsoft news, events, and further information, see the Kronos and Microsoft Alliance page.