Silk Central relies on a relational database for storing its data. A well sized and maintained DBMS can significantly impact system performance. This blog post introduces some recommendations for a Microsoft SQL Server-based installation of Silk Central.

1. Data files

You should store SQL Server data files (.mdf) and SQL Server protocol files (.ldf) on different physical drives. The reason is that data files are usually accessed randomly whereas protocol files are written sequentially. This happens concurrently and can seriously impact performance.

We recommend setting Autogrowth for these files to at least 250 MB. This should reduce fragmentation of your database files on your physical hard drive. Do not rely on the default Autogrowth value of SQL Server (1 MB) as this can lead to considerable fragmentation of your data files and seriously impact performance.

Format partitions containing the data files and the protocol files as NTFS with 64 kByte block size. This is due to the fact that the smallest unit in SQL Server is a page, which consists of 8 kBytes. SQL server stores pages in groups of 8 within a block (8x8 kBytes = 64 kBytes), therefore memory is always allocated in 64 kBytes chunks.

2. Memory

Use the 64-bit SQL Server version with at least 4 GB of physical memory. The value should be increased as database size increases. We suggest using physical hardware or a virtual machine exclusively dedicated to host SQL Server. Meaning, no other applications or services should consume memory, CPU or produce disk IO.

Lock Pages In Memory (LPIM) Mode

Virtual Machine Environment

When SQL Server is running on a virtual machine, we recommend enabling the "Lock Pages In Memory" setting. In LPIM mode, SQL Server directly manages physical memory bypassing Windows memory management, preventing the system from paging buffer pool pages to virtual memory on disk.

In a virtual environment, it is more likely for paging to occur when all combined virtual machines consume more physical memory than is available on the host system hardware.  Never page memory to disk because this may lead to a drastic drop of performance or even stall in Silk Central.

Physical Hardware

On physical hardware dedicate to SQL Server only, there is a lower risk of paging memory to disk. Nevertheless, there are some smaller performance benefits with LPIM mode due to more efficient memory management, especially when there are multiple NUMA (Non Uniform Memory Access) nodes in the system. In this case, we recommend using LPIM mode.

When LPIM mode is disabled, it should not be necessary to limit the amount of memory consumed by SQL Server with setting maximum server memory, unless you experience problems that too little memory is left for the OS.

How to Enable Lock Pages In Memory (LPIM) Mode

LPIM mode is enabled via Windows Policy for the Windows account that runs SQL Server (normally "Local System"). On Windows Server OS, it might be enabled by default already.

https://msdn.microsoft.com/en-us/ms190730.aspx

After restarting SQL Server there must be following message in the SqlServer log:

"Using locked pages for buffer pool”

For SQL Server 2008 R2 SP1, LPIM mode is only enabled by default in Enterprise Edition.

For Standard Edition the following update needs to be installed:

https://support.microsoft.com/en-us/kb/970070

Furthermore, you need to add startup parameter -T845 in the SQL Server service configuration.

Limit "Maximum server memory" in LPIM Mode

It is important to limit the amount of memory locked to keep enough space for OS core tasks with the global SQL Server property "Maximum server memory".

Provided that no other software consumes memory, we suggest reserving 1 GB RAM for the OS, and then an additional 1GB for each 4 GB of RAM installed from 4-16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM. If more physical RAM is added in the future, the maximum should be increased accordingly, otherwise SQL Server would not benefit from added memory.

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver15

3. Recovery Model

We recommend operating Silk Central SQL Server databases in simple recovery model. Of course, this depends on your company's processes and policies if you can switch from a full recovery model to a simple one. In most cases, it is sufficient to combine frequent backups with the simple recovery model. This keeps the cost of SQL Server log files low.
 
There is no problem with integrity when switching recovery models, however, we suggest disconnecting the database in Silk Central before switching the recovery model. Consequentially, the log file gets much smaller. 
 
Note that it is important to schedule a frequent backup of the SQL Server database.

4. SQL Server in a Virtual Machine

To achieve sufficient performance, we strongly recommend reserving resources to be used exclusively for the VM:

• Reserve at least four CPUs exclusively for the VM.
• Reserve assigned disks for SQL Server data files and protocol files exclusively for the VM.
• Reserve allocated physical memory exclusively for the VM.
• For the System Database "tempdb", store data files (.mdf) and protocol files (.ldf) on a separate disk exclusively reserved for the VM. Ideally, create one separate .mdf file per CPU Core, each of equal size.
• Make sure SQL Server is running in Lock Pages In Memory mode (see the Memory section).

Without resource reservation, Silk Central performance is more impacted by activity and load in other virtual machines.

5. General Settings

Properties of Silk Central Database

Auto Shrink: Set the option Auto Shrink to false. If you set this option to true this would increase fragmentation of your database and could slow down performance after deleting from the database.

Auto Update Statistics: Set this option to true.

Auto Update Statistics Asynchronously: Set this option to true.

Compatibility Level: For SQL Server 2014 and 2016 set the compatibility level to "SQL Server 2012 (110)". All databases running on other supported versions of SQL Server should be set to the highest available compatibility level.

Server Properties

Max Degree of Parallelism (Server Properties > Page: Advanced > Category: Parallelism)

We suggest limiting the degree of parallelism to the number of hardware processor cores per NUMA (Non Uniform Memory Access) memory node, and not more than 8. With higher values or unlimited parallelism, there is a risk that this leads to negative performance impacts.

Note that this setting affects all databases on this SQL Server instance.

The number of cores per NUMA node can be determined in the server properties on Processors page or with the following query:

SELECT COUNT(parent_node_id) as noof_logical_processors_per_numa
FROM sys.dm_os_schedulers
WHERE [status] = 'VISIBLE ONLINE' and parent_node_id < 64
GROUP BY parent_node_id

Maximum server memory (Section Memory)

See section ->Memory

6. Index Fragmentation

Keeping indexes regularly up-to-date is an important task. This is something which has to be managed on the database server itself rather than in Silk Central. We recommend having your SQL Server database administrator set up a maintenance plan doing regular index maintenance for the Silk Central database. You can use this script for checking indexes for fragmentation: 

SELECT *
FROM (
SELECT object_name(ps.object_id) AS TableName, i.Name AS IndexName,
cast(ps.avg_fragmentation_in_percent AS decimal(6,2)) AS Fragmentation,
CASE WHEN ps.avg_fragmentation_in_percent < 40 THEN 'reorganize' ELSE 'rebuild' END AS Action
FROM sys.dm_db_index_physical_stats(db_id(),null,null,null,'limited') AS ps
INNER JOIN sys.indexes AS i ON i.index_id=ps.index_id and i.object_id=ps.object_id
WHERE i.Name is not null and objectproperty(i.object_id, 'IsUserTable') = 1 and ps.avg_fragmentation_in_percent >= 10) indexFrag
ORDER BY Fragmentation DESC

The output shows the most fragmented indexes. Of course, there are a lot of indexes containing little data, therefore these have a rather small impact on performance. 

We want to draw special attention to these tables:

• TM_PackageRuns
• TM_TestDefExecutions
• TM_NodeAttributeValues
• TM_NodeAttributeOptions
• TM_PlanTreePaths
• TM_TestPlanNodes
• TM_TestDefStatuses

Some of the tables contain indexes which cannot be rebuilt online but might heavily impact performance of the Silk Central system. Therefore, we recommend setting up a maintenance plan where you set the indexes for these columns to be rebuilt online. If this is not possible then they should be rebuilt offline.

We recommend doing the index maintenance during off-peak hours with low activity on the system, e.g. during the night or on weekends.

Although it depends on how much and in which way your system is used, we recommend starting with a monthly index rebuild and doing an index fragmentation check with the script above more often. This should help to find the right interval for doing this maintenance job.

7. Network

Make sure that SQL Server is in the same LAN as the front-end server and the application server. Placing the database server in a different network than the other Silk Central servers can cause large delays and impacts user experience.