We’ve created a cms scheduled job that processes a queue of operations on meta classes and meta fields. In this job we Create and Delete meta classes and meta fields. We also relate meta fields to meta classes. During the job execution we have an issue where the sql server cpu is getting slammed due to thousands of calls to the following stored procedure per second: mdpsp_sys_LoadMetaFieldListByMetaClassId. The stored proc alone is pretty simple and fast, but what is making this SP execute so often?
We're using the MetaClass and MetaField classes in the Mediachase.MetaDataPlus.Configurator namespace.
Through some debugging we've narrowed it to the following method in the MetaClass class:
private void AddFields(IEnumerable<MetaField> metaFields, int weight)
In this method the following line is responsible for the call to the stored proc mentioned above, because the MetaFields property calls LoadMetaFields().
Here is our code that is called maybe 300 times during the life of the job
public string CreateMetaFieldRelation(MetaClass metaClass, MetaField metaField)
if (metaClass == null)
return "ERROR: Meta class does not exist";
Would it be possible to post the code of your scheduled job here ?
Even better, try to use some profiler (like dotTrace) to look into the execution of your job, that would reveal a lot of thing you might have overlooked
I'd also recommend Stackify Prefix, it's free and will show you all the SQL executions being made and errors even if they aren't logged on local development.
A word of warning: last time I check, they still haven't fixed it https://vimvq1987.com/a-curious-case-of-memory-dump-diagnostic-how-stackify-can-cause-troubles-to-your-site/
Our scheduled job code is calling the following 3 scenarios using the methods below from EPi. It's rather simple what we are doing from our end with very few new classes/fields involved. Yet when tracing via SQL Profiler, the procedure mdpsp_sys_LoadMetaFieldListByMetaClassId is being called 30,000+ times. Any idea?
Create MetaClass -- only 3 new classes created
MetaClass.Create(MetaDataContext.Instance, metaClassNamespace, name, friendlyName, tableName, parentMetaClass, MetaClassType.User, description);
Create MetaField -- only 2 new fields created
MetaField.Create(MetaDataContext.Instance, metaFieldNamespace, name, friendlyName, description, dataType, 255, true, false, false, false);
Create Relation -- associating 40 fields per new class
Care to share your profiling results (probably with code/pdb) to me? Preferably directly via mail,
I've got a hunch that this might have something to do with event replication...
We ran a test in our QA environment which is load balanced between two servers: QA1 and QA2
The job to create the class and add field relations was exectued on QA1 and in SQL profiler we saw QA1 call mdpsp_sys_LoadMetaFieldListByMetaClassId once. On QA2 that same SP was called 11781 times in the same timespan.
We have two applications, our CMS site and our Commerce site. Both are setup for event replication on the same soap udp address/port.
Can you think of anything with event replication that might be causing this activity that we're seeing? Should we be running the same endpoints between our CMS and Commerce sites? Or do you think this event replication thing is unrelated to the issue we're seeing?
Shouldn't that be clear on dotTrace profile result? So you don't even have to guess
Just to follow up, we sent the trace file to you via email and we saw your response. To add to this, we pulled one server out of EPi's event replication and ran the job without any issues. The stored procedure, mdpsp_sys_LoadMetaFieldListByMetaClassId, was called one time each while profiling (just as it should). So this really points to the event replication as being the issue. Let us know what you find!
I decided to file a bug and "fix" it - the fix will be in the later version of Commerce
Thanks Quan! What was the issue and what is the fix? Can you give us a ticket number so we can keep an eye out for this update?
Your "hunch" is correct, and yes this has something to do with event. When looking into it I realize we can reduce the cost of reacting to events, so we did. The fix is under review now.
The bug is COM-8757 but it will be some time before it's made public.