Minimizing database locks and improving performance with SQL Server 2005 and 2008

Technote (FAQ)


Question

How can database locks and / or blocks be reduced when using SQL Server 2005 and Maximo releases greater than 6.1

Cause

Changes were made to Maximo to fix locking problems on SQL Server 2005 and additional properties were added to Maximo after the initial major release (6.0). These properties are stored in the "additionalmaximo.properties" file so customer settings will not be over written when a patch is applied. To take advantage of these properties, they must be added to the "maximo.properties" file and the EAR file must be rebuilt/redeployed.

Answer

In many cases the default settings shipped with Maximo will provide adequate performance; however, in some cases, large concurrent user counts and large databases my require additional settings to improve the way SQL Server returns result sets.

SQL Server 2005 can use two types of database cursors. One is called client side cursors and the other is called server side cursors. Client side cursors are often quiet efficient but use memory on the database client to store the result sets. Server side cursors use database server memory to store result sets.

The Maximo 6 architecture presents the Maximo application server as a client of the SQL Server 2005 database. Since many users may connect to Maximo and request large result sets simultaneously, the Maximo application server memory can quickly be exhausted if client side cursors are not configured optimally.

NOTE: These properties should be added to and used in conjunction with the required property:

mxe.db.sqlserverPrefetchRows=200

This property must be uncommented and used with the other properties noted in this document in the maximo.properties file to reduce locking and blocking on the database server. Since the properties below were not added until patches subsequent to the initial release of Maximo 6, they appear in the additionalmaximo.properties file but they must be copied to the maximo.properties file and the EAR file must be rebuilt and redeployed any time properties changes are made.

For SQL Server 2005, there are six additional properties available in Maximo to manage cursor usage.


mxe.db.disableservercursor=Y

By default, Maximo 6 will use server side cursors but in heavy usage scenarios, it is often more efficient to use client side cursors. The type of cursor used (server or client) is managed by disableservercursor property. The recommended setting for Maximo 6.2 and above with SQL Server 2005 to use client side cursors is Y

mxe.db.resultsettype=TYPE_FAST_FORWARD

It is important to remember that when using client side cursors, application server memory will be used and can cause problems with the application if the memory usage is not limited. The size of the results returned to the Maximo server can be determined by the resultsettype property. Maximo provides for using several types of result sets as defined by SQL Server. Maximo development has tested three of these types: TYPE_SCROLL_SENSITIVE, TYPE_SCROLL_INSENSITIVE and TYPE_FAST_FORWARD. The most efficient of these is TYPE_FAST_FORWARD and in SQL Server 2005 the database manages the size of the results returned to the client in a much better way than SQL Server 2000 does. TYPE_SCROLL_SENSITIVE provides for moving forward and backward through a result set. Maximo code does not use backward scrolling functionality so does not require this type of functionality. The recommended setting for Maximo 6.2 and above with SQL Server 2005 to use fast forward cursors is TYPE_FAST_FORWARD.


mxe.db.fetchsizeuse=Y

In SQL Server 2005, the size of the fetched results form the database can be managed in terms of record counts. In order to enable this functionality, the fetchsizeuse flag must be set. The recommended setting for Maximo 6.2 and above with SQL Server 2005 to enable the use of the fetch size parameter is Y.


mxe.db.fetchsize=40


In SQL Server 2005, the size of the fetched results form the database can be managed in terms of record counts. The fetchsize must be set to the number of records to be returned at one time. The recommended setting for Maximo 6.2 and above with SQL Server 2005 to set the fetch size record count is 40.

mxe.db.optionuse = Y

SQL Server 2005 changed the way it used the optimizer from SQL Server 2000. In doing so, the FAST_FORWARD cursors caused unnecessary lag in searching through fetch statements. To resolve this issue, Microsoft has provided a work around to improve the performance of fetch statements when FAST_FORWARD cursors are used. In order to implement this work around the optionuse flag must be set. The recommended setting for Maximo 6.2 and above with SQL Server 2005 to enable the use of the option parameter is Y.


mxe.db.optionnum=1000

SQL Server 2005 changed the way it used the optimizer from SQL Server 2000. In doing so, the FAST_FORWARD cursors caused unnecessary lag in searching through fetch statements. To resolve this issue, Microsoft has provided a work around to improve the performance of fetch statements when FAST_FORWARD cursors are used. In order to implement this work around the optionnum value must be set to a value that corresponds to the Microsoft option of "FAST". The recommended setting for Maximo 6.2 and above with SQL Server 2005 to set the option to "FAST" is 1000.

See Microsoft document 946793 for information on the OPTION work around: http://support.microsoft.com/kb/946793

NOTE: These additional properties are not available for Maximo releases prior to 6.2. The above settings will apply only to Maximo versions greater than 6.1.

NOTE: These properties should be added to and used in conjunction with the following property already in the maximo.properties file to reduce locking and blocking on the database server. Since these properties were not added until patches subsequent to the initial release of Maximo 6, they appear in the additionalmaximo.properties file.

mxe.db.sqlserverPrefetchRows=200

NOTE: Performance can also be impacted by too many locks. When Page Locking is enabled, an action can lock a whole page of records rather than just the record affected. Use the following technote to properly set row level locking in SQL Server:
http://www.ibm.com/support/docview.wss?uid=swg21268567

Cross reference information
Segment Product Component Platform Version Edition
Systems and Asset Management Tivoli Change and Configuration Management Database
Systems and Asset Management Tivoli Asset Management for IT
Systems and Asset Management Tivoli Service Request Manager
Systems and Asset Management Tivoli Service Automation Manager

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

IBM Maximo Asset Management
System Related

Software version:

6.2, 6.2.1, 6.2.2, 6.2.3, 6.2.4, 6.2.5, 6.2.6, 6.2.7, 6.2.8, 7.1, 7.1.1, 7.1.2, 7.2, 7.2.1, 7.5

Operating system(s):

Windows

Reference #:

1296072

Modified date:

2009-09-02

Translate my page

Machine Translation

Content navigation