Sitecore Items with Too Many Children

How to find items with too many sub-items the easy way

Sitecore generally recommends that items in the content tree not exceed more than around 100 items. Exceeding that can cause performance issues and Sitecore recommends that you convert those items into a bucket. An easy way to identify items with too many children is to search in SQL:

select *
	from items i
	left join (SELECT ParentID, COUNT(*) AS ChildCount
		FROM items
		WHERE ParentID IS NOT NULL
		GROUP BY ParentID
		HAVING COUNT(*) > 100) p on p.ParentID = i.ID
		where p.ParentID is not null
	order by p.ChildCount desc