Performance optimizations for Microsoft SQL ServerPermanent link for this heading

First published: 2018 (cf)
Last update: 7 April 2023 (cf)

SummaryPermanent link for this heading

This article describes the optimization settings for Microsoft SQL Server when used in conjunction with Fabasoft Folio/Fabasoft eGov-Suite installations.

The settings described in this article were determined in collaboration with Microsoft and customers using Fabasoft Folio.

Warning: The settings described in this article are designed to benefit Fabasoft Folio databases, if any other databases are hosted on the database server please double check the effect of these settings on these databases.

Warning: Experience with Microsoft SQL Server and understanding of Microsoft SQL Server settings is required for these changes.

InformationPermanent link for this heading

Please see the corresponding Microsoft articles for further information about the settings. The settings are be thought as suggestions that need to be tested individually for your environment.

SolutionPermanent link for this heading

In this section you will find all the settings that may be changed in order to improve the Fabasoft Folio databases performance.

StatisticsPermanent link for this heading

The default behaviour of SQL Server is to only create statistics for the first index column. In Fabasoft Folio's atval tables (e.g. atstrval), Fabasoft Folio uses clustered indexes with four columns. Because SQL Server has no statistics for indexed columns 2 to 4, the execution plans are not optimal.

By executing sp_createstats with parameter 'indexonly', all columns of the index are considered for the statistics.
By executing sp_createstats with parameter 'fullscan', statistics are recomputed with the full set of data (not only a sample set of data).
Run sp_createstats every time you create or modify indexes (also when creating new Fabasoft Folio COO-Services and using table definitions). The settings are saved at the indexes.

Run sp_updatestats on a regular basis. Consider, that sp_updatestats can run several minutes on large databases.

sp_createstats 'indexonly','fullscan'



Isolation ModePermanent link for this heading

In the following situation:

  • In one transaction data is updated and/or inserted. For this transaction the table is locked by the SQL Server.
  • In a second transaction (e.g. another user) runs a SELECT to this table, querying data.

As per default the SQL Server will halt the execution of the SELECT statement, waiting for the COMMIT of the UPDATE/INSERT statement, and present the new data to the SELECT statement.
Using the settings below you can influence this behavior, when set the SQL Server will copy the old data to a session tempdb table ("snapshot"), issue the UPDATE/INSERT on this tempdb table and commit them to the main table.
During the running UPDATE/INSERT transaction, the SELECT statement can directly read the old values without delay.

With this optimization you can improve the overall Fabasoft Folio performance, but the snapshot isolation mode will raise the usage and size of tempdb.

For details see:

Warning: The Fabasoft Folio COO-Services may need to be stopped for changing the isolation mode.

Change the Isolation Mode of each Fabasoft Folio database to "Read Commited Snapshot"




Optimize for Ad hoc WorkloadsPermanent link for this heading

Fabasoft Folio uses a generic database model, therefore many different queries are generated by the Fabasoft Folio query engine. SQL Server's default behavior is to cache all the execution plans of used queries. Because of the varieties of queries, SQL Server uses a big amount of memory to save execution plans that are never used again. By optimizing SQL Server for Ad hoc workloads, SQL Server reduces the number of execution plans stored in memory, releasing memory for other caches.

For details see:

SP_CONFIGURE 'show advanced options',1



SP_CONFIGURE 'optimize for ad hoc workloads',1




Parameterization ForcedPermanent link for this heading

With forced parameterization Microsoft SQL Server will automatically create parametered queries from plain queries. Query plans of parameterized queries can be reused, optimizing query performance for similar queries.

Important: Please verify the database performance especially after setting this parameter because of different timing results at our customers.

For details see:

Change the Parameterization Mode of each Fabasoft Folio database to FORCED



Trace FlagsPermanent link for this heading

Microsoft SQL Server can detect when the leading column of a statistics object is ascending and can mark it as ascending. A statistics object that belongs to an ascending column is branded as „ascending“ after three updates on the statistics. Fabasoft Folio uses the ascending objid in all indexes. Microsoft SQL Server usually will detect the ascending behaviour of this column (after at least 3 times of updating the statistics).

To check if the detection worked, run:



Example: DBCC SHOW_STATISTICS(cooobject, coobjectIX)


"Leading column Type" should be "Ascending".

If the Leading column Type is not Ascending for indexes with objid as primary column, and you have updated statistics in a regular basis, you can force quick updating statistics before every query run. This may improve performance for several queries, but can also decrease the overall performance since statistics will always be updated.

Warning: Use these trace flags only if your database engine did not detect the ascending behaviour of objid.

Set these Trace-flags:

  • Trace-flag 2389
  • Trace-flag 2390

For details see:

Applies toPermanent link for this heading

  • Microsoft SQL Server
  • Fabasoft Folio (all versions)
  • Fabasoft eGov-Suite (all versions)

Download PDF

Download PDF