Group By statemnt working in different manner from SQL 2005 to SQL2008

Question

We have got a complex query using 34 group by fields and 34 + 8 Sum() fileds in the output.

We have migrated DB from a SQL Server 2005 to a SQL 2008 R2 instance.

Now the same query on the same data tooks about 1 min on SQL 2005 and about 20 min on SQL 2008 R2, while others perform normally.

Index are rebuilded and stats are updated.

During our investigation whe tryed to remove some filed fron both group by and Select statement, and by doing this query time boosted to 10 secs.

By the way, of course we can optimize our query, but the question is: why this query performace is dramatically falled down migrating to SQL server 2008 r2?

Any idea?

Thanks in advance.

---

Ruggiero Lauria
MCT-MCITP-MCSA-MCSE-MS SQL DBA

Answer 1

The size of the disk space where log files (.ldf) are kept from two servers can also play a bit of role speed wise.

---

Jack of All

Answer 2

Can you compare execution plans on both servers - how they are different?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog