Had to modify it to get it to work on my system:

JOIN sys.dm_db_missing_index_details ON sys.objects.object_id=dm_db_missing_index_details.object_id

modified to be

JOIN sys.dm_db_missing_index_details ON sys.objects.object_id=sys.dm_db_missing_index_details.object_id

All,

Could I replace :

SELECT @caseID = CASEID

...

Hi,

Ask customer for some perfmon logs, showing disk queues and read/write times for the database disk. The customer hardware people say there is nothing wrong on the cluster but I...

Hi,

I have asked the customer to actually record when the application is slow during the day, they are doing this today so hopefully I will get a better picture of...

Guys,

Thanks for all the advice. I have asked for more traces from the server which will be done tomorrow so I will see if there are any other queries hitting...

Ran sp_who2 and did not see any blocking, however during the running of the query which took over 90 seconds sp_who2 reported that the spid was sleeping a lot of...

Was able to run some test on the server with no other users active, the timings where:

(3323 row(s) affected)

Table 'LGNCC_CLOSEDCASEEVENTS'. Scan count 1, logical reads 19539, physical reads 2, read-ahead...

Tried rewritng the view without the union all and various index combinations but did not show much improvement. Decided to look at what the server was doing during the query...

Hi

When you say I/O output problem do you mean network bandwidth ?

Ronnie

All,

Thanks for the help on this.

Ronnie

Philip,

[font="Courier New"]select

ENQUIRY.CASEID,

ENQUIRY.CASEREF,

ENQUIRY.ENQUIRYTYPE,

ENQUIRY.OBJECTTYPE,

ENQUIRY.XREF1,

ENQUIRY.XREF2,

ENQUIRY.XREF3,

ENQUIRY.OBJECTDESC

FROM LGNCC_ENQUIRY ENQUIRY WITH (INDEX(lgncc_enquiry_t2))

WHERE xref1 = '1001183168' AND

objecttype = 'D4'

(971...

Philip,

xref1 is varchar(80).

[font="Courier New"]

SELECT * FROM LGNCC_ENQUIRY WITH (INDEX(lgncc_enquiry_t2))

WHERE xref1 = '1001183168' AND

objecttype = 'D4'

(971 row(s) affected)

Table 'LGNCC_ENQUIRY'. Scan count 1, logical reads 3058, physical...

All,

Info lgncc_enquiry has 434523 rows and had statistics collected yesterday.

[font="Courier New"]SELECT * FROM LGNCC_ENQUIRY WHERE xref1 = '1001183168' AND objecttype = 'D4'

(971 row(s) affected)

Table 'LGNCC_ENQUIRY'. Scan...

All,

I have noticed that on the customer site the following query uses different plans for different data. So as an example:

Table has two indexes:

index1 is on column x

index2 is on...

All,

I have had another look through the profiler trace I have to try and get to the bottom of this issue. The worst performing query took 41 seconds to return...