FAQ's - APPX Software, Inc. : APPX Utility : APPX System Administration :
Known issue with SQL Server SP4
http://support.microsoft.com/?kbid=899976The root of the problem is that MS changed some of the optimizer rules in SP4 so SQL Server is choosing an index scan over an index seek and locking way too many records as a result. In particular, since Appx creates parameterized queries, the SQL Server optimizer can't know (at the time that it plans the query) that the data type of a parameter matches the data type of the corresponding column. For example, Appx may generate a query like:
SELECT * FROM appx_dmo_prospect WHERE prospect_no = ?
Where the '?' is a parameter marker. SQL Server plans the query based on that text - it doesn't know the value of the parameter at planning time. Instead, each time we execute that plan, we provide a different value for the parameter. In SP4, SQL Server is taking a cautious approach and assuming that the data type of the parameter may not match the data type of the column and therefore, it scans (and locks) the whole index instead of seeking directly to the desired row (and locking only that row).
You can get around this problem (without rolling back to SP3) by following "Method 2" in the article above (don't forget to stop and re-start the server after adding the startup parameter).
What is the default field mapping for RDBMS?