Performance optimizations for Microsoft SQL Server
First published: 2018 (cf)
Last update: 7 April 2023 (cf)
Summary
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.
Information
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.
Solution
In this section you will find all the settings that may be changed in order to improve the Fabasoft Folio databases performance.
Statistics
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'
sp_updatestats
Isolation Mode
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"
ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON
Optimize for Ad hoc Workloads
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:
- http://msdn.microsoft.com/en-us/library/cc645587.aspx
- http://blogs.technet.com/b/josebda/archive/2009/03/19/optimize-for-ad-hoc-workloads-in-sql-server-2008.aspx
SP_CONFIGURE 'show advanced options',1
RECONFIGURE
GO
SP_CONFIGURE 'optimize for ad hoc workloads',1
RECONFIGURE
GO
Parameterization Forced
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
ALTER DATABASE SET PARAMETERIZATION FORCED WITH NO_WAIT
Trace Flags
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:
DBCC TRACEON(2388);
DBCC SHOW_STATISTICS(, )
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 to
- Microsoft SQL Server
- Fabasoft Folio (all versions)
- Fabasoft eGov-Suite (all versions)