SQL Server Performance Tuning – 3

SQL Server Performance Tuning – 3

1. What is Isolation level in SQL Server?

Isolation level defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions.

2. How many types of Isolation levels are available in SQL Server?

• READ COMMITTED
• READ UNCOMMITTED
• REPEATABLE READ
• SERIALIZABLE
• SNAPSHOT

3. What is READ COMMITTED Isolation Level?

A query in the current transaction cannot read data modified by another transaction that has not yet committed, thus preventing dirty reads. However, data can still be modified by other transactions between issuing statements within the current transaction, so nonrepeatable reads and phantom reads are still possible. The isolation level uses shared locking or row versioning to prevent dirty reads, depending on whether theREAD_COMMITTED_SNAPSHOT database option is enabled. Read Committed is the default isolation level for all SQL Server databases.

4. What is READ UNCOMMITTED Isolation Level?

A query in the current transaction can read data modified within another transaction but not yet committed. The database engine does not issue shared locks when Read Uncommitted is specified, making this the least restrictive of the isolation levels. As a result, it’s possible that a statement will read rows that have been inserted, updated or deleted, but never committed to the database, a condition known as dirty reads. It’s also possible for data to be modified by another transaction between issuing statements within the current transaction, resulting in nonrepeatable reads orphantom reads.

5. What is REPEATABLE READ Isolation Level?

A query in the current transaction cannot read data modified by another transaction that has not yet committed, thus preventing dirty reads. In addition, no other transactions can modify data being read by the current transaction until it completes, eliminating nonrepeatable reads. However, if another transaction inserts new rows that match the search condition in the current transaction, in between the current transaction accessing the same data twice, phantom rows can appear in the second read.

6. What is SERIALZABLE Isolation Level?

A query in the current transaction cannot read data modified by another transaction that has not yet committed. No other transaction can modify data being read by the current transaction until it completes, and no other transaction can insert new rows that would match the search condition in the current transaction until it completes. As a result, the Serializable isolation level prevents dirty reads, nonrepeatable reads, and phantom reads. However, it can have the biggest impact on performance, compared to the other isolation levels.

7. What is SNAPSHOT Isolation Level?

A statement can use data only if it will be in a consistent state throughout the transaction. If another transaction modifies data after the start of the current transaction, the data is not visible to the current transaction. The current transaction works with a snapshot of the data as it existed at the beginning of that transaction. Snapshot transactions do not request locks when reading data, nor do they block other transactions from writing data. In addition, other transactions writing data do not block the current transaction for reading data. As with the Serializable isolation level, the Snapshot level prevents dirty reads, nonrepeatable reads and phantom reads.

8. What is the default Isolation level of SQL Server databases?

Read committed (Database Engine default level)

9. How can we change the Isolation level of a database?

USE AdventureWorks2008R2;

GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

GO

10. What is controlled by Transaction isolation levels?

Transaction isolation levels control:

• Whether locks are taken when data is read and what type of locks are requested.
• How long the read locks are held.
• Whether a read operation referencing rows modified by another transaction:

o    Blocks until the exclusive lock on the row is freed.

o    Retrieves the committed version of the row that existed at the time the statement or transaction started.

o    Reads the uncommitted data modification.

11. What are dirty reads?

Reading uncommitted modifications are called Dirty Reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction, thus getting incorrect or wrong data is possible.

12. What are Phantom reads?

Data getting changed in current transaction by other transactions is called Phantom Reads. New rows can be added by other transactions, so you get different number of rows by firing same query in current transaction.

13. In which Isolation level dirty reads occur?

Read uncommitted

14. What are HINTS in SQL Server Query?

Hints are options or strategies specified for enforcement by SQL Server query processor on SELECT, INSERT, UPDATE, or DELETE statements. These hints override any execution plan that query optimizer might select for a query.

15. What are various types of Hints available in SQL Server?

• Query Hintstell the optimizer to apply this hint throughout the execution of the entire query.
• Join Hintstell the optimizer to use a particular join at a particular point in the query
• Table Hintscontrol table scans and the use of a particular index for a table

16. What is Parameter sniffing?

SQL Server compiles the stored procedures using (sniffing) the parameters send the first time the procedure is compiled and put it in plan cache. After that every time the procedure executed again, SQL Server retrieves the execution plan from the cache and uses it (unless there is a reason for recompilation). The potential problem arises when the first time the stored procedure is executed, the set of parameters generate an acceptable plan for that set of parameters but very bad for other more common sets of parameters.

17. What are the various workarounds to overcome this problem?

There are some workarounds to overcome this problem.

• OPTION (RECOMPILE)
• OPTION (OPTIMIZE FOR (@VARIABLE=VALUE))
• OPTION (OPTIMIZE FOR (@VARIABLE UNKNOWN))
• Use local variables

18. What is a lock?

SQL Server uses locks as a mechanism to bring the consistency at the same time build the concurrency for the system. Locks are inevitable in any database world and those are also fundamental building blocks.

19. What are the different types of lock modes in SQL Serve?

• Shared locks (S)
• Exclusive locks (X)
• Intent lock
• Schema lock
• Bulk update locks (BU)
• KEY RANGE Lock

20. What is SHARED lock?

Shared locks (S): Used when performing read-only operations against database. Resources locked with a shared lock are available for SELECT, but not for modification.

21. What is EXCLUSIVE lock?

Exclusive locks (X): Used for operations that modifies data. INSERT, UPDATE, and DELETE require exclusive locks. No more than one transaction can have an exclusive lock on a resource. If an exclusive lock exists on a resource, no other transaction can access that resource.

22. What is INTENT lock?

Establishes a locking hierarchy. For example, if a transaction has an exclusive lock on a row, SQL Server places an intent lock on the table. When another transaction requests a lock on a row in the table, SQL Server knows to check the rows to see if they have locks. If a table does not have intent lock, it can issue the requested lock without checking each row for a lock.

23. What is SCHEMA lock?

Used to prevent a table or index that is being used by another session from being dropped or its schema being modified. When a resource is locked with a schema lock, the object cannot be accessed.

24. What is BULK UPDATE lock?

Bulk update locks (BU): Used to prevent other processes from accessing a table while a bulk load procedure is being processed. It will, however, allow processing of concurrent bulk load processes, which allows you to run parallel loads. A bulk load procedure is one performed by using bulk copy program (bcp) or BULK INSERT.

25. What is KEY RANGE lock?

Locks taken in serializable isolation level for locking ranges of data. These kind of locks can be taken only on keys.

26. What is lock escalation?

Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead while increasing the probability of concurrency contention.

Applies to: SQL Server 2008 R2 and higher versions.

27. What triggers lock escalation?

Lock escalation is triggered when lock escalation is not disabled on the table by using the ALTER TABLE SET LOCK_ESCALATION option, and when either of the following conditions exists:

• A single Transact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table or index.
• A single Transact-SQL statement acquires at least 5,000 locks on a single partition of a partitioned table and the ALTER TABLE SET LOCK_ESCALATION option is set to AUTO.
• The number of locks in an instance of the Database Engine exceeds memory or configuration thresholds.
• If locks cannot be escalated because of lock conflicts, the Database Engine periodically triggers lock escalation at every 1,250 new locks acquired.

28. Is it possible to disable Lock escalation in SQL Server?

Yes, It is possible to disable lock escalation.

29. How can we disable Lock escalation in SQL Server?

With SQL Server 2008 you can also specify escalation rules on the table level with ALTER TABLE SET LOCK_ESCALATION statement. There are 3 available modes:

• DISABLE – lock escalation on specific table is disabled
• TABLE (default) – default behavior of lock escalation – locks are escalated to the table level.
• AUTO – if table is partitioned, locks would be escalated to partition level when table is partitioned or on table level if table is not partitioned

30. What is the least restrictive type of lock?

The least restrictive type of lock is a shared lock.

31. What is the most restrictive lock?

The most restrictive type of lock is a schema-modification.