I believe the issue is with the poor I/O performance,

Ronnie.Doggart (3/12/2008)

---

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 its data using 0.2 seconds of CPU time. Now my initial theory was that it was an I/O problem, I am not so sure now. During the profile session the query ran 200 plus times with and average run time of 0.5 seconds.

During the 40 seconds that the query took other queries where running and completing in less that 1 second. In fact another user ran the same query with different selection criteria and it returned in less than 1 second. If my I/O theory was right I would have expected other queries to slow down during the 40 seconds period.

When I run the query on the customer site it returns in 1 second or less.

Help

Ronnie

Ronnie.Doggart (3/12/2008)

---

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 columns x and y

select * from table1 where x = 'xyz' and y='ab'

produces a plan that uses index1 and does more reads

select * from table1 where x = 'def' and y='st'

produces a plan that uses index2 and does fewer reads

Why does it not use index2 everytime as its columns are used in the 'where' clause of the select.

Ronnie