SQL Server Storage Internals Part 1

home
database
view story

5 Kicks

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

submitted by jacobsebastian(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: Database |Views: 21
Tweet

tags: Database another

Everyones tags:

Your Tags:

Please login or signup.

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:

More who kicked this story...

Comments:

posted by vijayst(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?

posted by dpeterson(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.

Most Active Users

What's this?