We are experiencing significant performance issues with Project Server 2019.
One of our primary suspects is an internal permission system Microsoft has implemented that defines the list of Projects a user can access.
During times of performance deeps we are seeing very slow performance of INSERT statements to a custom type created by Microsoft:
CREATE TYPE UniqueGuidArray AS TABLE (
[Item] [Uniqueidentifier] NOT NULL,
PRIMARY KEY CLUSTERED
(
[Item] ASC
)WITH (IGNORE_DUP_KEY = OFF )
)
GO
These INSERT statements can number up to several thousand statements and are performed one by one (by Microsoft). We've seen it taking over 1 min at some cases.
Since this is a Type created by Microsoft as a part of the product and is used in over 40 different procedures,
We cannot alter the code itself (in favor of other solutions like using a table) and are looking for any solution
That can improve performance of the type itself without altering the subsequent procedures.