Run your query in query analyser as normal with text output, but wrap it with the following statements

SET SHOWPLAN_TEXT ON

GO

SELECT ....

GO

SET SHOWPLAN_TEXT OFF

GO

It basically shows the plan without actually running...

Absolutely!!

Slow

----------------------------------

SELECT

Shifts.dtShiftDate,

Shifts.chMachineID,

Shifts.siShiftNo,

Shifts.vchOperation,

Parts.vchPlatform,

Parts.vchOpening,

Parts.vchPlatform+' '+vchopening,

rtrim(parts.vchdescription)+' - '+rtrim(ltrim(Parts.chPartID)),

Shifts.intPressCycles,

Shifts.intPressCycles*bitIncludeInMachineHrs,

intPressCycles*bitIncludeInMachineHrs*bitIncludePressCycles,

Shifts.intTimeDown,

Shifts.intScrap, Shifts.intRings,

60*MachineHrs/intCycleTimeGoal*(ftYieldGoal/100)*(ftUptimeGoal/100)*bitIncludeinMachineHrs*bitIncludePressCycles,

60*MachineHrs/intCycleTimeGoal*(ftYieldGoal/100)*(ftUptimeGoal/100)*bitIncludeinMachineHrs,

60*MachineHrs/PartMetrics.intCycleTimeBud*(PartMetrics.ftYieldBud/100)*(PartMetrics.ftUptimeBud/100)*bitIncludeInMachineHrs*bitIncludePressCycles,

60*MachineHrs/PartMetrics.intCycleTimeBud*(PartMetrics.ftYieldBud/100)*(PartMetrics.ftUptimeBud/100)*bitIncludeInMachineHrs,

ltrim(datepart(month,dtshiftdate))+' - '+rtrim(datepart(year,dtshiftdate)),

Shifts.OperatorTimeMins,

Shifts.MachineHrs*bitIncludeInMachineHrs,

(Shifts.intPressCycles*bitIncludeInMachineHrs-intRings)*(1-ftyieldgoal/100),

(intPressCycles*bitIncludeInMachineHrs-intRings)*(1-PartMetrics.ftYieldBud/100),

MachineHrs*(1-PartMetrics.ftUptimeBud/100)*bitIncludeInMachineHrs,

PartMetrics.ftCrewSizeBud,...

1) As I said at the start, the records returned by both queries are equivalent (same records, same count). The count is about 4,500 out of a table that has...

Test server - the two queries look to have about the same execution plan. The Prod server sticks a sort order in as the second step where the other 3...

You were probably capable of finding this link yourself but perhaps there are some ideas to be had here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;321363&Product=sql2k

There seem to be a couple of kb and books...

That is a very good idea. What I'm haven't told you is that the slow query was generated from an Excel pivot table via odbc. My user knows that the...

Yes, the second query is more restrictive theoretically, but why would a condition that changes no logic (pulls the same number of rows), change the query execution speed by multiple...

The problem is not this simple. Notice I have 'shiftdate <= today' in the query, that should eliminate no records.

I haven't been able to gleen good information out of the execution plan because I don't understand it. When I see the costs in the gui plan I can understand...

Do you know who is timing you out? (server side, client side, etc.) What tool are you using to retrieve the records?

Clustering an index affects the physical layout of the data on disk. I think you can adjust free space in your cluster to make inserts a little faster. You can...

Also, any clues on why execution would be faster on my test box? Utilization is low on both boxes (prd is dual proc with 3gb ram and 10% avg utilization).

The...

How fitting to have sad faces all over my post, thats how I feel. Is there a way to shut that behavior off? 🙂

No malice intended in my prior statement. Caching may be taking the perf of the fast query from 4 seconds down to 2 seconds and the slow from 16 minutes...

With all do respect I don't think this has anything to do with caching. I say this because I have run the slow query back to back many times and...