General information

The jTDS driver is used to connect to MS SQL Server. Most questions can be solved by the jTDS FAQ:

http://jtds.sourceforge.net/faq.html

Slow processing and locking situations

Especially when using multiple connections e.g. by different in/output steps to the same database we often got reports about slow processing and even locking situations.

User reported performance improved significantly by disabling the connection pooling. (tested with MS SQL Server 2000)

Another issue is found together with the Update step, it has two statements: one for looking up the data and one for the update. This can lead to deadlock situations even when we use one (since version 3.x) or two connections.

User experienced deadlocks after creating indexes, therefor I suspect the creation / updating of the index locks the table for a certain time.

I looked into the MS SQL documentation and found the reasons for this:
Fill factor: http://technet.microsoft.com/en-us/library/ms177459.aspx
especialy:

"Page Splits and Performance Considerations

When a new row is added to a full index page, the Database Engine moves approximately half the rows to a new page to make room for the new row. This reorganization is known as a page split. A page split makes room for new records, but can take time to perform and is a resource intensive operation. Also, it can cause fragmentation that causes increased I/O operations. A correctly chosen fill factor value can reduce the potential for page splits by providing enough space for index expansion as data is added to the underlying table."

Also see "Creating indexes" on http://technet.microsoft.com/en-us/library/ms190197.aspx:
"Performance considerations: [...] Creating the index offline or online.
When an index is created offline (the default), exclusive locks are held on the underlying table until the transaction creating the index has completed. The table is inaccessible to users while the index is being created."

--> Due to these two informations I propose to try to use an online index first and/or change the fill factor.

The syntax is described over here: http://technet.microsoft.com/en-us/library/ms188783.aspx

Options for creating an index are:

PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = { ON | OFF }
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| DROP_EXISTING = { ON | OFF }
| ONLINE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| MAXDOP = max_degree_of_parallelism

We look forward to your experiences and comments.