Actual Execution Plan - Estimated/Actual Rows Differ Greatly

Question

I'm wondering if I can speed this query up. One thing I noticed is the estimated number of rows differs greatly from the actual number of rows.

https://www.brentozar.com/pastetheplan/?id=H1ya3-3pe

Answer 1

ClientRequestID in tblCR_ClientRequestTIP has no statistics, and SQL Server would love to have some. You can see that warning in the actual execution plan. This leads me to believe perhaps you have auto create statistics turned off. This may be causing issues. Have you updated statistics recently?

I'd consider adding the following indexes:

CREATE NONCLUSTERED INDEX IX_tblCR_ClientRequestTIP_ClientRequestID
ON dbo.tblCR_ClientRequestTIP(ClientRequestID)
INCLUDE (TIP_BeginDate, TIP_LocationName1);

CREATE NONCLUSTERED INDEX IX_tblCR_ClientRequestJOB_ClientRequestID
ON tblCR_ClientRequestJOB(ClientRequestID)
INCLUDE (JOB_Date);

CREATE NONCLUSTERED INDEX IX_tblCR_ClientRequestPP_ClientRequestID
ON dbo.tblCR_ClientRequestPP(ClientRequestID)
INCLUDE (PP_BeginningDate)

(there are others that could be added as well, this is just a sample)

Also of note, tblCommitment, tblStaff, and tblProgram do not have clustered indexes.

The tblClientRequestJOB row estimates are not exactly perfect, but the actual data size vs the estimated memory required for that table shows that row estimate is not really problematic. I doubt SQL Server would choose a different plan if it knew the actual row counts ahead of time in that instance. A far bigger change is likely to come from getting rid of the eager index spool resulting from the clustered index scan of tblCR_ClientRequestTIP.