Symptoms
You observe very slow processing/delay of notification email after upgrading K2 NLB cluster to 4.6.11, depending on amount of notifications this can be accompanied with extreme overall slowness of K2 environment (very slow worklist retrieval etc.).
Additional symptoms:
MSMQ public queue is constantly filled up and its processing very slow
You observe locks on K2 DB on Identity.Identity table
Diagnoses
This is known issue which may occur in 4.6.11 and which occurs due to combination of two factors: data retrieval from SQL XML column not being quick enough (we retrieving user email from XML column in Identity table) and large amount of emails being sent. This issue is fully addressed in 4.7 OOB where separate Email column added into identity table which solves this issue. For K2 4.6.11 there is a coldfix available upon request from K2 support.
Additional verification steps to confirm that your slow processing of notifications and overall slowness of K2 caused by this issue - using "sp_who3" SQL stored procedure (can be obtained at TechNet) it is possible to see that you have multiple prolonged locks on K2 DB caused by the following queries:
WITH XMLNAMESPACES('http://schemas.k2.com/identity/customProperties.xsd' AS n) SELECT [ID], [FQN], [Name], [Label], [Type], [Enabled], [Properties], [ExpireOn], [ExpireInterval], [Resolved], [ContainersExpireOn], [ContainersResolved], [MembersExpireOn], [MembersResolved], [Dynamic], [ProviderData] FROM [Identity].[Identity] AS [I] CROSS APPLY I.Properties.nodes('/n:properties/n:item') AS Props(Prop) WHERE ((Prop.value('@name','NVARCHAR(512)') = 'Email') AND (Prop.value('@value','NVARCHAR(512)') = 'k2@domain.com')) AND [Enabled] = 1 AND [Type] IN (1)
This is a SQL query that extracts email address from the user's properties XML column in the Identity table entry. Normally such query should take less than 0.5 seconds to resolve all users' email addresses and send them the notification email (for a group of 30 users that should receive this email for one event), but in certain scenarios this SQL query may take around 5 seconds to resolve one user's email address, resulting in a delay in sending emails as well as overall slowness of K2 platform (due to locks on database).
Resolution
Consider upgrading to 4.7 where this issue is adressed OOB or contact K2 support to obtain coldfix for K2 4.6.11
NOTE: When this patch is applied to 4.6.11 you have to drop column and function added by this patch before attempting upgrade to K2 4.7 as 4.7 installed does not expect these objects to be present in K2 DB.
