Index Remained Fragmented EVEN AFTER Index Rebuild Job

I had an interesting thing to find out this week. An Organization had an issue: Their queries are running extremely slow. They did some research on this. They found that even simple queries which uses index seek too are extremely slow. They copied the data into another database and created the indexes and executed again. They were quite fast. There were no other processes running when this test was carried out.

Through another friend of mine they got my contacts and they called me. I got the execution plans from both databases and they were identical. Then with the help from them, I ran a query using sys.dm_db_index_physical_stats against this index and found that it is fragmented. They rebuilt the index and the query started working well again.

Using the same DMV against other tables, I found out that the indexes are fragmented. They sent me the figures they received using dm_db_index_physical_stats Fragmented means some are 80% - 95% fragmented. To be honest with you, I even saw a few indexes showing 100% fragmentation which I have never seen before. I asked them to rebuild the indexes. Later I explained them how these house keeping jobs could be automated.

After a couple of weeks, they called me again. The indexes are fragmented again. I asked them whether they have added the index maintenance job. They answered back saying they have an index maintenance task as part of the database maintenance job and even made it as the first task of the job, [which runs on Sunday nights] but that has not resolved the issue.


First of all I asked them whether there is any other huge process like ETL job running afterwards. They politly said, that the index maintenance task is just part of the database maintenance task and they don’t do any other tasks in the nights. They do other things, like backing up database and executing few DBCC commands as part of the database maintenance jobs

I was clueless. I just tried another blind shot. I asked another question to run the physical stats on Monday morning to see the results. When they came back with the results, it showed like the rebuild index didn’t make any improvement.

I lost my patience. Is this step enabled? I even logged into their system (with the permission from their management) and checked the job. Yes it is. Then what is going wrong?
While checking I just checked the other jobs too. There I found the issue. The last step of the job was Shrink database. I remembered this basically a major re-organizing event. I moves the data/index pages drastically.

Is this the cause for the issue?

My tests on a different server showed me that shrinking the database could cause fragmentation of indexes. Additionally, I was able to get some blogs too on this subject. The best blog I got was this:

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/13/629059.aspx

One paragraph says this:

“Shrinking a database file moves pages from the end of the allocated range of a database file to somewhere at the front of the file, below the shrink threshold. It pays no attention to any index ordering or potential fragmentation. Hence, shrink operations on data files are excellent ways to introduce significant logical fragmentation.”

When you read this paragraph, if you think this clearly says the issue, you should read the next line:

“I've seen people who's maintenance plans are a nightly rebuild of all indexes, followed by a database shrink - total waste of time!”

I disabled the shrink database task. Even though it took some time to understand the issue, it is a good learning for me.