AI OnAI Off
After Upgrade - Spike in SQL Wait Time on mdpsp_sys_OpenSymmetricKey
mdpsp_sys_OpenSymmetricKey has not been changed for a very long time - I suggest you to try on another server (probably with the vanilla Quicksilver site) to see if the same thing happens.
Regards,
/Q
Nov 22, 2016 8:52
I can see from the schema that the SP hasn't changed but what about Epi's code that utilizes it? What are some of the most common epi processes that would utilize code that would envoke a call to that SP? For reference, our site is still on the legacy promotion system and has not moved over to the new IOrderRepository interface yet. To be clear, I'm not rulling out our code in all of this, but I'm trying to figure out where to focus since access to this SP is done thru epi's code. This happened immediately after deploying the code and db changes for an upgrade from 9.19 to 9.24.1.
Nov 22, 2016 14:53
Are you using encryption anywhere in your metaclasses? I.e. is there any encrypted metafields in your system?
This is not related to the legacy promotion system, and/or the new IOrderRepository, but to the metadata system.
/Q
Nov 22, 2016 15:11
I do not believe we are using encrypted metafields. I ran the following query and received zero results:
SELECT * FROM dbo.MetaField WITH (NOLOCK) WHERE ISNULL(IsEncrypted,0) = 1
Let me know if there is anywhere else I should check. Thanks
Nov 22, 2016 16:05
You can try to disable mdpsp_sys_OpenSymmetricKey/mdpsp_sys_CloseSymmetricKey by make it like this
ALTER PROCEDURE [dbo].[mdpsp_sys_OpenSymmetricKey] AS
Basically, make them do nothing. If you upgraded from Commerce 8, they will try to open and close the symmetric key. I don't know if any changes between 9.19 and 9.24.1 can slow down it, so it can be just an environment (SQL Server update?) issue.
Nov 22, 2016 16:11
Are these encryption SPs used for the payment tables? I'm a little nervous to just alter the procedure like that unless it is only specifically utilized for the metafields in that table I mentioned earlier.
We upgraded from 7.5 to 9.19 back in August, but didn't see this issue after the upgrade. We did see the ecf_Inventory_QueryInventory issue and that it was reported as a bug. This was part of the reasoning for our recent upgrade to 9.24.1. I'm happy to say that the upgrade did fix that inventory query issue:
No changes or updates were made to the SQL server during this time other than the scripts that were part of the epi upgrade process that were extracted from the nuget updates.
Nov 22, 2016 16:42
Still digging into this, I'm seeing an increase usage of the SP CatalogContentProperty_Load, this could help explain the increase of the OpenSymmetricKey usage. Now to find out why CatalogContentProperty_Load went up in usage...
Nov 22, 2016 18:50
We recently upgraded from 9.19 to 9.24.1.
We've been monitoring SQL database activity because of the previous bug that was causing a spike in activity on ecf_Inventory_QueryInventory. The upgrade definitely cleaned up the activity on ecf_Inventory_QueryInventory, but unfortunately, we saw a significant spike in wait time on mdpsp_sys_OpenSymmetricKey start up. I was curious if a) anyone else that's monitoring their sql activity notices this, and b) was there anything in the upgrades between 9.19 and 9.24.1 that could attribute to this?
We didn't add any new encrypted meta fields to our application and I'm unsure of what else we could have done in our code to generate this spike.
Appreciate any insight others might have.