Previously we had issues with a bad performing SQL that is in top in Microsoft SQL Server Management Studio (Activity monitor). It was found out that this was used by a version sync that ensure that the content versions used by the Content Repository and the Catalog UI are up to date with the change. As we import all product data from other systems, we don't care about this, and in Commerce 8.9 this new feauture was added to disable this job (http://world.episerver.com/blogs/Magnus-Rahl/2015/3/optional-performance-tweaks-in-episerver-commerce-8-9/).
After upgrade to 8.9 and defining this in Web.config, we still see this SQL running very often causing a lot of trouble for the SQL environment:
Have we missed anything?
We're aware of that issue and are working to solve it. In the next release of Commerce, the version sync issue should be gone and the performance gain should be very significant.
Sounds good! Is there any bug or story connected to this, so we can track the proceedings? When is next release planned?
I don't think we have public stories - so you might not have access to that. I can't say for sure about the timeframe, but it should be no later than this fall.
Thank you very much for your fast responses. Is there anything we can do in the meantime? Applying index to that column for BigTable did not work after site restart. We enabled the "DisableVersionSync", but just to make sure, this is not completely removing the sync jobs?
Enabling DisableVersionSync will skip the version sync if you're using other APIs then Content API. For example, if you use ICatalogSystem to save CatalogEntryDto or when you import the catalog. When you use Content API, there still be some slowness due to DDS.
How did you import products from other system? Did you use Content API or using catalog import export? If you are using Content API then you might want to save the content with the extended flag ExtendedSaveAction.ClearVersions.
In the meantime, since you don't want catalog version at all, you can clean up the big table by executing these: DELETE FROM [tblBigTable] WHERE StoreName='EPiServer.Commerce.Catalog.Provider.CatalogContentDraft' and DELETE FROM tblBigTableReference WHERE PropertyName='ContentReferenceToSoftlinkMapping'. As always, please backup your database first.
I will answer instead of Rasmus. We are importing products using via ICatalogSystem (e.g. ICatalogSystem.SaveCatalogEntry(catalogEntryDto)). Is it safe for us to do clean up of big tables since we don't care about products/nodes versions because all changes comes from external system. It shouldn't affect page versions in CMS or anything else, right?
Any other ideas are appreciated too. Thank you.
Yes, it would be safe for having no catalog version at all.
EDIT: I want to note that tblBigtable is home of other data also, so be careful with the store you work with (CatalogContentDraftStore)
We have tried to add the corresponding index the SQL performance monitor suggest:
/*Missing Index Details from ExecutionPlan1.sqlplanThe Query Processor estimates that implementing the following index could improve the query cost by 94.7742%.*//*GOCREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]ON [dbo].[tblBigTable] ([Row])INCLUDE ([pkId],[String01])GO*/
This will cause this error. Can we solve that following problem somehow? After removing the index again the application starts without problems.