![]() ![]() This means every time we need to scan the index, it’ll take 10% longer (1,100 pages instead of 1,000). Instead of our phone book having 1,000 pages that are 100% full, we might have 1100 pages that are only 90% full. How Fragmentation Hurts SQL Server Performanceīad internal fragmentation (having lots of free space on the pages) means the index is bigger than it needs to be. Deletes cause problems by leaving empty space behind (internal fragmentation). ![]() When Pat Down marries Phil McCann, and we update her last name in the phone book, we leave empty space in the D section and cram a new record into the M’s. Updating people in our phone book causes problems too. ![]() Problem #2 – External Fragmentation: The phone book pages are out of order. Problem #1 – Internal Fragmentation: We’ve got a newly added page with hardly any stuff on it. We’ll have to tack more blank pages onto the end. If there’s not enough free space, SQL Server has to do some rearranging – but it can’t exactly shove a brand new page in the middle of the phone book. When SQL Server rebuilds indexes, it uses the fill factor to decide how much free space to leave on each page. Ideally, each page has some empty space, and we govern that with the fill factor. Let’s take a step back for a second and pretend that your database is a phone book organized by last name, first name.Īs people move into your city, we have to add them to the phone book. What is Index Fragmentation and How It Happens If your database was a phone book, it would look like this one.Īnd I bet you’re wasting your time, missing the real point. I bet you’re worried about your index fragmentation. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |