Nolock Hint
in [Server Configuration]
|
Prev: cannot obtain provider factory for data provider named 'microsoft.sqlserverce.client3.5'
Next: Can't authenticate in SQL Mgmt Studio
From: Kalen Delaney on 13 Dec 2009 11:15 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 13 Dec 2009 12:08 > 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 13 Dec 2009 12:36 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 13 Dec 2009 12:40 "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 13 Dec 2009 13:34
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/ > > |