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: Jay on 13 Dec 2009 13:44 Before snapshots were available, I would have said: bite the bullet and make sure your users know it's dirty data. However, now I say: it depends on what you want from the query and if your DBA will cooperate. "RG" <nobody(a)nowhere.com> wrote in message news:6BB21654-A36D-4109-90C4-89448217633A(a)microsoft.com... > 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/ >> >> >
From: Erland Sommarskog on 13 Dec 2009 15:36 Jay (spam(a)nospam.org) writes: > "Dan Guzman" <guzmanda(a)nospam-online.sbcglobal.net> wrote in message > news:D64B7ECA-90CC-4424-AD1C-13E6B1533BAF(a)microsoft.com... >> 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. I don't know about Informix, but the above is certainly true for SQL Server. (And I would expect it to be true for any DBMS.) > What you're saying implies that in READ COMMITTED isolation, the index > pages are not guaranteed to be consistent, which doesn't make sense. I don't see how that is implied from what Dan says. I have not thought much about why IAM scans are not used in READ COMMITTED, but I assume the reason is that an IAM scan in that mode would require the Index Allocation Map to be lock for the duration of the scan, which would rule out all inserts against the table. -- 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: Erland Sommarskog on 13 Dec 2009 15:38 RG (nobody(a)nowhere.com) writes: > In the least, this sounds like I shouldn't do select with nolock on a > table that is frequently updated. Is that right? Depends. If you are just looking at data out of curiousity, then you should use NOLOCK. But if you are running a check to see whether an account has sufficient cash holdings, you should not. -- 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: Jay on 13 Dec 2009 16:08 Uh, duh?!?! The statements: "but the above is certainly true for SQL Server" and "I have not thought much about why IAM scans are not used in READ COMMITTED" are inconsistent. Either the QO will chose the index, or it won't. "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CE0DBD389F57Yazorman(a)127.0.0.1... > Jay (spam(a)nospam.org) writes: >> "Dan Guzman" <guzmanda(a)nospam-online.sbcglobal.net> wrote in message >> news:D64B7ECA-90CC-4424-AD1C-13E6B1533BAF(a)microsoft.com... >>> 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. > > I don't know about Informix, but the above is certainly true for > SQL Server. (And I would expect it to be true for any DBMS.) > >> What you're saying implies that in READ COMMITTED isolation, the index >> pages are not guaranteed to be consistent, which doesn't make sense. > > I don't see how that is implied from what Dan says. > > I have not thought much about why IAM scans are not used in READ > COMMITTED, > but I assume the reason is that an IAM scan in that mode would require > the Index Allocation Map to be lock for the duration of the scan, which > would rule out all inserts against the table. > > -- > 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: Erland Sommarskog on 13 Dec 2009 17:45
Jay (spam(a)nospam.org) writes: > The statements: > > "but the above is certainly true for SQL Server" > > and > > "I have not thought much about why IAM scans are not used in READ > COMMITTED" > > are inconsistent. Either the QO will chose the index, or it won't. Depends on what you mean with "using an index". In a clustered index scan, SQL Server uses the linked list to follow the links. Most people think of that as a table scan. In an IAM scan, it scans the index allocation map for the table, and reads the pages in that order. -- 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 |