Professional SQL Server 2008 Internals and Troubleshooting

Author: Christian Bolton, Justin Langford, Brent Ozar, James Rowland-Jones, Jonathan Kehayias, Steven Wort, Visit Amazon's Steven Wort Pagesearch resultsLearn about Author CentralSteven Wort
4.8
This Month Stack Overflow 2

Comments

by anonymous   2019-07-21

RE:

How this is handled internally by query analyzer.

This is a highly abbreviated summary of a section from the book "Professional SQL Server 2008 Internals and Troubleshooting"

  1. The SQL Server Network Interface (SNI) on the client establishes a connection to a TDS endpoint and sends the SELECT statement as a TDS message.
  2. The SNI on the server unpacks the message and passes the SQL Command to the Command Parser
  3. The command parser checks if a suitable plan exists in the cache. If not it creates a query tree and passes it to the optimizer.
  4. The optimizer generates a query plan. (If required)
  5. The Query Executor processes the query.
  6. The results are sent back to the client using the TDS protocol.

It looks as though this page has considerably more details about some of these steps.

by anonymous   2019-07-21

Set your transaction isolation level to REPEATABLE READ for the duration of your transaction. This is also preferable, in my opinion, to using locking HINTS because of the increased clarity of your code implementation.

As you are reading only a single row, you do not need to worry about range inserts to your set, a caveat of REPEATABLE READ.

I suggest reading the "Locking and Latches" chapter in the book Professional SQL Server 2008 Internals and Troubleshooting. It contains excellent explanations, including code examples, of the various Isolation Levels available in SQL Server as well as describing the mechanics of the each of the data anomaly scenarios, such as Phantoms etc.

DISCLAIMER: I'm not on the payroll for this title but I do have two copies, one hard and another on kindle edition, that's how good it is that I bought it twice!