April 11, 2013 at 3:05 am
I'm troubleshooting a latch contention issue at the moment. One of the symptoms operations noted during the incident itself was that a SELECT TOP 10 * FROM MyTable WITH (NOLOCK) was taking up to 6 seconds (should take less than 1). Diagnosis so far reveals major contention for cluster pages for said table, with high levels of PAGELATCH_SH & _EX waits.
My question is, I have a vague memory that dirty reads (NOLOCK selects), as well as the Sch_S lock, still need to take a PAGELATCH_SH latch to read the target buffer page(s).
Can anyone point me to a chapter & verse reference confirming (or denying) that?
April 11, 2013 at 3:35 am
Nolock != nolatch
Nolock or the read uncommitted isolation level simply mean that a query does not take shared locks while reading. All other locks and latches are taken and held as normal. Latches protect the physical structure of a page, if something could read a page without latching it, there would be so much potential for severe errors.
As for nolock itself...
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2013 at 4:59 am
Thanks for the reply. I totally agree about the dangers of using NOLOCK in any application code. However as a tool for an admin doing a quick check to see if a table is still available in the middle of a crisis, it makes sense.
But BOL is fairly reticent about latches as these are considered mostly "internal", and neither Kalen nor Henderson on internals are giving me the confirmation that reads (including dirty reads) necessarily take a shared page latch (although it seems logical that they must do). I'm reading through the SQLCAT paper on "Diagnosing and Resolving Latch Contention on SQL Server" at the moment, which looks pretty detailed (and useful for my overall investigation) but if anybody knows of a msdn or other reference that confirms that all reads require page latches, please link. Ta.
April 11, 2013 at 5:04 am
I don't know a reference offhand, but I can tell you absolutely, any read of any page, regardless of the isolation level, will take a shared page latch for the duration of the read.
Latches are for physical protection, a read latch on a page prevents any other process from changing the physical structure on the page. Any read of any page takes a shared latch (PageLatch_SH), any modification to any page takes an exclusive page latch (PageLatch_EX)
Other latches are taken as pages move from memory to disk or back (PageIOLatch_*)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply