Tag Archives: SQL

How SQL Index Fragmentation will kill Sitecore’s Performance

Thought I wrote a blog post about this years ago, but apparently I didn’t.


Poor index maintenance is a major cause of decreased SQL Server performance, which in turn will impact your Sitecore’s performance. The Sitecore databases contains tables with numerous entries, that get updated frequently, therefore high index fragmentation will occur.

Detecting SQL Server index fragmentation

The following script displays the average fragmentation, and as a help generates the SQL query to fix it.

ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
'ALTER INDEX ' + QUOTENAME(ind.name) + ' ON ' +QUOTENAME(object_name(ind.object_id)) +
CASE WHEN indexstats.avg_fragmentation_in_percent>30 THEN ' REBUILD '
WHEN indexstats.avg_fragmentation_in_percent>=5 THEN 'REORGANIZE'
ELSE NULL END as [SQLQuery] -- if <5 not required, so no query needed
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
--indexstats.avg_fragmentation_in_percent , e.g. >10, you can specify any number in percent
ind.Name is not null
ORDER BY indexstats.avg_fragmentation_in_percent DESC

Below you can see the typical result of running the script above. I was shocked as the majority of indexes on my local SQL server where over 99%.


The script above generates the SQL statements needed to defragment the affected indexes, so you can automate the defragmentation process, using SQL Server Maintenance Plans.

Anyway I hope this helps keeping you sitecore solution running at its best, Alan

Sitecore Commerce 8 powered by Microsoft Dynamics (SCpbMD) – Cannot change the publishing status of the channel

Well I have been having a lot of fun setting up Sitecore Commerce 8 powered by Microsoft Dynamics; there are a lot of moving parts to get get the catalog data from Ax, all the way into Sitecore. Not to get political but in fact 90% of the issue have come from Microsoft Ax not being setup correctly.

Routing Service

The Routing Service is responsible for transferring the catalog data from the Channel Database into Sitecore Commerce Server which acts as an edge server for Sitecore. In addition the routing service needs to access the Microsoft Ax Real Time Service (RTS) to change the publishing status.

But why does Sitecore need to update the publishing status anyway? I would assume that was the responsibility Microsoft Ax?

The Sitecore Routing Service is considered part of AX channel publish step. The Routing Service needs to ensure all publishes from HQ to channel database is finished. For a more detailed answer see this article from Microsoft, where the “Routing Service” is equivalent to the SharePoint in the diagram.

I found the following articles by Hao Liu, very useful in tracking down errors and issues with the catalog synchronization.

Communication with Ax is a black box

When communicating with Microsoft Ax it is completely transparent. You call the Microsoft run time DLL’s which connect to the channel database; which resolves how to communicate with the RTS, get the catalog data, create shopping baskets, talk to HQ’s Ax to create orders etc.
Which is great until it does not work as determining where it has gone wrong is a nightmare.
I did not have access to the Microsoft Ax setup and we got  the following exception.

Exception while calling invoke method UpdateChannelPublishingStatus: An unsecured or incorrectly secured fault was received from the other party. See the inner FaultException for the fault code and detail.

Any of the following reasons can cause the a fore mentioned error

  1. You have to use SSL to communicate with the RTS web services
  2. The RTS web service is not running at all.
  3. The RTS web service is not running SSL (which it must)
  4. The RTS web service “host name” does not match the name of the SSL certificate.
  5. The host name running the RTS Web service does not match the host defined in Microsoft Ax.
  6. The credentials running the RTS web service are not correct, and do not have access to the HQ Ax.

Cannot change the publishing status of the channel (record Id: 5637146084) to ‘Failed’.

Then we got the following exception and we (Sitecore Support, The Microsoft Ax Partner & myself) spent a lot of time trying to identify what was causing the following exception:

Cannot change the publishing status of the channel (record Id: 5637146084) to ‘Failed’.
at Microsoft.Dynamics.Commerce.Runtime.TransactionService.TransactionServiceClient.GetResponseFromMethod(String methodName, Object[] parameterList, Boolean useExtensionMethod) at Microsoft.Dynamics.Commerce.Runtime.TransactionService.TransactionServiceClient.UpdateChannelPublishingStatus(Int64 channelId, OnlineChannelPublishStatusType publishingStatus, String publishingStatusMessage)


I am not sure if it was sheer desperation, divine intervention, or more likely luck!!!

But I checked the SQL server which hosted the channel database, and it had no disk left.

For some reason (I did not install the async client and or the channel database) the recovery mode was set to “Full” and it had therefore create 1 TB of log file 😦

I set the recovery mode to “Simple”, ran shrink on the database and the size dropped from 1TB to almost nothing, and the exception was gone 🙂