Hi,

The customer will not give me access to the sql server cluster, the reason being that it runs other applications to the one I support.

I get information in small...

It does not appear to be running in parallel, and setting maxdop to 1 makes little difference.

I ran the query last night when the box was quiet and it...

Yes the tables do have Primary Keys:

ALTER TABLE [dbo].[LGNCC_ENQUIRY] ADD PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE...

Chris,

You are correct in assuming that it is not using the index that contains the objecttype even though it is specified in the query, there are only four unique objecttypes...

The lgncc_enquiry table is indexed on xref1 and has another index on xref1 and objecttype. The lgncc_caseevents table and lgncc_closedcaseevents are both indexed on caseid.

Ronnie

Why would SQL Server be flushing the cache so quickly ?

Ronnie

The clustered index scan on lgncc_caseevents and lgncc_closedcaseevents are on an index that is on caseid, which is the join column to the table in the view.

Also the first...