Notes on DBS

GUIDs hurt performance of inserts. 
Relational databases use B+ tree structure to store data.
If data coming in is not ordered, then a lot of IO work is required to rearrange the leafs. 
GUIDs are random, so they may lead to heavy performance drawbacks, especially with large tables.

Database generated integers may hurt performance of inserts as well but for another reason. 
When a database generates a new auto-incremented value for a primary key, it takes a lock to avoid race conditions.
It causes a performance issue that shows up in scenarios with multithread or bulk inserts.

       IF EXISTS (SELECT * FROM dbo.Ids WHERE EntityId = @EntityId)
       BEGIN
              BEGIN TRAN    
              COMMIT TRAN
        END 
        
Heavy load systems leads to deadlock.
When a transaction selects, it obtains a shared lock. 
After that it tries to upgrade the lock to exclusive to update the record. 
If another transaction selects with the same entity Id after the first transaction ends selecting,
but before it starts the update, the deadlock will occur. 
Two transactions will acquire the shared lock, and both of them will also try to acquire an exclusive lock,
waiting for each other to release the shared lock.

https://enterprisecraftsmanship.com/2014/11/15/cqs-with-database-generated-ids/