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:
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, ...
Get Microsoft SQL Server 2012 Bible now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.