If BPASession table is defragmented, then literally an hour later it is fragmented back by 40% or more.
The reason for this behavior is that the table has a clustered index on the sessionid field, which has a unique identifier type
When a new row is added, a unique GUID is generated for it, since it is not sequential, then the insertion occurs not at the “end”, but at the “center” of the table with a very expensive splitting operation.
According to MS SQL algorithms, page splitting occurs as follows:
A new page (8Kb) is allocated and a half of the primary is copied to it, then a new line is inserted on the primary page. On average, adding a new line to BPASession results in copying / moving 15 “old” lines. Which leads to strong fragmentation.
There are three ways of how to resolve this:
- Generate a new GUID, not by the NEWID () function, but by the NEWSEQUENTIALID () function, which creates sequential GUIDs.
- move the cluster index from the BPASession.sessionid column to the BPASession.sessionnumber column of type INT IDENTITY (1,1)
- move the cluster primary key from the BPASession.sessionid column to the BPASession.sessionnumber column
... View more