Function in Joining column degrades performance in 2008

Question

Function in Joining column degrades performance in 2008

Viewing 5 posts - 16 through 19 (of 19 total)

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

kchant Ten Centuries Points: 1231 More actions · Answer 1

Basically, we has some SQL in a stored procedure which performed reasonably OK on 2000, but we found that when the same SQL was running on 2008 it performed terribly, we ended up having to force the Join types using the Option clause.

Another thing to check as far as the code is concerned is to double check that Left Joins are absolutely necessary...

Dave Ballantyne SSC-Dedicated Points: 33556 More actions · Answer 2

Jeff Moden (7/14/2010)
kchant (7/14/2010)
It may be worth checking your physical join operators as well, as I've encountered performance issues with stored procedures during migrations from 2000 to 2008...
Can you elaborate a bit on that?

I havent done any in depth investigation on this behaviour (yet... :)), but i think the behaviour changed in 2008 , so that the optimizer would consider loop joins , ( at all / more favourably , dont know !)

In this scenario i think we are in 'polish a turd' territory

http://sqlblogcasts.com/blogs/sqlandthelike/archive/2010/07/02/using-a-udf-as-a-sarg-make-a-hash-of-it.aspx

Clear Sky SQL
My Blog[/url]

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

I might have missed it, but did anyone mention that when upgrading from SQL 2000 to SQL 2008 you MUST update ALL statistics with a FULL SCAN?

Also did we get a stats IO and query plan output?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

kchant Ten Centuries Points: 1231 More actions · Answer 4

I haven't, I know it's a fairly common issue for a lot of performance issues, just make sure you do the larger tables out of hours, especially if you're experiencing performance issues already (and try not to use ms_foreachtable unless all your tables are really small)...

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

TheSQLGuru (7/15/2010)
I might have missed it, but did anyone mention that when upgrading from SQL 2000 to SQL 2008 you MUST update ALL statistics with a FULL SCAN?
Also did we get a stats IO and query plan output?

THAT's better. 😉

--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)
Intro to Tally Tables and Functions