We have been running our databases on Server 2003/SQL 2005, and recently set up a new server running Server 2008 R2/SQL 2008 R2. For the most part, performance has been just fine. However, there is a particular query that runs quickly (<1 sec) on 2005,
but very slowly (over 30 minutes!) on our new setup. The query is:
SELECT CT.ClearingUniqueTradeID, TradeID From Polaris.Bridge.TradeClearingTrade TCT
INNER JOIN [Polaris].[Clearing].[ClearingTrade] CT ON CT.ClearingTradeID = TCT.ClearingTradeID
WHERE TradeDate = '2010-06-11'
AND ClearingTradeStatusID = 4
ORDER BY ClearingUniqueTradeID
The query returns about 20,000 rows. If we remove the ORDER BY, it runs in less than a second. I've run the Tuning Advisor against the query and it suggested a couple of indices - these make the query fast again, but I don't think that should be necessary
merely to sort 20k rows of numerical values. SQL 2005 did fine without them.
I did apply CU4 to see if there were a
View Complete Post