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 :)

Monday, November 14, 2016

How Sitecore MVP Community Is Growing from Last 8 Years

The Sitecore MVP community is growing really very fast, and through this Graph you can easily identify the exact number of Sitecore MVP awarded in each year for example in year 2008 it was 13 MVPs in the community and in the year 2016 we have 221 MVPs in the community.

This graph clearly shows the incremental progress of the Sitecore MVP community from the Year 2008 to 2016.

2017 MVP nomination is going on, and getting more information regarding MVP Program is really good for us like who is the first Sitecore MVP? Who is the life- time Sitecore MVP? How many MVPs are there in 2008, 2009 etc?

The same question came to mind, and I thought to collect all the related information and post the consolidate blog for the same.

So I collected all the information from the sitecore site and different web sources/sites, links etc. and tried to figure out the exact numbers and list of the Sitecore MVPs.

Number of Sitecore MVPs Year Wise

Number of MVP
Guess ??

Life Time Sitecore MVP Award

John West is the Lifetime Sitecore MVP, and his only name is enough in the sitecore cumminity.

Sitecore named, John West, as its first-ever Lifetime MVP. West has been a long-time inspiration to the Sitecore developer community, acting as an uncompromisingly honest voice and a champion of the Sitecore platform. John is the author Professional Sitecore Development, the first book on Sitecore development published by Wrox, as well as hundreds of blog posts on

In literally thousands of comments on Sitecore’s community forum, he has built up an enthusiastic fan base of developers who seek out his advice and rely on his candor.

First Sitecore MVP
Alex de Groot,

I found this information from the below blog,

List of Sitecore MVP Year wise

Sitecore MVPs in Year 2008

  1. Alistair Deneys(Next Digital Group) Canada
  2. Eric Briand (Ergonet)
  3. Glen McInnis (Non-linear Creations) Germany
  4. Christopher Wojciech (Netzkern GmbH)
  5. Julius Ganns (Netzkern GmbH) Denmark
  6. Klaus Petersen (Alpha Solutions A/S)
  7. Thomas Eldblom (Pentia A/S) The Netherlands
  8. Justin Sjouw (Caesar Optimit)
  9. Marc van Aalst (Evident) The United Kingdom
  10. Mark Cassidy(CorePoint IT Limited)
  11. Andy Uzick (FMC Technologies, Inc.)
  12. Sasha Pfandt (Digitaria Interactive, Inc.)
  13. Ben Golden (Aware Web Solutions)

Sitecore MVPs in Year 2009

  1. Guillaume Buat-Ménard from Aqueduct
  2. Jason Davey from Bullseye
  3. Sebastian Winslow from Codehouse
  4. Stefan van Dockum from Colours
  5. Kern Herskind Hansen
  6. Vincent van Middendorp from Evident Interactive
  7. Sean Kearney from Hedgehog Development
  8. Andreas Bergström from Metamatrix
  9. Jens Mikkelsen from Pentia
  10. Matt Hovany from The Revere Group
  11. Aren Cambre from Southern Methodist University
  12. Gabriel Boys from Velir

Sitecore MVPs in Year 2010

  1. Karina Apostolides  Endava
  2. Rick Cabral   ISITE Design
  3. Corey Caplette        Velir
  4. Dustin Collis NavigationArts
  5. Sean Connell Verndale
  6. Cristoffer Crusell     Omegapoint
  7. Mark Graber  Oasis
  8. Jan Hebnes   1508
  9. Robbert Hock         Kayee, Internet & Software Oplossingen
  10. Nick Laidlaw  Oasis
  11. Larre Landin  Omegapoint
  12. Luc Mercx     Suneco
  13. Ronald Nieuwenhuis IQuailty
  14. Brian Pedersen        Pentia
  15. Stephen Pope         Eduserv
  16. Travis Sheppard      BGT Partners
  17. Jeff Stoll       Horizontal Integration
  18. Marco Tana   Roundedcube
  19. Tamas Varga Carnation
  20. Adam Weber Aware Web Solutions

Sitecore MVPs in Year 2011-12

  1. Adam Najmanowicz             
  2. Alan Gallauresi      
  3. Alexander Pfandt  
  4. Alistair Deneys
  5. Amanda Shiga
  6. Anders Dryer              
  7. Andrew King                  
  8. Andy Uzick                                          
  9. Aren Cambre                                       
  10. Arend van de Beek                             
  11. Atsuo Aoki                                           
  12. Barend van der Hout                          
  13. Ben Golden                                         
  14. Brent McLean                                      
  15. Brian Beckham                                   
  16. Charlie Turano                                    
  17. Chris Williams                                    
  18. Colin te Kemple                                  
  19. Daniel DeLay                                       
  20. Daniel Tobner                                       
  21. Eric Leong                                           
  22. Eric Nordberg                                      
  23. Eric Briand                                           
  24. Gabriel Boys                                        
  25. Glen McInnis                                      
  26. Jakob Leander                                    
  27. Jason Hedlund                                  
  28. Jens Mikkelson                                   
  29. Jeroen Speldekamp                          
  30. Joacim Schiodtz                                  
  31. Karina Apostolides                            
  32. Keiichi Hashimoto                             
  33. Kern Nightingale                                
  34. Klaus Petersen                                   
  35. Lars Erhardsen                                  
  36. Mark Ursino                                        
  37. Matt Hovany                                        
  38. Max Teo Wee Kwang                         
  39. Mike Edwards                                     
  40. Nick Hills                                            
  41. Nick Wesselman                                
  42. Paul Caponetti                                    
  43. Peter Iuvara                                         
  44. Remco van Toor                                  
  45. Richard Hauer                                     
  46. Robert Jongkind                                 
  47. Roger Norrlén                                     
  48. Sean Kearney                                     
  49. Sebastian Winslow                           
  50. Thomas Eldblom                               
  51. Troy Lüchinger                                   
  52. Vincent van Middendorp                   

NOTE: I didn’t find any specific list for year 2012, in Yyear 2013 dated 02-14-2013, Sitecore announced the MVP list but it announced the 2013 Sitecore Most Valuable Professionals (MVPs), as per the current year.   

Sitecore MVPs in Year 2013

  1. Anders Christian Ellegaard Gjelstrup                                
  2. Benjamin Vangansewinkel                                              
  3. Brian Mercer                                                                 
  4. Chait Suwannarat                                                          
  5. Chris Lees                                                                     
  6. Chris van de Steeg                                                         
  7. Dan Solovay                                                                  
  8. Douglas Couto                                                             
  9. Dražen Janjiček                                                            
  10. Emmanuel Christophe                                                    
  11. Grant Bartlett                                                                 
  12. Henri De Roeck                                                              
  13. James de la Bastide                                                        
  14. Jamie Stump                                                                  
  15. Joel Konecny                                                                 
  16. Joel Lipman                                                                    
  17. Kam Figy                                                                        
  18. Kamruz Jaman                                                                
  19. Keith Wood                                                                     
  20. Kiran Patil                                                                       
  21. Kosei Akazawa                                                                
  22. Krishnamurthy Balasubramaniam                             
  23. Marc Duiker                                                                    
  24. Mark Stiles                                                                      
  25. Markus Grün                                                                   
  26. Matthew Kenny                                                              
  27. Mikael Högberg                                                              
  28. Mikkel Holck Madsen                                                      
  29. Moe Sugizaki                                                                 
  30. Nick Allen                                                                      
  31. Niket Ashesh                                                                  
  32. Patrik Holmberg                                                              
  33. Paul George                                                                   
  34. Rahul Mahboobani                                                          
  35. Reto Hugi                                                                      
  36. Robin Hermanussen                                                       
  37. Ruud van Falier                                                             
  38. Sheetal Jain                                                                  
  39. Stephen Tynes                                                              
  40. Thomas N. Lin                                                               
  41. Tim Braga                                                                     
  42. Viv Slack                                                                       

Sitecore MVPs in Year 2014

Sitecore MVPs in Year 2015

Sitecore MVPs in Year 2016

I Hope this consolidated information will help in the Sitecore community.

NOTE: Please let me know if you found some delta in the provided information, so I will update the same, as I collected these information from different sources.

Happy sitecoring :)

Information Source links:

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...