Index Fragmentation

[ousoonerjoe]

Using SQL 2008 R2.

A couple of months ago, we turned on CDC on some select tables. The following week, we had to turn off the Index Rebuild process of the weekly maintenance job due to the fact that CDC blocks the dropping of the Primary Key and caused the maintenance job to fail (which also happened to include the Full Backup process). Skip ahead to today and now we are getting reports of major slow downs. After checking the index fragmentation rates, some were showing 100% fragmentation. I found the following script to rebuild the indexes using DBCC DBREINDEX to get around the CDC issue:

DECLARE @TableName varchar(255);

DECLARE TableCursor CURSOR FOR
    SELECT table_name FROM information_schema.tables
    WHERE table_type = 'base table'
        AND Table_Schema = 'dbo'
    ORDER BY Table_Name;

OPEN TableCursor;

FETCH NEXT FROM TableCursor INTO @TableName
    WHILE @@FETCH_STATUS = 0
        BEGIN
            DBCC DBREINDEX(@TableName,' ',90);
            PRINT @TableName + ' - Completed.';
            FETCH NEXT FROM TableCursor INTO @TableName;
        END

CLOSE TableCursor;

DEALLOCATE TableCursor;

Once that finished, I ran the following against a couple of tables to see how well it defragged the indexes. I noticed that there were 3 levels to each index. Level 1 was defragged, but 2 and 3 were not. What are the levels and does one need to be concerned with them regarding defraging?

SELECT i.name, ips.*
FROM sys.dm_db_index_physical_stats(DB_ID(N'DbName'), OBJECT_ID(N'TableName'), NULL, NULL, 'DETAILED') ips
    --INNER JOIN sys.objects o ON ips.object_id = o.object_id
    INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id;

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[ousoonerjoe]

I finally found something that talks about the Index Levels:

How b-tree database indexes work and how to tell if they are efficient (100' level)

From what I gather, only the top most level (in this case 0) is effected during rebuild. It is also the only level that physically exists on the drive. The others are logical indexes that aid in locating the specific value within the index. Based on that, rebuilding of indexes and viewing the fragmentation of each level is overkill. Only the fragmentation of Index Level 0 is critical.

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[gmmastros]

Only the fragmentation of Index Level 0 is critical.

I might agree that fragmentation of index level 0 is most important, but I'm not sure if I agree that fragmentation of other levels is unimportant.

http://www.sqlserverfaq.net/2010/06...-only-factor-which-decides-the-rebuild-index/

From what I gather, only the top most level (in this case 0) is effected during rebuild.

I don't think this is correct. According to this:

http://technet.microsoft.com/en-us/library/ms189858.aspx

[tt]Reorganizing an index defragments the leaf level of clustered and nonclustered indexes[/tt]

Then later....

[tt]Rebuilding an index drops the index and creates a new one.[/tt]

This implies that rebuilding an index would fix fragmentation of the other levels.

Can you do me a favor? Can you run the following query and post the results?

SELECT fill_factor,COUNT(*)
 FROM sys.indexes
 GROUP BY fill_factor

That query is blazingly fast, so you don't need to worry about running it on a production server.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Using SQL 2008 R2.

Are you sure? I ask because according to microsoft, this problem should be fixed in R2.

http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=474589

http://support.microsoft.com/kb/977076/

The fix for this issue was first released in Cumulative Update 6 for SQL Server 2008 Service Pack 1.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

R2 and 2008 are different. The patch is pending the review process.

(It is not fixed in SQL Server 2008 R2 Nov CTP!)

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[gmmastros]

I know they are different. I guess I didn't read it carefully enough. I mistakenly thought that anything fixed for 2008 would also be fixed in the newer version (2008 R2), which appears to be a faulty assumption on my part.

Still... I'm curious to know what your fill factors are (based on the query I presented earlier). You see, you can use a lower fill factor which will initially require more space, but allows for some page activity before splitting. The net effect of a lower fill factor is that you will not get fragmented indexes as quickly as you would with a fill factor = 100.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

Fill Factor FillCnt
0 472
90 409
---------------------------------------

We were able to obtain the patch from MS though it is not a fully approved patch and they give the "use at your own risk" disclaimer for it. We had to email them directly to get it. I am going to install it this weekend (after all the full backups are made). Hopefully, it will solve me CDC and Index Rebuild issues. If it does, then the indexes will be rebuilt every week and fragmentation issues will no longer creep up on us like this again.

Unfortunately, MS is viewing 2008 and 2008 R2 as completely separate projects. I saw some MS articles where the patch was reportedly integrated into R2 before release, but then later it wasn't. The latest KB article I read did in fact address the issue and was to be in the first Service Pack for R2. When you click on the individual link to the KB issue related to R2, it takes you back to the 2008 CDC issue. (gotta love the recursive KB pages). Needless to say, we were able to get a hold them directly and they sent us a link to pull it down.

I'll update this topic with the results on Monday. I've seen a lot of other users out there looking for the patch and if it works.

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[gmmastros]

That's good news about the patch.

Other things you can do to help....

If any of those indexes with fill factor = 0 are getting fragmented, then alter the index so it has a fill factor = 90. If any of the indexes with fill factor = 90 are getting heavily fragmented, then change the fill factor to 80. I wouldn't recommend going below 80 except for extreme circumstances.

Next... in addition to weekly index rebuilds, you should consider doing nightly reorganizes (when your usage is at it's lowest). From my reading, a reorganize is an online operation (doesn't block other queries), but it does take processing and will likely slow other things down.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

Yeah, I read that about Reorganize. Both can be done as an OnLine process, but both still have over head that can affect production's throughput. However, I was in a bad place this morning, and ran the script shown in the original posting. It took only 10 minutes to complete against the 200 tables while processing in an On-Line state. Got an immediate response of improvement as soon as it completed.

I think I will add the Reorganize to the Differential backup process that occurs every other night. But all that will be pending the results of this patch.

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[gmmastros]

Rebuild can only be done online if you have the enterprise version of SQL Server. Reorganize is always an online process.

Also... reorganize does not drop the index so you shouldn't have a problem running it with or without the patch.

You may also want to do a little research on updating statistics frequently throughout the day.

Honesty time: My DB is tiny by today's standards, weighing in at 300 megs, so I don't usually have these types of problems. But, I do read a lot of blogs and participate in several forums. It's my understanding that updating statistics during the day is common, and then reorganize or rebuild during the night.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

I'm not all that familiar with the Statistics side of things. Our database is still fairly small at 2.5 gigs, but growing with each project enhancement. However, with that said, we still have several "heavy traffic" tables with a lot of Inserts and Updates happening.

UPDATE: The files MS sent us were passworded and were looking for multiple "disks" on execution. We've written back to them and new files are expected within the next day or two. I'll give more updates as they happen.

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[ousoonerjoe]

UPDATE: The patches did the trick. I was able to rebuild indexes on a table with the CDC enabled. I also re-enabled the process in the Maintenance Plan and did an execute. All is well. Indexes are back to normal tolerances.

We have encountered no issues with installing the emergency patch so far.

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------