Tuning Microsoft SQL Server

Tune Microsoft SQL Server for optimal performance.

Tuning the server and memory

Use a dedicated server for the Maximo® Real Estate and Facilities database when you use Microsoft SQL Server. Compared to other database platforms, Microsoft SQL Server requires up to twice the memory resources to achieve the same level of performance as other database platforms, so a large memory allocation is needed when you are implementing Microsoft SQL Server.

Isolating snapshots

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:

Implicit conversions

When SQL Server tries to join on or compare fields of different data types, it converts one to match the other. This is called implicit conversion. An implicit conversion is not desired and can lead to poor performance because SQL Server does not use indexes optimally. For more information, see decimal and numeric (Transact-SQL).

If there are implicit conversions, the plans that are generated might still be cached in SQL Server. For more information, see the following script that shows the plans with the implicit conversions Show Plans with Implicit Conversions.

When you run the script, the results are shown. If you click the XML link in the results, you can search for and observe the implicit conversion.

Sparse columns

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

Some users have issues when storing data because the row data is too large for specific business object tables, for example, T tables such as T_TRIREALESTATECONTRACT, T_TRICAPITALPROJECT, and T_TRIBUILDING. Users see the 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 resolve such a situation, analyze the business object and delete unused fields.

An alternative is to use is sparse columns, which is a column that is 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. However, if the column has a value, 2-4 more bytes over the value size are needed to save the field value. There is a tradeoff, and the ratio of nonnull to null values needs to be significant for any benefit. Do not use sparse columns unless the space saved is at least 20-40%. There is also a cost when reading nonnull values from sparse columns. Table operations including this column might require more processing. However, depending on the data that is being stored, you might use this option to reduce row sizes.

Database Table Manager:

For environments that use a Microsoft SQL Server database, IBM® Maximo Real Estate and Facilities supports the actions of viewing, creating, and removing sparse columns by using the Database Table Manager tool. For more information, see Database tables.

Alternatively, to make a sparse column, you must work outside of Maximo Real Estate and Facilities directly in the database. Do not publish the business object because this action might make the field revert to nonsparse. Use sparse columns sparingly because they might have a performance impact depending on the data. For more information, see Use Sparse Columns.

Cleanup parameters for rebuilding the index

The INDEX REBUILD script contains the nightly Platform Maintenance stored procedure for Microsoft SQL Server. However, you must configure the following parameters for each of your environments. Consult with your SQL Server database administrator to tune these settings for optimal 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 fill with data.
page_count_thresh: If set, the number of pages that the current table uses.
rebuild_online: Rebuilds the indexes online so that other processes can access the table while the rebuild is occurring.
compute_statistics: If set, computes the statistics on the tables.
report_only: If set to true, this parameter does not build the index. Instead, it reports the status of the indexes, stats, and tables.
MinutesToRun: The number of minutes to run, where 0 (zero) means run until complete. MinutesToRun can be set to a limit. For example, set it to 60 minutes, so that the process stops after 1 hour. On the next run, the process picks up where it last stopped.