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.
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.
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.
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.
To use forced parameterization with the AR System database
To set forced parameterization, enter the following SQL command:
ALTER DATABASE ARSystem SET PARAMETERIZATION FORCED
You can set this attribute without interrupting the existing database connections.
- To revert to the original setting, enter the following SQL command:
ALTER DATABASE ARSystem SET PARAMETERIZATION SIMPLE