From: Kalen Delaney on
There are two varieties of snapshot isolation: read committed snapshot and
full snapshot isolation. You should read about them in the SQL Server
documentation (Books Online) and here is a whitepaper to get more info:
http://msdn.microsoft.com/en-us/library/ms345124(SQL.90).aspx


--
HTH
Kalen
----------------------------------------
Kalen Delaney
SQL Server MVP
www.SQLServerInternals.com

"RG" <nobody(a)nowhere.com> wrote in message
news:#EbRGMAfKHA.5020(a)TK2MSFTNGP02.phx.gbl...
> This is a great piece of info. What other snapshot variations are there?
>
> Thanks,
>
> Roman
>
> "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
> news:Xns9CE07C1F0BDEFYazorman(a)127.0.0.1...
>> RG (nobody(a)nowhere.com) writes:
>>> Does the performance gain stem from that sql server doesn't need to
>>> check
>>> for "in middle of transaction" rows?
>>
>> The main gain is that you are not blocked by others. You also gain some
>> by the reduced amount of lock manangement, but that is not the major
>> part.
>>
>> That does not mean that you should use NOLOCK every now and then. Most of
>> the time when you want to avoid blocking, you should probably use some
>> snapshot variation.
>>
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>>
>> Links for SQL Server Books Online:
>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>> SQL 2000:
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>
From: Dan Guzman on
> I wasn't aware of a performance gain from the use of NO LOCK.

When NOLOCK is specified, the storage engine might choose to scan a table
using IAM pages instead of following the next logical page pointers of the
clustered index. Such an IAM scan can reduce disk seek time plus the
locking overhead is eliminated.

This is not to say that NOLOCK should be used to improve performance. Data
integrity is the major consideration and NOLOCK may result in rows being
skipped or duplicated in the result. If concurrency is a concern, a better
option is to set the database to use READ_COMMITTED_SNAPSHOT as mentioned by
others in this thread.

CREATE TABLE dbo.Foo(
Bar int NOT NULL CONSTRAINT PK_Foo PRIMARY KEY
,FooBar char(8000) NULL
);

WITH
a AS (SELECT 0 i UNION ALL SELECT 0),
b AS (SELECT 0 i FROM a b, a c, a d, a e, a f, a g)
INSERT INTO dbo.Foo (Bar)
SELECT ROW_NUMBER() OVER (ORDER BY i)
FROM b;

WITH
a AS (SELECT 0 i UNION ALL SELECT 0),
b AS (SELECT 0 i FROM a b, a c, a d, a e, a f, a g)
INSERT INTO dbo.Foo (Bar)
SELECT ROW_NUMBER() OVER (ORDER BY i) * -1
FROM b;

SELECT Bar FROM dbo.foo; --rows returned in order of the clustered index key
SELECT Bar FROM dbo.Foo WITH (NOLOCK); --rows returned in IAM order
SELECT Bar FROM dbo.Foo WITH (TABLOCK); --rows returned in IAM order

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/


From: Jay on
I might be confusing this with Informix, but I thought that any SELECT that
only needed data from and index, would by default, only read the index. This
is because the concurrency applied to the data pages, applies to the index
pages as well and the QO knows it can get data faster and with fewer reads
from an index page.

What you're saying implies that in READ COMMITTED isolation, the index pages
are not guaranteed to be consistent, which doesn't make sense.

"Dan Guzman" <guzmanda(a)nospam-online.sbcglobal.net> wrote in message
news:D64B7ECA-90CC-4424-AD1C-13E6B1533BAF(a)microsoft.com...
>> I wasn't aware of a performance gain from the use of NO LOCK.
>
> When NOLOCK is specified, the storage engine might choose to scan a table
> using IAM pages instead of following the next logical page pointers of the
> clustered index. Such an IAM scan can reduce disk seek time plus the
> locking overhead is eliminated.
>
> This is not to say that NOLOCK should be used to improve performance.
> Data integrity is the major consideration and NOLOCK may result in rows
> being skipped or duplicated in the result. If concurrency is a concern, a
> better option is to set the database to use READ_COMMITTED_SNAPSHOT as
> mentioned by others in this thread.
>
> CREATE TABLE dbo.Foo(
> Bar int NOT NULL CONSTRAINT PK_Foo PRIMARY KEY
> ,FooBar char(8000) NULL
> );
>
> WITH
> a AS (SELECT 0 i UNION ALL SELECT 0),
> b AS (SELECT 0 i FROM a b, a c, a d, a e, a f, a g)
> INSERT INTO dbo.Foo (Bar)
> SELECT ROW_NUMBER() OVER (ORDER BY i)
> FROM b;
>
> WITH
> a AS (SELECT 0 i UNION ALL SELECT 0),
> b AS (SELECT 0 i FROM a b, a c, a d, a e, a f, a g)
> INSERT INTO dbo.Foo (Bar)
> SELECT ROW_NUMBER() OVER (ORDER BY i) * -1
> FROM b;
>
> SELECT Bar FROM dbo.foo; --rows returned in order of the clustered index
> key
> SELECT Bar FROM dbo.Foo WITH (NOLOCK); --rows returned in IAM order
> SELECT Bar FROM dbo.Foo WITH (TABLOCK); --rows returned in IAM order
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
>


From: Jay on
"Dan Guzman" <guzmanda(a)nospam-online.sbcglobal.net> wrote in message
news:D64B7ECA-90CC-4424-AD1C-13E6B1533BAF(a)microsoft.com...
>> I wasn't aware of a performance gain from the use of NO LOCK.
>
> When NOLOCK is specified, the storage engine might choose to scan a table
> using IAM pages instead of following the next logical page pointers of the
> clustered index. Such an IAM scan can reduce disk seek time plus the
> locking overhead is eliminated.
>

I might be confusing this with Informix, but I thought that any SELECT that
only needed data from and index, would by default, only read the index. This
is because the concurrency applied to the data pages, applies to the index
pages as well and the QO knows it can get data faster and with fewer reads
from an index page.

What you're saying implies that in READ COMMITTED isolation, the index pages
are not guaranteed to be consistent, which doesn't make sense.


From: RG on
In the least, this sounds like I shouldn't do select with nolock on a table
that is frequently updated. Is that right?

Thanks again
"Dan Guzman" <guzmanda(a)nospam-online.sbcglobal.net> wrote in message
news:D64B7ECA-90CC-4424-AD1C-13E6B1533BAF(a)microsoft.com...
>> I wasn't aware of a performance gain from the use of NO LOCK.
>
> When NOLOCK is specified, the storage engine might choose to scan a table
> using IAM pages instead of following the next logical page pointers of the
> clustered index. Such an IAM scan can reduce disk seek time plus the
> locking overhead is eliminated.
>
> This is not to say that NOLOCK should be used to improve performance.
> Data integrity is the major consideration and NOLOCK may result in rows
> being skipped or duplicated in the result. If concurrency is a concern, a
> better option is to set the database to use READ_COMMITTED_SNAPSHOT as
> mentioned by others in this thread.
>
> CREATE TABLE dbo.Foo(
> Bar int NOT NULL CONSTRAINT PK_Foo PRIMARY KEY
> ,FooBar char(8000) NULL
> );
>
> WITH
> a AS (SELECT 0 i UNION ALL SELECT 0),
> b AS (SELECT 0 i FROM a b, a c, a d, a e, a f, a g)
> INSERT INTO dbo.Foo (Bar)
> SELECT ROW_NUMBER() OVER (ORDER BY i)
> FROM b;
>
> WITH
> a AS (SELECT 0 i UNION ALL SELECT 0),
> b AS (SELECT 0 i FROM a b, a c, a d, a e, a f, a g)
> INSERT INTO dbo.Foo (Bar)
> SELECT ROW_NUMBER() OVER (ORDER BY i) * -1
> FROM b;
>
> SELECT Bar FROM dbo.foo; --rows returned in order of the clustered index
> key
> SELECT Bar FROM dbo.Foo WITH (NOLOCK); --rows returned in IAM order
> SELECT Bar FROM dbo.Foo WITH (TABLOCK); --rows returned in IAM order
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
>