Comparison in Where or Having Clauses NOT working

  • GilaMonster (3/13/2012)

    Ken at work (3/13/2012)

    So in addition to that you're saying that SQL 2005 may run somewhat slower on some queries. Swell!

    A very small percentage of queries (and I mean very small). Usually from changes to the optimiser that result in a different plan being generated and fixable in every case I've seem (which admittedly hasn't been many). It's not something that happens every system and it's not something that one should expect when upgrading.

    At a previous employer we moved both the PeopleSoft HR and Finance systems from SQL Server 2000 to SQL Server 2005. This was needed as the HR system upgrade required the move. We experienced a performance issue with the finance system under SQL 2005 when users attempted to open POs. Turned out there was a table with over 200,000 rows with no indexes defined. For whatever reason, this was not a problem with SQL 2000, but SQL 2005 had a problem. Once we found this table (it took over two weeks, server side traces and DTA couldn't find it) and created a proper index, no more problems.

  • I recall seeing 2 instances of this. The clients went from 2000 to 2005 and a few queries performed poorly. Full index rebuilds were done in both cases so I didn't bother with statistics. I found in both cases there was a heap involved. Since I really try to avoid heaps since I consider them a heap of trouble, I created sensible clustered indexes on the tables and everything ran fine. I never did investigate whether or not heap handling is anything common to upgrading from SQL 2000 so I couldn't really say for sure.

    Todd Fifield

Viewing 2 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply