Practice 1 Create a simple table with one column and no primary key. Insert two rows into the table. Start a transaction (without committing it), and update one of the rows in the table. In another connection, try to read the unaffected row, both rows, and the affected row, and watch the result. Do this for all different isolation levels (including read committed snapshot), and observe the differences in behavior.
Create a simple table with one integer column, and declare it as the table's primary key. Within a
Table Hint (Transact-SQL)
Using Snapshot Isolation
"The Rational Guide to SQL Server 2005 Service Broker" by Roger Wolter (Rational Press, 2006)
The choice of transaction isolation level affects both query results and performance because of blocking and row versioning.
Use the least
When a more restrictive transaction isolation level is required, consider applying table locking hints rather than specifying transaction isolation level on the session level using the SET TRANSACTION ISOLATION LEVEL statement.
Keep transactions short. Open the transaction as late as possible, and close it as early as possible.
Design transactions to minimize deadlocks.
Consider alternate solutions to locking by using the @@ROWCOUNT function and the OUTPUT clause.
When not rolling back a transaction in a try/catch block, always verify that the transaction is not uncommittable by querying the XACT_STATE() function.
Database optimization is a compelling part of the database developer's job role. The difference in performance between a well-optimized database and a non-optimized database can be
The task of optimizing a database can be broken down into
Exam objectives in this chapter:
Optimize and tune queries for performance.
Evaluate query performance.
Analyze query plans.
Modify queries to improve performance.
Test queries for improved performance.
Detect locking problems.
Modify queries to optimize client and server performance.
Rewrite subqueries to joins.
Design queries that have search arguments (SARGs).
Convert single-row statements into set-based queries.
Optimize indexing strategies.
Design an index strategy.
Analyze index use across an application.
Add, remove, or redesign indexes.
Optimize data storage.
Choose column data types to reduce storage requirements across the enterprise.
Design appropriate use of varchar across the enterprise.
Denormalize entities to minimize page reads per query.
Optimize table width.