TRIRIGAFEEDIA Wiki

Best Practices for System Performance

5 Database Server Tuning & Maintenance

<aside> 💡 To download a PDF copy of this TRIRIGAFEEDIA Best Practices Chapter 5.5: Microsoft SQL Server Database, click the attachment below.

</aside>

TRIRIGAFEEDIA_3.x.x_Best_Practices_05.5_Microsoft_SQL_Server_Database_20210422.pdf

5.5 Microsoft SQL Server Database


5.5.1 Microsoft SQL Server Database Server Tuning


IBM outlines recommendations for running TRIRIGA on a Microsoft SQL Server database. In addition to this wiki page, see the following IBM Support blog entry: IBM TRIRIGA best practice recommendations for a Microsoft SQL Server database.

a. Server and Memory Considerations


IBM strongly recommends a dedicated server for the TRIRIGA database when using Microsoft SQL Server. Compared to other database platforms, Microsoft SQL Server was found to require up to twice the memory resources to achieve the same level of performance as other database platforms. Thus, a large memory allocation is crucial when choosing Microsoft SQL Server.

b. Snapshot Isolation


Configure the database to allow read committed isolation to reduce blocking:

ALTER DATABASE <*dbname*>
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE <*dbname*>
SET READ_COMMITTED_SNAPSHOT ON

For more information, see (1) Snapshot Isolation in SQL Server, (2) Row Versioning-based Isolation Levels in the Database Engine, and (3) Using Row Versioning-based Isolation Levels.