Never trust the customer 🙂 We were sure that the server had last service packs applied, that’s the first that we asked of the customer IT, and got a definite answer that “But, of course!”. So we didn’t really think this might be an issue. Then we noticed that SQL Server build number was 1399, while my laptop was running 3042. This reads: customer’s top-notch server is still on SQL Server 2005 RTM, my laptop is on SP2. There may hide the culprit.
So – a lesson learned: check the versions yourself, check them first, no matter what they say to you.
BUT STILL – did SP2 really bring such a huge performance improvement? Any experiences anyone? I’ve found out that there are some improvements with performance, but on such a huge scale? I mean, it is still three times faster on four times weaker hardware. This should call for a 12-fold improvement in certain operations, quite a feat 🙂
I look forward to this evening, when we have SP2 upgrade scheduled – I really can’t wait to see if this helps. I hope so, otherwise, we don’t need technical consultants on this project – we need exorcists!
Hi Vjeko, I’ll be interested to see if this solved the problem. When I read the first post on the mystery laptop I wondered how much of that 4GB of RAM on the laptop was being used to cache the SQL Server and how much of the 16GB was allocated on the server. It’s all a bit too technical for me and I’m sure the performance monitor would have picked that up.
I had a similar weird experience with 5.0 on SQL2005 when I was experiencing terrible performance due to the parameter sniffing problem whereby SQL is incorrectly selecting the wrong query plan for a parameterised query. The test system on the client site had exactly the same server version with similar server specs but they did not have the performance problems. The only suggestion I got from our MS Support team was that different installations behaved differently but I could find no reason for them to have good performance and me to have bad. When I re-created the database with the go live data conversion, their performance was terrible (just as mine had been) and I put on the CU3 for SP2 on the SQL box and put in some Query Plan Guides to force re-compiles on the slow running queries. It’s strange but I was using a restore of the same databases on two different servers but one had bad performance and one had good – weird huh? I never got to the bottom of it.
Dave: yes, totally weird. We are now going to try the same job on two more desktop and one more laptop machine, just to confirm whether the server performs exceptionally poorly, or my laptop performs exceptionally well. I somehow doubt the latter, because it is totally fragmented, slow 5400 RPM disk. And it’s running Vista. Geez.