Hello everyone,

I would like to get your insights on some recommendations I received for optimizing our dedicated SQL Server 2019 Standard instance. This server exclusively hosts the databases for Laserfiche 12, including the following modules:

• Laserfiche Content Repository

• Laserfiche Workflow

• Laserfiche Forms

• Laserfiche Directory Server

Server specs:

• CPU: Intel Xeon Gold 6152 (16 physical cores, 16 logical)

• RAM: 64 GB

Suggested parameter adjustments by phase (with explanation and SQL command):

Phase 1 – Critical Adjustments (memory and parallelism):

• max server memory (MB) → from 45000 to 57344 (56 GB)
  Explanation: Reserve about 8 GB for the operating system and other applications (80–90% of total memory).
  SQL Command: sp_configure 'max server memory', 57344; RECONFIGURE;

• max degree of parallelism → from 0 (unlimited) to 8
  Explanation: Best practice is to use half of the physical cores (16), which helps reduce contention.
  SQL Command: sp_configure 'max degree of parallelism', 8; RECONFIGURE;

• cost threshold for parallelism → from 5 to 50
  Explanation: Ensures only queries with estimated cost >50 will use parallelism, avoiding unnecessary parallelism for small queries.
  SQL Command: sp_configure 'cost threshold for parallelism', 50; RECONFIGURE;

Phase 2 – Optimizations:

• optimize for ad hoc workloads → from 0 to 1
  Explanation: Improves plan cache efficiency when there are many single-use queries.
  SQL Command: sp_configure 'optimize for ad hoc workloads', 1; RECONFIGURE;

• min server memory (MB) → from 1024 to 8192 (8 GB)
  Explanation: Guarantees a minimum memory allocation for SQL Server (about 10–15% of max server memory).
  SQL Command: sp_configure 'min server memory', 8192; RECONFIGURE;

Phase 3 – Optional Adjustments:

• min memory per query (KB) → from 1024 to 4096 (4 MB)
  Explanation: Provides more memory to complex queries such as reporting or aggregations.
  SQL Command: sp_configure 'min memory per query', 4096; RECONFIGURE;

• backup compression default → from 0 to 1
  Explanation: Reduces backup size with minimal CPU impact.
  SQL Command: sp_configure 'backup compression default', 1; RECONFIGURE;

• max worker threads → from 0 (auto) to 1024
  Explanation: For 16 cores, the calculation suggests 512 + (16 × 32) = 1024, which is suitable for high concurrency.
  SQL Command: sp_configure 'max worker threads', 1024; RECONFIGURE;

Validation after adjustments:

• Monitor memory usage with performance counters:
  SELECT counter_name, cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE '%Memory%';

• Check query parallelism in Query Store runtime stats:
  SELECT query_id, plan_id, max_dop FROM sys.query_store_runtime_stats;

• Review wait statistics:
  SELECT * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC;

Has anyone here applied similar adjustments in their SQL environments for Laserfiche?
Do you think these recommendations are aligned with best practices for Laserfiche workloads?

I’d appreciate any feedback or lessons learned from your own environments.