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 18:07 Erland, you're being obtuse. "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CE0F1AD44003Yazorman(a)127.0.0.1... > 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 >
From: Kalen Delaney on 13 Dec 2009 20:07 My understanding of why IAM scans won't be used for READ COMMITTED is specifically to avoid the inconsistencies that can occur with an IAM scan. -- HTH Kalen ---------------------------------------- Kalen Delaney SQL Server MVP www.SQLServerInternals.com "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: Jay on 13 Dec 2009 20:22 > My understanding of why IAM scans won't be used for READ COMMITTED is > specifically to avoid the inconsistencies that can occur with an IAM scan. > OK, then what am I missing. When in READ COMMITTED isolation, if SQL Server will scan the index if everything in the query is in the index, but won't scan the index to avoid inconsistencies... Do you see my confusion? Or is the IAM not the index? > -- > HTH > Kalen > ---------------------------------------- > Kalen Delaney > SQL Server MVP > www.SQLServerInternals.com > > "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: Kalen Delaney on 13 Dec 2009 23:47
Correct, the IAM is definitely not the index. It is a single page bitmap that keeps track of which extents belong to an object. Scanning using the IAM is also called allocation order scan. So there are two completely different choices for scanning... following the pointers from page to page in an index, and access each extent pointed to by bits in the IAM. More info here: http://blogs.msdn.com/sqlserverstorageengine/archive/2006/11/09/when-can-allocation-order-scans-be-used.aspx and here: http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/25/under-the-covers-iam-chains-and-allocation-units-in-sql-server-2005.aspx There's also lots more info in my book. -- HTH Kalen ---------------------------------------- Kalen Delaney SQL Server MVP www.SQLServerInternals.com "Jay" <spam(a)nospam.org> wrote in message news:eX2qZvFfKHA.3792(a)TK2MSFTNGP02.phx.gbl... >> My understanding of why IAM scans won't be used for READ COMMITTED is >> specifically to avoid the inconsistencies that can occur with an IAM >> scan. >> > OK, then what am I missing. When in READ COMMITTED isolation, if SQL > Server will scan the index if everything in the query is in the index, but > won't scan the index to avoid inconsistencies... > > Do you see my confusion? Or is the IAM not the index? > >> -- >> HTH >> Kalen >> ---------------------------------------- >> Kalen Delaney >> SQL Server MVP >> www.SQLServerInternals.com >> >> "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 >>> > > |