BPASession table optimization

[AleksandrSalnik]

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

[MelanieGiuliani]

Hi Aleksandr, 

Thanks so much for submitting your idea! We are moving it into the Under Consideration status while we route the idea through our internal review process. 

We will update you as your idea moves along the lifecycle.

Thank you!
Melanie