Rapidly fragmenting index in SQL Server

Question

I have a query which currently runs on two physical servers just fine in around 7 seconds. The query is relatively complex as it joins several tables and is formed by Entity Framework. I'm currently migrating the database to a virtually hosted environment and everything seems to be ok with the exception of this one query. When querying against the virtually hosted SQL Server instance the query initially runs in 7 seconds but after an hour or two will suddenly take around 8 minutes.

Looking at the execution plan whilst in the slow state I identified an unexpected full table scan. If I rebuild the index on that table it instantly reverts to taking 7 seconds. However, within an hour or so it will switch to taking 8 minutes.

The table in question has very little change and often I have been able to determine zero change between it running well and running slowly. After rebuilding the index, fragmentation drops to around 0.02% but within an hour or two it jumps to between 50%-60%.

• Page fullness – 52.95%
• Total fragmentation – 54.19%
• Average row size – 338
• Depth – 3
• Forwarded records – 0
• Ghost records – 0
• Index type – CLUSTERED INDEX
• Leaf-level rows – 134900
• Maximum row size – 604
• Minimum row size – 239
• Pages – 10736
• Partition ID – 1
• Version ghost rows – 0

I don’t know for certain whether the fragmentation is causing the issue but I am at a complete loss as to why it may be fragmenting so quickly. Can anyone explain?

Answer 1

• You can see the differences of many fragmentation techniques and see what is the most appropriate for your case.

• FILL FACTOR could also impact the fragmentation.

• STATISTICS on that table might not be updated

• You might be a victim by parameter sniffing problem ( a query plan is cached but not the most optimal). Can you check if the same query plan is used for both cases?

• Are you sure the both queries are identical? If not, what is the difference?

We might help you more if you put the a print-screen with fragmentation info from sys.dm_db_index_physical_stats when the query works well, respectively bad.

Later edit: With the default fill factor, when a page split occurs half of the rows are kept in the initial page and the other half will be moved to a new page.

You have very little change but for sure the number of pages doubles, so I suspect a "little update" that is made on all (or almost all) rows in the table due to that 53% internal fragmentation.

Other actions to be performed:

• 1/ It will be useful to post your table structure, to have a look at.
• 2/ Do you have any column with CHAR data type?
• 3/ List average number of rows in the page (fast vs slow)
• 4/ Check all stored procedures/queries that touch that table
• 5/ Add a trigger and log (in another table?) the actions performed on your table (you might have UPDATE TableX SET colA = colA WHERE 1=1)

Keeps us posted.