SQL Server 2005 Index Tearing or Index splitting

Question

SQL Server 2005 Index Tearing or Index splitting

Shazbot!

SSC Enthusiast

Points: 136
More actions
February 19, 2009 at 12:58 pm

#197308

I just started and a job and the CTO asked me this question. I never heard of it before but he told me it happens when a database gets to large and and a table has too many records the indexes start to break apart.
I searched online and from what I read this can happen with a bad database design or problems with the database.
Could some help me understand this concept and why this would happen?
TIA!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

JestersGrind SSCertifiable Points: 5812 More actions · Answer 1

I've never heard of it referred to as index tearing or index splitting, but I assume he's talking about torn pages and page splitting which are two entirely different things, neither of which involves the index being broken apart.

A torn page or torn I/O is when a partial write takes place, leaving the data in an invalid state.

A page split occurs when you try to insert a record into a data page and there's no room for it. SQL will move some of the data to a new page, effectively splitting the page. This can have performance consequences, but can minimized by using a fill factor. A fill factor leaves space in the pages for new data.

Greg

Paul Randal One Orange Chip Points: 29544 More actions · Answer 2

Sounds like he means page splits. Point him at this TechNet Magazine article I wrote which explains some more - http://technet.microsoft.com/en-us/magazine/cc671165.aspx

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Shazbot! SSC Enthusiast Points: 136 More actions · Answer 3

Thank you for time and posts. I will look into the article to get a better understanding of what the terminology is.

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 4

Paul Randal (2/19/2009)
Sounds like he means page splits. Point him at this TechNet Magazine article I wrote which explains some more - http://technet.microsoft.com/en-us/magazine/cc671165.aspx

Hi Paul,

That is a great article, I was pointed to it by a Microsoft Consultant we had in shop a little while. I always struggle trying to explain to people what it means for index to be fragmented as such. Your diagrams made my life easier ;-). Thanks.

Mohit.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

Paul Randal One Orange Chip Points: 29544 More actions · Answer 5

You're welcome. Check out the other ones too - see the TechNet Magazine category on my blog.

Cheers