Unusual case

Windows 2003 SP2, SQL 2000 Enterprise, SP4.
I have one select using severals tables. One of them is XXX.
The query run in less than a second and use clustered index on XXX. Execution plan show that.
Now, I improve other different query adding a new non cluster index on XXX.
After that the new query run faster.
The first one run in more than 1 minutes with a lot of read ahead of XXX. However the execution plan still use the cluster index. There is no use of the new non clustered index at all.
Statistics are up to day. Defrag is ok and so on.
I was searching the reason and find nothing. So I have to delete the non clustered index and, of course, the first query back to normal and the second one run slow.
What I'm missing?
Fell free to ask for any question include both queries and execution plan if you like. Please don't question about statistics, defrag, space free, integrity, service pack, etc.

Yes. All of them as more than one column. Some begins with the same column than clustered, some in other place (2nd or 4th, just and example.).
The clustered has one column.

I agree with you Nikidar.
But in this case (may I don't explain correctly) with original query the execution plan is using clustered index. Why when I add other non cluster with cluster column as part of this new index, the query run slowly? and execution plan don't use it. I mean the execution plan is the same and now run slow.
I delete this new index and the query run faster again. That as no sense to me.

<p>&nbsp;I find something more.</p><p>If I execute the query&nbsp; with or without the new index nothing change in Query Analyzer. Same execution plan, same duration.<br></p><p>Now using the application, that is what is find with the new index using profiler:</p><p>Duration: 46298 &nbsp; CPU: 44844&nbsp; Reads:11645889</p><p>&nbsp;</p><p>Without new index:</p><p>Duration: 1561&nbsp; CPU: 266&nbsp; Reads: 12688&nbsp;</p><p>&nbsp;</p><p>Is a 3rd part application and use ODBC to reach the database.</p><p>&nbsp;</p><p>Any clue?&nbsp;</p><p><img src="file:///C:/DOCUME%7E1/Luis/LOCALS%7E1/Temp/moz-screenshot.jpg" alt=""><img src="file:///C:/DOCUME%7E1/Luis/LOCALS%7E1/Temp/moz-screenshot-1.jpg" alt=""><img src="file:///C:/DOCUME%7E1/Luis/LOCALS%7E1/Temp/moz-screenshot-2.jpg" alt=""><img src="/Community/forums/C:%5CTuning%5CMimo%5CMimo08.01.24%5Ccon%20index.jpg" mce_src="/Community/forums/C:TuningMimoMimo08.01.24con index.jpg"> </p>

How about disk placements?
I see it is not mentioned here and any chance to perfmon stats for those.

Don't know what you mean with placements.
But, with new index Profiler show a lot of reads. See one post before. Always running from application.

Is it on RAID5 or RAID10?

RAID 1.
4 Physical disks, 2 RAID 1.
OS, SQL, Log in one, data in other.

Agree with you.
I have to talk with Company Developer and show him what is going on with the application.

LOL Dinakar...
Using Profiler helps to see what sp_cursor uses handle to execute the queries/proedures and you can capture the sp_cursorprepexec calls to see what it is trying to execute...
then tune that query/procedure and also using PERFMON by looking at the SQL Server:Cursor Manager By Type – Cursor Requests/Sec counter, you can get a general feel for how many cursors are being used on the system by looking at this performance counter. Determine if cursors are the most appropriate means to accomplish the processing or whether a set-based operation, which is generally more efficient, is possible.

No index hint.
Yes, I get previous results with profiler.
The query is always the same.