What indexes am I missing
CREATE NONCLUSTERED INDEX [IDX_loan_last_event_date] ON [dbo].[loan]
INCLUDE ( [last_event_dtm]) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
SELECT L.loan_no FROM dbo.loan AS L CROSS APPLY ( SELECT TOP (1) -- needs an ORDER BY clause at the end! csm_var_initial -- needs an alias or table name! FROM dbo.loan_score_results AS R JOIN dbo.loan_score_detail AS D ON R.loan_no = D.loan_no AND R.score_id = D.score_id AND R.csm_id = D.csm_id WHERE current_flg = 'Y' -- needs an alias or table name! AND R.loan_no = L.loan_no AND R.csm_id = 1 AND D.csm_var_id = 1 ) WHERE L.last_event_dtm >= DATEADD(DAY, 60, GETDATE());
Note the comments there, and the rearrangement of the WHERE clause to be in a form that can use an index. An index with last_event_dtm as the key, and loan_no as a key or included column would be useful, for example:
CREATE INDEX index_name ON dbo.loan (last_event_dtm, loan_no);
If you must use a function, the APPLY clause above can be straightforwardly converted to an in-line TVF. This will perform the same as the fully-inline query above. Scalar functions are to be avoided.