It’s difficult to say why Query Optimizer do not choose most effective plan. Especially, when you are saying that indexes and statistics are OK. It...

Let me guess. Your prod server is SQL 7 and your dev - is SQL 2000?

Query optimizer in SQL 2000 much better that in SQL 7.

But still, even SQL 2K...

Can you try to run these query with INNER JOINs :

'Slow'

SELECT ...

FROM ShopFloor.dbo.Operations Operations

INNER JOIN ShopFloor.dbo.vShifts Shifts ON Operations.vchOperation = Shifts.vchOperation  AND Shifts.vchOperation<>'DEVELOPMENT'

INNER JOIN ShopFloor.dbo.Parts Parts ON Parts.chPartID = Shifts.chPartID

INNER...

It seems likee you have this condition in your 'slow' query:

WHERE([Operations].[vchOperation]=[Shifts].[vchOperation] AND Convert([Shifts].[dtShiftDate])>dateadd(month, ... ,

but do not have it in your 'fast' query.

Can you post ALL joins and where clauses where you are using this colums (shiftdate) for both cases.

Will,

in your spProc2 do this:

ABORT_TRANSACTION:

  IF @@TRANCOUNT > 0

     ROLLBACK TRANSACTION

BTW, stop using GOTO

Igor

Aaron,

1) How many rows does this query return

where shiftdate <= today ?

2) How do you get this (today - 5 months)? Can you give us more details.

Igor

You could use the following approach in your WHERE clause:

WHERE  (EmployeeID = @EmployeeID OR @EmployeeID IS NULL) AND ...

HTH,

Igor

A few thoughts:

1. Run DBCC USEROPTION when user gets this error and when he doesn’t (when he is a member of sysadmin role)

2. Try SET ANSI_WARNINGS OFF for user's session (use SQL...

You could try to set priority boost = 1, but read in BOL about "priority boost Option" first.

Good luck,

Igor

P.S. Did you reboot your server after setting working set size =...

Check these articles:

http://support.microsoft.com/default.aspx?scid=kb;en-us;276044

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

From BOL:

"Standard 32-bit addresses can map a maximum of 4 GB of memory. The standard address spaces of 32-bit Microsoft Windows NT® 4.0 and Windows 2000 processes are therefore...

" Windows 2000 automatically supports single sign-on for users within a domain forest."

- Windows 2000 Resource Kit, Planning Distributed Security, Authenticating All User Access

" Kerberos authentication uses transparent transitive trust among...

These quotes might help you:

" Windows 2000 automatically supports single sign-on for users within a domain forest."

" Kerberos authentication uses transparent transitive trust among domains in a forest, but...

Try this:

osql -L

or

isql -L

But results might not be 100% accurate.

Igor