Thank you! That was excactly what I wanted to know 🙂

But then I have another issue of discussion. I thought they could be related but maybe not.

Our company upgraded both our software from SS 7.0 to SS 2000 and our hardware to a new cluster configuration. We have alot of similar aggregation SPs' that run every night aggregating between .5 and 5 millions of records each.

Now to the issue which I thought I had resolved in a way less then obvious.

The SPs' before the upgrade took (each) a couple (1-10) minutes to run. After the upgrade they never finished running. Or atleast they all took more then 12 hours to run (and we had to kill them). The problem was that running the same SPs' in QA still took 1-10 minutes. All were GROUPing BY a 'timestamp' column (an integer with number of seconds since '1970-01-01'). The table/index structure remained the same after the upgrade. The funny thing was that when replacing our IN parameters in the SPs' to their static equivalences the SPs' became fast again. After some time I realised that the Query Plan for the SP with the parameters always ended up using Nested Loop Joins and doing not in Parallell. When not using parameters it changed join type and started using parallellism. The ugly part of the story is that when doing a 'parameter swap'

(i.e using a parameter declared as an INT and then: DECLARE @b-2 AS INT

SET @b-2 = 'value of IN parameter to SP'

and then using @b-2 instead of @a in the rest of the SP) they all have the same runtime (and Query Plan) as with static values (and when the SP is run as a query in QA).

I do not know why this 'avoided' the problem and how to protect one self in the future for this problems, all I know is that this was good enough workaround for this particular problem.

If anyone has any comments or solutions to why this happend and/or why my way managed to avoid the problem I would be very happy to hear it!

Regards, Hans!

Edited by - hanslindgren on 05/27/2003 05:33:36 AM