SQL SERVER – Check the Isolation Level with DBCC useroptions

In recent consultancy project coordinator asked me – “can you tell me what is the isolation level for this database?” I have worked with different isolation levels but have not ever queried database for the same. I quickly looked up bookonline and found out the DBCC command which can give me the same details.

You can run the DBCC UserOptions command on any database to get few details about dateformat, datefirst as well isolation level.

DBCC useroptions

Set Option                  Value
--------------------------- --------------
textsize                    2147483647
language                    us_english
dateformat                  mdy
datefirst                   7
lock_timeout                -1
quoted_identifier           SET
arithabort                  SET
ansi_null_dflt_on           SET
ansi_warnings               SET
ansi_padding                SET
ansi_nulls                  SET
concat_null_yields_null     SET
isolation level             read committed

I thought this was very handy script, which I have not used earlier. Thanks Gary for asking right question.

Note: This optional will only give you isolation for current connection where the command has ran and not at server level.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Simple Example of Snapshot Isolation – Reduce the Blocking Transactions

To learn any technology and move to a more advanced level, it is very important to understand the fundamentals of the subject first. Today, we will be talking about something which has been quite introduced a long time ago but not properly explored when it comes to the isolation level.

Snapshot Isolation was introduced in SQL Server in 2005. However, the reality is that there are still many software shops which are using the SQL Server 2000, and therefore cannot be able to maintain the Snapshot Isolation. Many software shops have upgraded to the later version of the SQL Server, but their respective developers have not spend enough time to upgrade themselves with the latest technology. “It works!” is a very common answer of many when they are asked about utilizing the new technology, instead of backward compatibility commands.

In one of the recent consultation project, I had same experience when developers have “heard about it” but have no idea about snapshot isolation. They were thinking it is the same as Snapshot Replication – which is plain wrong. This is the same demo I am including here which I have created for them.

In Snapshot Isolation, the updated row versions for each transaction are maintained in TempDB. Once a transaction has begun, it ignores all the newer rows inserted or updated in the table.

Let us examine this example which shows the simple demonstration. This transaction works on optimistic concurrency model. Since reading a certain transaction does not block writing transaction, it also does not block the reading transaction, which reduced the blocking.

First, enable database to work with Snapshot Isolation. Additionally, check the existing values in the table from HumanResources.Shift.

ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON
GO
SELECT ModifiedDate
FROM HumanResources.Shift
GO

Now, we will need two different sessions to prove this example.

First Session: Set Transaction level isolation to snapshot and begin the transaction. Update the column “ModifiedDate” to today’s date.

-- Session 1
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
UPDATE
HumanResources.Shift
SET ModifiedDate = GETDATE()
GO

Please note that we have not yet been committed to the transaction. Now, open the second session and run the following “SELECT” statement. Then, check the values of the table. Please pay attention on setting the Isolation level for the second one as “Snapshot” at the same time when we already start the transaction using BEGIN TRAN.

-- Session 2
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
SELECT
ModifiedDate
FROM HumanResources.Shift
GO

You will notice that the values in the table are still original values. They have not been modified yet. Once again, go back to session 1 and commit the transaction.

-- Session 1
COMMIT

After that, go back to Session 2 and see the values of the table.

-- Session 2
SELECT ModifiedDate
FROM HumanResources.Shift
GO

You will notice that the values are yet not changed and they are still the same old values which were there right in the beginning of the session.

Now, let us commit the transaction in the session 2. Once committed, run the same SELECT statement once more and see what the result is.

-- Session 2
COMMIT
SELECT
ModifiedDate
FROM HumanResources.Shift
GO

You will notice that it now reflects the new updated value.

I hope that this example is clear enough as it would give you good idea how the Snapshot Isolation level works. There is much more to write about an extra level, READ_COMMITTED_SNAPSHOT, which we will be discussing in another post soon.

If you wish to use this transaction’s Isolation level in your production database, I would appreciate your comments about their performance on your servers. I have included here the complete script used in this example for your quick reference.

ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON
GO
SELECT ModifiedDate
FROM HumanResources.Shift
GO
-- Session 1
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
UPDATE
HumanResources.Shift
SET ModifiedDate = GETDATE()
GO
-- Session 2
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
SELECT
ModifiedDate
FROM HumanResources.Shift
GO
-- Session 1
COMMIT
-- Session 2
SELECT ModifiedDate
FROM HumanResources.Shift
GO
-- Session 2
COMMIT
SELECT
ModifiedDate
FROM HumanResources.Shift
GO

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthirty Author Visit – SQL SERVER – User Group Meeting – Ahmedabad – August 30, 2008

I always enjoy participating in SQL Server User Group. We had recent meeting of Ahmedabad User Group on August 30. We had many things discussed in meeting.

I enjoyed meeting fellows from different company who visited user group. The major discussion we had was quality of programmers and quality of work done by programmers. We all felt that looking at current market everybody is rushing for IT jobs. Finding right job is difficult and finding right candidate for job is even more difficult. User groups are the place for good developers to show up for good networking with industry leads and fellow developers.

One question I constantly discuss with user group president Jacob Sebastian (SQL Server – MVP) is that not many people are aware of what is User Group.

User group is place to meet fellow developers like us and learn something new at no cost. User groups are free and there is no fee. A users’ group may provide its members (and sometimes the general public as well) with one or more of the following services:

  • periodic meetings
  • public lectures
  • a newsletter
  • a library of media or tools
  • a software archive
  • an online presence such as a dial-up BBS or Internet website
  • swap meets
  • technical support
  • social events

During latest meeting we all enjoyed great demonstration of SQL Server Transaction Isolations Levels by Jacob Sebastian. He has explained lots of details which are interesting to learn. His presentation was followed up by small discussion about Common Table Expression by Pinal Dave.

If you have missed meeting on August 30, I suggest you attend the next meeting for sure. It is really great to meet SQL Server Experts in one place and learn so much in less than 2 hours. I will keep all of you posted with updated news about next meeting of SQL Server User Group of Ahmedabad.

Reference : Pinal Dave (http://blog.SQLAuthority.com) , Wiki – User Group