Fast index fragmentation in SQL Server

Question

I have a query that currently runs on two physical servers, in just about 7 seconds. The query is relatively complex because it joins several tables and is formed by the Entity Framework. Currently, I am moving the database to a virtual hosted environment and everything looks fine except for this one query. When querying a virtual instance of SQL Server, the query starts after 7 seconds, but after an hour or two it suddenly takes about 8 minutes.

Looking at the execution plan, while in a slow state, I found an unexpected scan of the full table. If I rebuild the index on this table, it will instantly return to 7 seconds. However, within an hour or so, it will switch to 8 minutes.

There are very few changes in the table in question, and often I was able to determine the zero change between how it works well and works slowly. After the index is restored, fragmentation drops to about 0.02%, but after an hour or two it jumps between 50% -60%.

• Page Completeness - 52.95%
• Total fragmentation - 54.19%
• Average row size - 338
• Depth - 3
• Forwarded entries - 0
• Ghost Records - 0
• Index Type - CLUSTERED INDEX
• Sheet Level Lines - 134900
• Maximum row size - 604
• The minimum row size is 239
• Pages - 10736
• Section ID - 1
• String Versions - 0

I don’t know for sure whether fragmentation is the cause of the problem, but I will completely lose why it can be fragmented so quickly. Can someone explain?

Answer 1

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

• FILL FACTOR can also affect fragmentation.

• STATISTICS in this table may not be updated

• You may be the victim of a parameterization problem (the 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 that both queries are identical? If not, what is the difference?

We could help you more if you put a print screen with fragmentation information from sys.dm_db_index_physical_stats when the request works well, respectively bad.

Edit later: With the default fill factor, when page splitting occurs, half of the lines are saved on the start page and the other half will be moved to a new page.

You have very few changes, but surely the number of pages is doubled, so I suspect that a “small update” was made on all (or almost all) rows in the table due to internal fragmentation of 53%.

Other actions to be performed:

• 1 / It will be useful to publish the structure of your table to view.
• 2 / Do you have a column with the CHAR data type?
• 3 / Average number of lines per page (fast and slow)
• 4 / Check all stored procedures / queries that relate to this table
• 5 / Add a trigger and a log (in another table?) The actions performed in your table (you can have UPDATE TableX SET colA = colA WHERE 1=1 )

Keeps us posted.

Answer 2

As far as I know, index fragmentation can also lead to poor performance. This may be due to the DML in this table.

You see that the page coverage is about 52%. you have 10736 the number of pages with a clustered index, imagine when a query is selected with a full index scan, it will move through the pages until it receives a correspondence record. It will cost you performance.

Fragmentation can occur, and it is normal to maintain tables for index recovery. But I think fragmentation reaches over 30% in one hour. I am not sure how this happens, since you did not provide detailed information.

It is possible that you can do:

• Check what indexes you have in this table.
• Which query to touch the table

Waiting for feedback from you.