Query time outs
I would like to know few pointers on tuning this query
The execution plan was created on build 10.0.2531, which is SQL Server 2008 (not R2), SP1 (not SP2). The latest service pack for SQL Server 2008 is SP4 (build 10.0.6000). This probably won't affect the execution plan or performance, but you probably ought to be on the latest service pack anyway.
As far tuning the execution plan is concerned:
- Adjust the indexing to avoid the Key Lookups and RID Lookup on PolicyStatus
- Consider creating a clustered index on PolicyStatus (currently a heap)
- Simplify the query into steps to help avoid the hash table spills
- Consider computed column indexes to make the CASE expressions on PolicyContingency seekable
- Consider computed column indexes on PolicyUnderlying for the same reason
- Consider a clustered index on dbo.CdtUA (UAId) to support the expensive inner join
- Consider a filtered index on PolicyTransaction to support the two inequality predicates
- Consider upgrading SQL Server to at least 2012 to get plan information about hash spills
- Consider upgrading to Plan Explorer Pro to see wait information
Those are the main things that leap out at me.