SQLServer performance tips

1) SQLServer by default holds a lot of extra locks on result sets during a transaction regardless of isolation level.  With a load this can be a problem.  The way to prevent these locks is with a lock hint in the query such as "select somecolumn from sometable with (nolock)".  This solves the problem but also creates one with CMP since JBoss is generating all of your queries.  You can solve this by editing standardjboss-jdbc.xml (or standardjbosscmp-jdbc.xml) and enabling row locking and then modifying  the row locking template for SQLServer from (xlock) to (nolock).  Kind of a hack, but works.  If you add the nolock, it is important to use optimistic locking to circum vent reading uncommitted data.

2) The jTDS JDBC driver is faster than the stock MS JDBC driver. Versions prior to 0.9 had a bug with millisecond rounding. This was fixed in release 0.9 of jTDS.

3) With both jTDS and the stock MS JDBC driver turning off unicode parameters might yield a big performance gain if the character columns are of type CHAR/VARCHAR (as opposed to NCHAR/NVARCHAR).