Query time outs

Arvind 2015-04-22 06:40:28

I have the following query runnning on SQL2008R2+ sp2 instance. This query executes has different execution times 31 seocnds and sometimes it takes more than 45 seconds to complete.The application throws out an time out error message when it crosses 30 seconds as run time errors. We have updated stats on the relavant tables with fullscan and the same query was taking less than 30 seconds before 2 weeks.
I would like to know few pointers on tuning this query

SQLkiwi 2015-04-22 07:32:28
> I have the following query runnning on SQL2008R2+ sp2 instance

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:

  1. Adjust the indexing to avoid the Key Lookups and RID Lookup on PolicyStatus
  2. Consider creating a clustered index on PolicyStatus (currently a heap)
  3. Simplify the query into steps to help avoid the hash table spills
  4. Consider computed column indexes to make the CASE expressions on PolicyContingency seekable
  5. Consider computed column indexes on PolicyUnderlying for the same reason
  6. Consider a clustered index on dbo.CdtUA (UAId) to support the expensive inner join
  7. Consider a filtered index on PolicyTransaction to support the two inequality predicates
  8. Consider upgrading SQL Server to at least 2012 to get plan information about hash spills
  9. Consider upgrading to Plan Explorer Pro to see wait information

Those are the main things that leap out at me.