5.5 Microsoft SQL Server Database

IBM outlines recommendations for running TRIRIGA on 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.

c. Implicit Conversions

When SQL Server tries to join on or compare fields of different data types, if they are not the same data type, it will convert one to match the other. This is called implicit conversion. An implicit conversion is not desired in SQL Server, and can lead to poor performance due to SQL Server not using indexes optimally. For more information, see decimal and numeric (Transact-SQL).

If there are implicit conversions, the plans generated may still be cached in SQL Server. The following SQL will show the plans with the implicit conversions. The results will be shown in the query results. If you click on the XML link, you can search for and observe the implicit conversion. If you observe implicit conversions, notify IBM Support for further assistance.

• Show Plans with Implicit Conversions

d. Sparse Columns

Microsoft SQL Server has a limitation for row size at about 8060 bytes of data. This applies to data that is stored in the row. Most VARCHAR/NVARCHAR data is kept off row (just a pointer is stored on the row).

Some users have had issues storing data because the row data is too big on specific business object tables (e.g. T tables like T_TRIREALESTATECONTRACT, T_TRICAPITALPROJECT and T_TRIBUILDING). Users see the dreaded "Cannot Create a row of size NNNN which is greater than the allowable maximum row size of 8060" in the server.log and they cannot save their record. To remedy the situation, you must analyze the business object and delete unused fields.

Another alternative you can use is sparse columns. Sparse column support was introduced in SQL Server 2008. A sparse column is an ordinary column that has been optimized for null value storage. Null value storage is optimized at the expense of value storage; a sparse column with a null value takes up no storage space at all. However, if the column has a value, 2-4 extra bytes over the value size are required to save the field value. There is a trade-off and the ratio of non-null to null values needs to be significant for any benefit. Microsoft suggests not using sparse columns unless the space saved is at least 20-40%. There is also a cost when reading non-null values from sparse columns; table operations including this column may require more processing. That being said, depending on the data being stored, this could be an option for making the row sizes smaller.

e. Index Rebuild Cleanup Parameters

The nightly Platform Maintenance stored procedure for Microsoft SQL Server was updated from the older INDEX DEFRAG script to a newer INDEX REBUILD script. These are the parameters that can be tailored for each individual environment. Consult with your SQL Server database administrator to tune these settings for optimum impact in your environment.

• reorg_frag_thresh: The percentage threshold at which to reorganize indexes.
• rebuild_frag_thresh: The percentage threshold at which to switch from reorganizing to rebuilding.
• fill_factor: The percentage of space on each leaf-level page to be filled with data.
• page_count_thresh: If set, the number of pages the current table uses.
• rebuild_online: Rebuild the indexes online so that other processes are able to access the table while the rebuild is occurring.
• compute_statistics: If set, compute statistics on the tables.
• report_only: If set to true, do not actually do the build, just report what the status of the indexes, stats, and/or tables are.
• MinutesToRun: The number of minutes to run, where 0 (zero) means run until complete. This can be set to a limit, like 60 (minutes), so the process will stop after 1 hour. On the next run, the process will pick up where it left off.

The following performance tuning indexes can be added to SQL Server databases that are running TRIRIGA Platform 3.5.x. These recommended SQL Server performance tuning indexes are the result of iterative performance tuning cycles and collaboration with the TRIRIGA development team. The indexes listed here are not included in the TRIRIGA base product unless otherwise stated.

These indexes provide significant performance improvements when measured against a broad performance test workload. While TRIRIGA recommends adding these indexes to the SQL Server database platform, performance gains might vary depending on an array of factors including application usage, load patterns, hardware sizing, application, database server configuration, and so on. SQL Server database administrators should monitor databases for efficient index usage to determine the overall impact produced by applying the recommended indexes, and to determine additional indexes that will improve performance based on situational and data composition needs.

• Platform Indexes

Any implementation of Reserve should be tuned to include appropriate indexes for performance improvement. The following indexes were identified to help increase performance dramatically for reserve queries by the TRIRIGA performance team on SQL Server, but you should review and tune for your specific implementation.

The following performance tuning indexes can be added to SQL Server databases that are running TRIRIGA Platform 3.5.x. These recommended SQL Server performance tuning indexes are the result of iterative performance tuning cycles and collaboration with the TRIRIGA development team. The indexes listed here are not included in the TRIRIGA base product unless otherwise stated.

These indexes provide significant performance improvements when measured against a broad performance test workload. While TRIRIGA recommends adding these indexes to the SQL Server database platform, performance gains might vary depending on an array of factors including application usage, load patterns, hardware sizing, application, database server configuration, and so on. SQL Server database administrators should monitor databases for efficient index usage to determine the overall impact produced by applying the recommended indexes, and to determine additional indexes that will improve performance based on situational and data composition needs.

• Reserve Indexes

Performance benchmark testing for Lease was performed on the DB2 database platform. However, the findings from that platform may also be applicable to Microsoft SQL Server. Your database administrator can take the identified queries from the DB2 results and use the index adviser for your database platform to see what indexes are recommended on that platform.