Microsoft SQL Server 2012 Bible

Transaction Performance Strategies

Transaction integrity theory can seem daunting at first, and SQL Server has numerous tools to control transaction isolation. If the database is low usage or primarily read-only, transaction locking and blocking shouldn't be a problem. However for heavy-usage OLTP databases, you need to apply the theory and working knowledge from this chapter using these strategies. Also if you mix reporting and OLTP systems, you potentially face blocking issues because reporting systems generally place locks at page or table level, which isn't ideal for your OLTP system, which often requires more granular row level locks.

Make sure you consider the following points when designing your application for performance:

1. Begin with a well-designed database schema: Start with a clean, simplified schema to reduce the number of unnecessary joins and reduce the amount of code used to shuttle data from bucket to bucket.

2. Use efficient set-based code, rather than slow iterative cursors or loops. However, there is a tradeoff. Large set-based operations can cause locking and blocking, so there must be a fine line between large and small sets when modifying data.

3. Use a good indexing strategy to eliminate unnecessary table scans and to speed transactions.

To reduce the severity of a locking problem, do the following:

• Evaluate and test using the read committed snapshot isolation level. Depending on your error handling, application flexibility, and hardware capabilities, ...