Using Microsoft SQL Server with BMC Remedy AR System

Microsoft SQL Server behaviors that you need to consider are described in the following sections.

Diary and Character field qualifications

When you specify search criteria for a field that contains more than 8000 characters or a diary field, you must use the LIKE operator. If you use any other relational operator, you receive an error.

Case sensitivity in queries

By default, the Microsoft SQL Server search criteria are in dictionary order and are case-insensitive. You can, however, specify an option that enables case-sensitive searches. For more information, see your Microsoft SQL Server documentation.

Reducing deadlocks by using snapshot isolation

Microsoft SQL Server 2005 provides the "snapshot" isolation level, which allows a transaction to read the last committed version of the data that is currently being changed. Thus, the transaction's view of the data is consistent with the state of the data when the transaction began without being blocked by other transactions. This reduces the possibility of deadlocks.

Warning

Some processing overhead occurs due to the creation of a temporary database with a large size, and the storage and retrieval of versioned data.

For more information about the snapshot isolation in SQL Server, go to http://msdn.microsoft.com/en-us/library/ms130975.aspx.

To use a snapshot isolation level with the AR System database

Stop the AR System server to ensure that all connections to the AR System database are closed.
For a server group or a shared database, stop all of the AR System instances.
To verify whether the appropriate isolation level is set for the AR System database, enter:
SELECT is_read_committed_snapshot_on FROM sys.databases where name = 'ARSystem'
To set the snapshot isolation level, enter:
ALTER DATABASE ARSystem SET READ_COMMITTED_SNAPSHOT ON
Restart the AR System server. Optionally, to revert to the original setting, use the following commands in the same sequence as mentioned:
ALTER DATABASE ARSystem SET READ_COMMITTED_SNAPSHOT OFF

Improving performance with parameterization

The AR System server uses dynamically built queries that need to be compiled and recompiled frequently. By default, the AR System database uses simple parameterization.

You can set simple parameterization using the following command:

ALTER DATABASE <database> SET PARAMETERIZATION SIMPLE

Forced parameterization converts any literal value that appears in a SELECT, INSERT, UPDATE, or DELETE statement to a parameter during query compilation. A parameterized query requires less recompilation, thereby improving performance.

Tip

Do not use forced parameterization in environments that rely heavily on indexed views and indexes on computed columns. Error reporting for forced parameterization might differ from that of simple parameterization. Only experienced database administrators should use forced parameterization after determining that its usage does not adversely affect performance.

For further information about forced parameterization, go to http://msdn.microsoft.com/en-us/library/ms175037.aspx.