Saturday, December 24, 2016

Sitecore Deadlock Analysis for Item Bucketing

Here I am sharing my detailed analysis of Sitecore Dead-lock which was occurring on our production site because of using a  Fast query for Item bucketing search.

Recently we faced the site down issue; sometimes our site went down for some time like 2-3 minutes in some intervals and then automatically up, and we also noticed that traffic was huge on the site(concurrent request was around 1000) during the downtime of the site.

After Detail analysis of the logs we found some deadlock victims as below:

Exception: System.Data.SqlClient.SqlException
Message: Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Source: .Net SqlClient Data Provider
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Sitecore.Data.DataProviders.Sql.DataProviderCommand.ExecuteNonQuery()

Then we switched our focus towards the database and tried to figure out the exact query that Cause the deadlock, and we got the below SQL query that was victim of the deadlock

(@value1 uniqueidentifier,@value2 nvarchar(38))SELECT DISTINCT [i].[ID] [ID], [i].[ParentID] [ParentID] FROM
[Items] [i] INNER JOIN [Descendants] ON [i].[ID] = [Descendants].[Descendant] INNER JOIN (SELECT DISTINCT [i].[ID] [ID],
[i].[ParentID] [ParentID] FROM [Items] [i] INNER JOIN (SELECT DISTINCT [i].[ID] [ID], [i].[ParentID] [ParentID] FROM
[Items] [i] INNER JOIN (SELECT DISTINCT [i].[ID] [ID], [i].[ParentID] [ParentID] FROM [Items] [i] WHERE LOWER([i].[Name]) =
'sitecore' AND [i].[ParentID] = @value1) [a] ON [i].[ParentID] = [a].[ID] WHERE LOWER([i].[Name]) = 'content') [a] ON [i].[ParentID] =
[a].[ID] WHERE LOWER([i].[Name]) = 'home') [a] ON [Descendants].[Ancestor] = [a].[ID] WHERE ([i].[TemplateID] = @value2)

Now at that particular time we figured out the problem of site-down, but we had not even the solution for this problem because we don’t know the root cause I means the piece of code which is creating the problem, so finally we more digged in to the code and figured out the exact problem,

"Actually we were using the fast query for getting the bucketing item" as below:

string fastQuery = string.Format("fast:/sitecore/content/Home/ /#Events#//*[@@templateid='{0}' and @Event From != '' and (@Event To >= '{1}') and @ POIs= \"%{2}%\"]", SitecoreContext.NewEventContentItemTemplateID.ToString(), DateTime.Now.ToString("yyyy-MM-dd"), this.ContextItem.ID.ToString());
var eventsItem = Sitecore.Context.Database.SelectItems(fastQuery).OrderBy(x => x["Event From"]).ToList();

We were searching events from around 5000 bucketing items, and due to high concurrent load(1000 request) it occurred the dead lock situation, then we changed the query(Index Content Search) to get the item bucketing items as per the Sitecore recommendation as we already knew but didn’t implement :)

Now we are getting the bucket items from Sitecore context search query from Index as below

  var bucket = Sitecore.Context.Database.GetItem(Constants.ItemID.EventsBucketID);

                using (var ctx = ContentSearchManager.GetIndex("IndexName".ToScSetting()).CreateSearchContext())
                    var today = DateTime.Today;
                    var pred = BaseEventPredicate(bucket, true)
                    var evts = ctx.GetQueryable<SearchEvent>(new CultureExecutionContext(Sitecore.Context.Language.CultureInfo))
                        .OrderBy(i => i.DateWeight)
                        .ThenBy(i => i.EventFrom)
                        .Select(i => i.GetItem())
                    return evts;

Now there are no error logs related With Dead-Lock, no site down

Now I was curious to reproduce the same error on the dev box, as this issue only occurred in production, so first thing I had to give some load on the dev box to reproduce the same error:

So I used visual studio load test and reproduce the same error on my local machine as below:

On the same time I watched the SQL profiler for the same load and found out the same dead lock issue as below:

So the conclusion of this post is, "never use Sitecore Fast query for Item Bucketing search".

Always use Sitecore Context Search Query using Index.

I hope this article will help you.

Happy Sitecoring:)

Friday, December 16, 2016

Check Sitecore Items having more than 100 child Item through Sitecore Database Query

As Sitecore recommendation, the number of items under any given node, 100 or less is considered the best for performance and usability. If items are increasing periodically then we should plan accordingly. 

For example, a Blog folder where Blog item increasing on a daily basis, we should create day/month/year format folder structure or use an item bucketing to store the large number of items.

But in real life scenario, sometimes we don't follow the Sitecore best practice and dump large number of items under one single node.

I involved one of the performance tuning task in my project and want to generate the report to get all the Sitecore items that having more than 100 child items, The Quick solution came in my mind is a direct database query in the Sitecore database, however there are also other good option to generate this report like Sitecore API, Powershell script, Content search APIs Etc. 

But I didn’t dig into these approaches, as I need to provide this report quickly, and I appreciate if somebody provides the other way to get this report. 

Below is the direct SQL query to get all the Sitecore items that have more than 100 immediate child items,

Select Master or web database,

And below is the result.

OMG, I shocked to see this magical figure, this is not the item bucketing folder, this is normal Sitecore item that has more than 40k Immediate Child Item,

You should also run this script in your Sitecore database and find some magical figure. :)

I ran the same query in the vanilla Sitecore 8.2 version and found only one entry that has more than 100 child item, that’s why we love #sitecore.

I hope this article will help you.

Happy sitecoring J

Saturday, December 10, 2016

Links Removed from master Database when we delete reference item from other database in Sitecore 7

If Your Project is running on sitecore7 update 3 or below version, then you should read this blog to avoid the Link tagging deletion problem from master database:

Have you ever faced this issue?, you switched to a different database than the master database, deleted an item, and selected "Remove Links" in the breaking links dialog box, the links would be removed from the master database instead of the selected database.

Yes, I recently faced the same issue in one of my project which is running on Sitecore 7.0 rev. 130918.

I deleted some items from web database with selected "Remove Links" in the breaking links dialog box, and the links removed from master database instead of web database.

But as per the expected behavior it should delete from web database instead of master database as I selected the current database as web.

So this is the problem in this version and I lost my production data(tagging) from master database due to this behaviour, Thanks  to my DBA who quickly restored the production master database.

Below are the steps to reproduce this issue:

Create 2 items in master database and linked item1 in item2 as below

Publish to Web database

Check the link database in the core database:

Delete the item1 from the web database:

Now check the link database:

It deleted the links from the master database instead of web database as we deleted the link from web database.

Now check the master database:

The tagging has been removed from master database from item2. we tagged item1 in item Link Field.

But I tried to replicate this issue in other Sitecore version like 7.5, 8.0,8.1 and its working fine.

Then I connected to the sitecore support team and they provided the hotfix for this issue.

Again thanks to the sitecore support team for the quick reply,

Below are the steps for the hotfix.
1. Copy the attached “Sitecore.Support.362884.dll” assembly to the “\bin” folder.

2. Replace the following line in the <uiDeleteItems> configuration section of the “web.config” file:
<processor mode="on" type="Sitecore.Shell.Framework.Pipelines.DeleteItems,Sitecore.Kernel" method="CheckLinks" /> 
With this line:
<processor mode="on" type="Sitecore.Support.Shell.Framework.Pipelines.DeleteItems,Sitecore.Support.362884" method="CheckLinks" />

3. Replace the following line in the “\Website\sitecore\shell\Applications\Links\BreakingLinks\BreakingLinks.xml” file:
<CodeBeside Type="Sitecore.Shell.Applications.Dialogs.BreakingLinks.BreakingLinksForm,Sitecore.Client"/>
With this line:
<CodeBeside Type="Sitecore.Support.Shell.Applications.Dialogs.BreakingLinks.BreakingLinksForm,Sitecore.Support.362884"/>

Download the Sitecore.Support.362884.dll

This issue has been fixed in Sitecore CMS 7.0 rev. 140120 (Update-4).
For more information please use the reference number 362884 in the following release notes:

I Hope this article will help you.

Hapy sitecoring

Friday, December 2, 2016

Bad IL Range Exception after installing SXA in Sitecore 8.2?

Have you ever experienced the same exception after the SXA(sitecore experience accelerator) installation on Sitecore 8.2? What is the main root cause for this exception?

After installing the Sitecore SXA, Content editor showing the below error:

Server Error in '/' Application.
Bad IL format.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
Exception Details: System.BadImageFormatException: Bad IL format.

I also faced the same exception after installing the SXA in Sitecore 8.2, I followed the same step as per the sitecore guideline


Before installing SXA, ensure that you have the following:
  • Sitecore Experience Platform 8.2 initial release or Sitecore Experience Platform 8.1 Update-3
  • Sitecore PowerShell extensions 

            Sitecore XP 8.1 – full 4.1 or later 
            Sitecore XP 8.2 – full 4.1 or later 


To install SXA:

  1. Download the appropriate SXA 1.1 installation package from 
  2. On the Sitecore Launchpad, click Control Panel. 
  3. In the Control Panel, in the Administration section, click Install a package, to open the Installation Wizard. 
  4. Click Upload package and follow the steps to upload the SXA package to Sitecore. 
  5. Click Next and follow the steps to install the package. 
  6. Before you close the wizard, select Restart the Sitecore Client and Restart the Sitecore Server. 

But still faced the same exception, after researched a bit, I found 2 main root causes for this exception as below:

First, Always do "Merge" option to resolve all item conflicts and during the SXA installation, I have mistakenly checked the override option that causes this exception.

It’s also clearly mentioned in the SXA package as well

And the 2nd point is to install PowerShell Extensions Full 4.1 or later Release for Sitecore 8.2, As SPE 4.0 module is not supported on Sitecore 8.2.

I hope this article will help you.

Happy sitecoring :)

Configure Sitecore 9.1 Content Tagging feature with Open Calais

What is Tagging? Tagging refers to the metadata of the content which is assigned to a content by the content creator and the r...