Index Tuning on Joined Query

Try this:

1. CHILDEVENTPROPERITIES - Make a nonclustered index:

   • Key columns: Property, PropertyValue
   • Included column: EventId

2. CHILDEVENTS - Make a nonclustered index:

   • Key columns: AddDate, Type
   • Included column: ChildID, EventID

3. CHILDINFORMATION - Make a nonclustered index:

   • Key columns: ChildID

In cases 1 and 2, check what is the optimal key column order (optimal leading column). Also evaluate if you need to keep existing nonclustered indexes, depending on your workload. This specific query doesn't need them.

One additional thing to try is breaking the query into a union, to get rid of the OR in the WHERE clause - see how it affects the plan.

SELECT
   CHILDID
   ,SUM(PREVIOUSWEEKSACCIDENTS) AS PREVIOUSWEEKSACCIDENTS
FROM
   (SELECT
      CHINFO.CHILDID
     ,COUNT(*) AS PREVIOUSWEEKSACCIDENTS
   FROM
      BKA.CHILDINFORMATION CHINFO
      JOIN BKA.CHILDEVENTS CHE
         ON CHE.CHILDID = CHINFO.CHILDID
      JOIN BKA.CHILDEVENTPROPERITIES CHEP
         ON CHE.EVENTID = CHEP.EVENTID
   WHERE
      CHE.TYPE = 'ACCIDENT'
      AND CHE.ADDDATE BETWEEN @lastweekstart AND @lastweekend
   GROUP BY
      CHINFO.CHILDID

   UNION ALL

   SELECT DISTINCT
      CHINFO.CHILDID
     ,COUNT(*) AS PREVIOUSWEEKSACCIDENTS
   FROM
      BKA.CHILDINFORMATION CHINFO
      JOIN BKA.CHILDEVENTS CHE
         ON CHE.CHILDID = CHINFO.CHILDID
      JOIN BKA.CHILDEVENTPROPERITIES CHEP
         ON CHE.EVENTID = CHEP.EVENTID
   WHERE
      CHE.TYPE = 'POTTYBREAK'
      AND CHEP.PROPERTY = 'SUCCESS'
      AND CHEP.PROPERTYVALUE = 'FALSE'
      AND CHE.ADDDATE BETWEEN @lastweekstart AND @lastweekend
   GROUP BY
      CHINFO.CHILDID) AS t
GROUP BY
   CHILDID

Since you have a foreign key from CHILDEVENTS to CHILDINFORMATION and you don't select any column from the child information table, you can skip that table. One less join will certainly not hurt:

SELECT CHE.CHILDID, COUNT(*) AS CURRENTWEEKPOTTYBREAKS        
FROM  BKA.CHILDEVENTS AS CHE           
WHERE CHE.TYPE ='POTTYBREAK'        
  AND CHE.ADDDATE BETWEEN @currentweekstart AND @currentweekend         
GROUP BY CHE.CHILDID ;

As for indexing, the existing index on (Adddate,Type) INCLUDE (ChildId, EventId) does not look very good for this query. I would try replacing it (in fact removing both those AddDate-Type indexes) with an index on:

(Type, AddDate) INCLUDE (ChildId)