SQL Server Storage Internals Part 1 - Basics(beyondrelational.com)

submitted by jacobsebastianjacobsebastian(3639) 9 months, 8 days ago

While learning query tuning topics, I got a chance to understand anatomy of data page and different types and levels of Index pages. This learning helped me to understand concepts better and to give better performance tuning solutions later. This is my...

2 comments |category: |Views: 21

tags: another

new Add a live kick counter to your blog >> liveImage

You can even customize the image by choosing your own colors, and then clicking the button below to update the preview and the html code:

  • "Kick It" text
  • "Kick It" background
  • kick count text
  • kick count background
  • border

Simply copy and paste this HTML into your blog post.


Users who kicked this story:
Comments:

posted by vijaystvijayst(1311) 9 months, 7 days ago 0

It is interesting to know the internals of SQL Server. I want to know how the transaction log works - for INSERT, UPDATE, DELETE statements.

I also like to know how transactions are coordinated. I am aware of two-phase commits. There is a PREPARE and COMMIT phase. But, I do not know what really happens in the PREPARE phase, and what happens in a COMMIT phase. If a COMMIT phase fails because of a hardware error, what really happens to the entire transaction?

Reply

posted by dpetersondpeterson(4397) 9 months, 6 days ago 0

If there is some sort of hardware failure during the commit phase, it's possible that the database could be left in an inconsistent state. However, depending on the recovery level setting of the database, it might be possible to reallocate the damaged pages (if it's not a total failure) and possibly replay the transaction.
I think the answer is "it depends" because it depends on what fails, when it fails, and what the recovery settings are on the database.

Reply

information Login or create an account to comment on this story