Upgrading from 2000 to 2008 performance issue

Question

Upgrading from 2000 to 2008 performance issue

Viewing 6 posts - 16 through 20 (of 20 total)

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

The LOOP join is a hint where they're trying to force a choice on the optimizer. In addition to fixing the scan, I'd test taking out that hint to see what it does for performance.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

reggie burns-317942 Right there with Babe Points: 794 More actions · Answer 2

I removed the Loop hint and it seem to make the performance worse than what it was before the move.

It degraded tremendously with the loop hint removed.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

reggie burns-317942 (10/13/2010)
I removed the Loop hint and it seem to make the performance worse than what it was before the move.
It degraded tremendously with the loop hint removed.

Did you check the datatypes of the columns in the joins like Jo and I suggested? It makes a hell of a difference in 2K8.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

reggie burns-317942 Right there with Babe Points: 794 More actions · Answer 4

Yea. All the datatypes in the joins are int.

Could it be because of having over 50mil records in one table and over 80mil in a 2nd table and neither are partitioned in 2008?

reggie burns-317942 Right there with Babe Points: 794 More actions · Answer 5

Could I see a performance change with 2000 and 2008 if they are configured as

2000 has Windows2003 Enterprise with 8GB RAM

2008R2 has Windows2008 R2 Enterprise with 8GB RAM

Could 2008 performace go down tremendously?

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 6

reggie burns-317942 (10/15/2010)
Could I see a performance change with 2000 and 2008 if they are configured as
2000 has Windows2003 Enterprise with 8GB RAM
2008R2 has Windows2008 R2 Enterprise with 8GB RAM
Could 2008 performace go down tremendously?

I would say no, based on this change only.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]