Isolation levels Demo

READ UNCOMMITTED

BEGIN TRAN

UPDATE  dbo.TestIsolationLevels

SET     EmpSalary = 25000

WHERE   EmpID = 2900

Now select the value that's being updated using the following (in a separate query window):

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SET NOCOUNT ON

GO

SELECT EmpID, EmpName, EmpSalary

FROM dbo.TestIsolationLevels

WHERE EmpID = 2900

READ COMMITTED (snapshot)

ALTER DATABASE [YourDB] SET READ_COMMITTED_SNAPSHOT ON

GO

BEGIN TRAN

UPDATE  dbo.TestIsolationLevels

SET     EmpSalary = 25000

WHERE   EmpID = 2900

Now in a separate query window:

SELECT  EmpID, EmpName, EmpSalary

FROM    dbo.TestIsolationLevels WITH (READCOMMITTEDLOCK)

WHERE   EmpID = 2900

The query will hang as it is waiting for the key lock on EmpID to be released. Allow the query to execute by issuing in your first window:

ROLLBACK;

READ COMMITTED

ALTER DATABASE [YourDB] SET READ_COMMITTED_SNAPSHOT ON

GO

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SET NOCOUNT ON

GO

BEGIN TRAN

UPDATE  dbo.TestIsolationLevels

SET     EmpSalary = 25000

WHERE   EmpID = 2900

Now in a separate query window:

SELECT  EmpID, EmpName, EmpSalary

FROM    dbo.TestIsolationLevels

WHERE   EmpID = 2900

The query will return the last consistent row of data. Note the empSalary column is 22000.00 despite the transaction being open and the update written (but uncommitted). This is correct, and the SELECT is reading from the previous row version, not the present state of the row. This is compliant with the C in ACID - consistency.

SNAPSHOT isolation

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SET NOCOUNT ON

GO

BEGIN TRAN

UPDATE  dbo.TestIsolationLevels

SET     EmpSalary = 25000

WHERE   EmpID = 2900

Now in a separate query window:

SELECT  EmpID, EmpName, EmpSalary

FROM    dbo.TestIsolationLevels

WHERE   EmpID = 2900

You will note that, like READ COMMITTED, the correct snapshot of the data row is returned, yielding empSalary = 22000, which is consistent and correct. Rollback the transaction.

REPEATABLE READ

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

SET NOCOUNT ON

GO

BEGIN TRAN

SELECT  EmpID, EmpName, EmpSalary

FROM    dbo.TestIsolationLevels

WHERE   EmpID = 2900

WAITFOR DELAY '00:00:10'

SELECT  EmpID, EmpName, EmpSalary

FROM    dbo.TestIsolationLevels

WHERE   EmpID = 2900

COMMIT

Run the below while the above is executing:

BEGIN TRAN

UPDATE  dbo.TestIsolationLevels

SET     EmpSalary = 25000

WHERE   EmpID = 2900

COMMIT

Problem with this isolation level is, we can see phantom rows (newly inserted) within the transaction.

Serializable Isolation

SERIALIZABLE has all the features of READ COMMITTED, REPEATABLE READ but also ensures concurrent transactions are treated as if they had been run in serial. This means guaranteed repeatable reads, and no phantom rows. Be warned, however, that this (and to some extent, the previous two isolation levels) can cause large performance losses as concurrent transactions are effectively queued.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SET NOCOUNT ON

GO

BEGIN TRAN

SELECT  EmpName

FROM    dbo.TestIsolationLevels

WAITFOR DELAY '00:00:10'

SELECT  EmpName

FROM    dbo.TestIsolationLevels

COMMIT

Run the below while the above is executing:

BEGIN TRAN

INSERT INTO dbo.TestIsolationLevels VALUES (3427, 'Phantom Employee 1', 30000)

COMMIT

DBCC Commands

1.       DBCC INPUTBUFFER       Displays the last statement sent from a client to an instance of Microsoft SQL Server.

DBCC INPUTBUFFER ( session_id [ , request_id ])

DBCC INPUTBUFFER (52);

2.       DBCC OPENTRAN             helps to identify active transactions that may be preventing log truncation. DBCC OPENTRAN displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the transaction log of the specified database.

DBCC OPENTRAN

3.       DBCC PROCCACHE           Displays information in a table format about the procedure cache.

4.       DBCC SHOW_STATISTICS              displays current query optimization statistics for a table or indexed view. The query optimizer uses statistics to estimate the cardinality or number of rows in the query result, which enables the query optimizer to create a high quality query plan.

DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target )

DBCC SHOW_STATISTICS ("Person.Address", AK_Address_rowguid);

5.       DBCC CHECKCONSTRAINTS          Checks the integrity of a specified constraint or all constraints on a specified table in the current database.

DBCC CHECKCONSTRAINTS(Table1)

6.       DBCC CHECKDB                 Checks the logical and physical integrity of all the objects in the specified database. Validates the contents of every indexed view in the database.

7.       DBCC CHECKIDENT          Checks the current identity value for the specified table in SQL Server 2012 and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.

DBCC CHECKIDENT ('Person.AddressType')

8.       DBCC CHECKTABLE          Checks the integrity of all the pages and structures that make up the table or indexed view.

DBCC CHECKTABLE ("HumanResources.Employee")

9.       DBCC CLEANTABLE          Reclaims space from dropped variable-length columns in tables or indexed views.

DBCC CLEANTABLE (AdventureWorks2012,"Production.Document", 0)

10.   DBCC DROPCLEANBUFFERS         Removes all clean buffers from the buffer pool.

11.   DBCC FREEPROCCACHE                 Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool.

12.   DBCC INDEXDEFRAG       Defragments indexes of the specified table or view.

DBCC INDEXDEFRAG (AdventureWorks2012, "Production.Product", PK_Product_ProductID)

13.   DBCC SHRINKDATABASE               Shrinks the size of the data and log files in the specified database.

DBCC SHRINKDATABASE (UserDB, 10);

DBCC SHRINKDATABASE (AdventureWorks2012, TRUNCATEONLY);

Query tuning tips

1.       If you currently have a query that uses NOT IN, which offers poor performance because the SQL Server optimizer has to use a scan to perform this activity, instead try to use one of the following options, all of which offer better performance:

Use EXISTS or NOT EXISTS.

Use IN.

Perform a LEFT OUTER JOIN and check for a NULL condition.

2.       If you run into a situation where a WHERE clause is not sargable because of the use of a function on the right side of an equality sign (and there is no other way to rewrite the WHERE clause), consider creating an index on a computed column instead. This way, you avoid the non-sargable WHERE clause altogether, using the results of the function in your WHERE clause instead.

3.       If you find that SQL Server uses a SCAN instead of an SEEK when you use an IN or OR clause as part of your WHERE clause, even when those columns are covered by an index, consider using an index hint to force the Query Optimizer to use the index.

For example:

SELECT * FROM tblTaskProcesses WHERE nextprocess = 1 AND processid IN (8,32,45)

--takes about 3 seconds, while:

SELECT * FROM tblTaskProcesses (INDEX = IX_ProcessID) WHERE nextprocess = 1 AND processid IN (8,32,45)

--returns in under a second.

4.       When you have a choice of using the IN or the BETWEEN clauses in your Transact-SQL, you will generally want to use the BETWEEN clause, as it is much more efficient. For example:

SELECT customer_number, customer_name  FROM customer

WHERE customer_number in (1000, 1001, 1002, 1003, 1004)

--Is much less efficient than this:

SELECT customer_number, customer_name  FROM customer WHERE customer_number BETWEEN 1000 and 1004

5.       Using the SUBSTRING function can force a scan instead of allowing the optimizer to use an index (assuming there is one). If the substring you are searching for does not include the first character of the column you are searching for, then a scan is performed. If possible, you should avoid using the SUBSTRING function and use the LIKE condition instead, for better performance.

6.       Where possible, avoid string concatenation in your Transact-SQL code, as it is not a fast process, contributing to overall slower performance of your application. If you need to use heavy string processing, consider creating a CLR function, which potentially will provide greater performance.

7.       If you want to boost the performance of a query that includes an AND operator in the WHERE clause, consider the following:

·         Of the search criterions in the WHERE clause, at least one of them should be based on a highly selective column that has an index.

·         If at least one of the search criterions in the WHERE clause is not highly selective, consider adding indexes to all of the columns referenced in the WHERE clause (assuming they will be selective).

·         If none of the columns in the WHERE clause are selective enough to use an index on their own, consider creating a covering/included index for the query.

8.       If you have a query that uses ORs and it not making the best use of indexes, consider rewriting it as a UNION ALL, and then testing performance.

9.       If your SELECT statement contains a HAVING clause, write your query so that the WHERE clause does most of the work (removing undesired rows) instead of the HAVING clause do the work of removing undesired rows. If the WHERE clause is used to eliminate as many of the undesired rows as possible, this means the GROUP BY and the HAVING clauses will have less work to do, boosting the overall performance of the query.

10.    The GROUP BY clause can be used with or without an aggregate function. But if you want optimum performance, don’t use the GROUP BY clause without an aggregate function. This is because you can accomplish the same end result by using the DISTINCT option instead, and it is faster.

11.    If you run into situations where perception is more important than raw performance, consider using the FAST query hint. The FAST query hint is used with the SELECT statement using this form:

·         OPTION(FAST number_of_rows)

·         where number_of_rows is the number of rows that are to be displayed as fast as possible.

12.    Avoid using variables in the WHERE clause of a query located in a batch file. The reason the indexes may not be used is because the Query Analyzer does not know the value of the variables when it selects an access method to perform the query. If you cannot avoid using variables in the WHERE clauses of batch scripts, consider using an INDEX query hint to tell the Query Optimizer to use the available indexes instead of ignoring them and performing a table scan.

13.    Non-sargable search arguments in the WHERE clause, such as “IS NULL”, “<>”, “!=”, “!>”, “!<”, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE”, and “LIKE ‘%500′” generally prevents (but not always) the query optimizer from using a useful index to perform a search. In addition, expressions that includes a function on a column, expressions that have the same column on both sides of the operator, or comparisons against a column (not a constant), are not sargable.