SQL Server Configuration Advice
Click here to see this page in full context

SQL Server Configuration Advice

   

Virtual Machine  

The Oracle RBMS provides a good experience with PROIV, with little or no configuration. This includes behaviour such as:

SQL Server does not provide the same experience without some additional configuration. There are two recommmended configurations of SQL Server with PROIV but the user is given access to a fine level of control.

Read Uncommitted or Dirty-Reads

The default configuration of PROIV for SQL Server is to allow transactions to read the uncommitted changes of other tranactions and to use NOLOCK hints. This avoids fetches from SQL Server (read) locking because other transactions are updating the same records. This in turn can cause PROIV screens to pause and/or fail.

The settings for this are:

Snapshot Read Committed

SQL Server 2005 introduced two new database options:

These options are configured directly on the database in SQL Server and not through PROIV. It can be done via Transact-SQL or Microsoft SQL Server Management Studio (see "Database Properties" -> "Options" -> "Allow Snapshot Isolation" and "Is Read Committed Snapshot On"). For an example of their use see Microsoft's Mini-Lab on the subject.

The second recommended configuration is to switch on the "SNAPSHOT_READ_COMMITTED" database option and switch off PROIV's adding of locking hints. This gives a behaviour very similar to that of Oracle. Although the transaction isolation level is still "READ_COMMITTED" (as in the previous configuration) its behaviour is different if the database has "SNAPSHOT_READ_COMMITTED" switched on.

The settings for this are:

IMPORTANT: the above PROIV configuration will only be successful if the accompanying database option change is made in SQL Server.

Alternative Configurations

The user is not restricted to using either of the recommended configurations. Be aware that certain choices will affect how PROIV operates. More pessimistic locking options means PROIV screens are less likely to work successfully on a shared table. The "SNAPSHOT" transaction isolation level (related to but not be confused with the database option) allows a high-level of isolation between transactions, which appears attractive. However if two concurrent transactions with this isolation level attempt to update the same row/value then the later transaction is forcibly rolled back by SQL Server; losing all uncommitted values. An application could re-apply (externally stored) changes but this not something that PROIV will do.

Dashboard Configuration In Detail

This section describes the dashboard configuration options in more detail to help users understand the impact of choices.

Startup Arguments

The startup arguments are passed directly to the SQL Server connection string. This means that they are not validated by PROIV and so the user should be careful to ensure that they are valid SQL Server connection string arguments. It should be noted that if these arguments are controlling elements of the SQL Server connection or behaviour they may be overridden by, or override, the PROIV configuration and care should be taken to make sure that things are working as expected.

Corresponds to configuration file setting "proiv.virtualMachine.database.driver.sqlsrv.startupArgs".

Advanced "Enable locking hints" Slider

This advanced option allows the user to control whether PROIV adds any locking hints to the SQL it generates or any full-function SQL that are executed from PROIV logic. In the recommended config for SNAPSHOT READ COMMITTED this slider is OFF.

If the slider is OFF then no locking hints are added. This means that the following options are ignored and no locking hints are added:

If the slider is ON then the settings listed above will perform as expected. See below...

The presence of lock hints in SQL Server can have a significant impact on both the performance and the behaviour of SQL Server. This impact can be positive or negative depending upon the overall settings and so is an Advanced setting. Specifically it can affect the way that the isolation level behaves, changing the locking behaviour of SQL Server when multiple transactions are in play.

Corresponds to configuration file setting "proiv.virtualMachine.database.driver.sqlsrv.advanced.enableLockHints".

"Read Uncommitted" Slider

If the slider is OFF then reads of tables are done using "WITH (READPAST)" to avoid locking with other transactions. This means that any locked row will not be included in the returned rows. See READPAST for more details on the table hint added by this option.

If the slider is ON then "WITH (NOLOCK)" is used to read uncommitted (or dirty) values. As well as being applied to PROIV's own transparent SQL, it is also applied to full-function SQL that has no locking hints and is a SELECT statement. See NOLOCK for more details on the table hint added by this option.

In the (recommended) "SNAPSHOT READ COMMITTED" configuration above there will be no read locking and "WITH (READPAST)" has no effect.

Corresponds to configuration file setting "proiv.virtualMachine.database.driver.sqlsrv.readUncommitted".

"Insert UpdLock" Slider

This option is used to reduce potential deadlocks in applications that operate on shared data in the database. By using "WITH (UPDLOCK)" the locking behaviour in the queries that might modify data is changed. SQL Server will avoid deadlocks by allowing transactions to hold shared locks to data. Rather than a deadlock occuring if one or both modify the data the shared locks will be updated to Exclusive. One transaction will wait for the other to complete rather than having any pending changes rolled back.

See UPDLOCK for more information on the table hint added by this option.

If the slider is ON then "WITH (UPDLOCK)" is added to the SQL statement when updating the database.

If the slider is OFF then no hint is added.

Corresponds to configuration file setting "proiv.virtualMachine.database.driver.sqlsrv.insertWithUpdlock".

"Enable MARS" Slider

Enables multiple active record sets in the SQL Server connection. Essentially this will allow multiple transactions to be executed on the same connection. Although PROIV's transparent SQL will not do this, it is possible for PROIV logic to cause nested transactions to run.

This is the default behaviour of many other RDBMSs.

Corresponds to configuration file setting "proiv.virtualMachine.database.driver.sqlsrv.enableMars".

Low level options

The following options are provided for users who wish to have a fine level of control over the SQL Server connection. It will be the users responsibility to ensure that the settings are valid and that they do not conflict with each other. Do not use or change these settings unless you are aware of the effects and interactions that can occur.

"Isolation Level" Choices

This sets the transaction isolation level for the SQL Server connection. It will affect all transactions executed including PROIV's transparent SQL and full-function SQL inside PROIV logic.

The advantage of increasing the isolation between transactions is that it increases their reproducibilty and data consistency when executed concurrently. The disadvantage is that this is often achieved by increased locking between transactions. In PROIV transactions can run for a long period (e.g. a screen that commits changes explicitly using the PROIV Logic "COMMIT()" verb). So the choice of transaction isolation level is also guided by how you intend to use PROIV.

PROIV provides the following choices which correspond directly to SQL server's. They are in the order of providing increasing levels of consistency but at the cost of increased locking etc.

Transaction Isolation Level

PROIV Remarks

READ_UNCOMMITTED

Very similar to PROIV's default configuration.

READ_COMMITTED

Behaviour affected by "SNAPSHOT READ COMMITED" database option.

REPEATABLE_READ

SERIALIZABLE

SNAPSHOT

Will forcibly rollback transactions when there is a conflict.

"Concurrency" Choices

This sets the SQL_ATTR_CONCURRENCY attribute of the SQL Server connection. It will affect all transactions executed although updates will always use a minimum of LOCK if set to READ_ONLY. The choices are:

Concurrency

PROIV Remarks

READ_ONLY

Default option for SNAPSHOT_READ_COMMITTED configuration

LOCK

Likely to cause locking in PROIV clients accessing the same data

VALUES

ROWVER

DEFAULT

Uses the database default for concurrency

"Cursor Type" Choices

This sets the SQL_ATTR_CURSOR_TYPE attribute of the SQL Server connection. It will affect all cursors created The choices are:

Cursor Type

PROIV Remarks

FORWARD_ONLY

Default option for both recommended configurations.

FAST_FORWARD

This is a driver optimisation, please check it is supported before using.

STATIC

KEYSET

DYNAMIC

Comment on this topic

Topic ID: 560062