same function from sql 2000 doesn't work on sql 2008 r2

Question

same function from sql 2000 doesn't work on sql 2008 r2

Linda_web

SSC Enthusiast

Points: 179
More actions
May 16, 2013 at 6:53 am

#275250

We have very strange situation I hope someone can help, we copied a database from sql 2000 sp4 to sql 2008 R2 SP2 , runs with compatibility level 2000, it has table valued function basically returns text column, the statement has select * from mytable where len(convert(varchar(4000), TextCol)) > 25
Same function works in sql 2000, but doesn’t work in sql 2008, simply no result just runs hours and hangs and we have to kill it. Any idea why? And what I need to do in sql 2008,
BTW: this new server has more RAM and CPU than old server, old server on 2003, but this sql on win 2008 r2
Thank you

Viewing 12 posts - 1 through 11 (of 11 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Really not enough info to really help. The problem is we can't see what you see. Can you post the offending query and the DDL for the table(s) it runs against?

Linda_web SSC Enthusiast Points: 179 More actions · Answer 2

My question is simple what is the different on TEXT data type from sql 2000 to sql 2008?

Why same table , same data size , same column, same statement works in sql 2000 SP 4 but hangs on sql 2008 R2, the db restored from sql 2000 backup to sql 2008 R2

Thanks

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 3

Have you at least run update stats at 100% after you moved the db to SQL2008? Microsoft recommends rebuilding all indexes. Your access paths for queries are not going to be that good and that might be the reason you are running the query and it runs forever and you have to kill it. Probably doing some table scans.

You probably should run DBCC CHECKDB if you haven't already as well.

Linda_web SSC Enthusiast Points: 179 More actions · Answer 4

Thank you for reply, yes we rebuilt all indexes and ran UPDATE STATISTIC and still same,

I will run DBCC CHECKD.

Thank you for your suggestion

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 5

Good on the rebuild index/update stats.

Another quick question. When you were running the db on SQL2000 did you run the SQL2008 Upgrade Advisor on the DB? Probably not a big deal but it points out possible database upgrade issue. Probably not a big deal with a select statement not working.

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

One suggestion, since you are now on 2008 can you change the datatype of your text column to varchar(max)? That will likely help your query because it won't have to convert each and every row.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 7

Linda_web (5/16/2013)
We have very strange situation I hope someone can help, we copied a database from sql 2000 sp4 to sql 2008 R2 SP2 , runs with compatibility level 2000, it has table valued function basically returns text column, the statement has select * from mytable where len(convert(varchar(4000), TextCol)) > 25
Same function works in sql 2000, but doesn’t work in sql 2008, simply no result just runs hours and hangs and we have to kill it. Any idea why? And what I need to do in sql 2008,
BTW: this new server has more RAM and CPU than old server, old server on 2003, but this sql on win 2008 r2
Thank you

I know this is no help on the current problem but I have to ask. If you convert the column to varchar(4000) on the fly for this, why not just convert the column in the table to varchar(4000) so you don't have to bother with the annoyances of the Text datatype?

My other question would be, why not just use DATALENGTH instead of doing the conversion to varchar(4000)?

--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)

Linda_web SSC Enthusiast Points: 179 More actions · Answer 8

Thank you all, yes I ran upgrade advisor and there were no issue, other than sql server level warning

I tried datalength, I tried many other things didn’t work. At this time changing data type not an option. I will run DBCC checkdb

BTW: I added a indexed column on the select and same issue, I don't have any issue on other columns on same table when I do select, only with TEXT column, strange works just fine on sql 2000 on very old computer. maybe sp cache something to do it

Thank you all for your time, I appreciate your inputs

Megistal SSCrazy Eights Points: 8787 More actions · Answer 9

I would go with what Sean Lange recommend first.

In no particular order:

Try #1:

If you can I would change the table convert data type to VARCHAR(26) and check the performance result if it changed.

(The query looks only for the 25 first characters regardless of the remaining ones so I would avoid taking more than necessary)

Try #2:

However if this is not possible, I would try adding a persisted computed column having the result of your where clause condition in it and outputting a bit if it satisfies the predicate or not.

It would be computed only once per row and you will be able to filter on that computed column (even be indexed if necessary even thought indexing a bit is arguable).

It might also fall into the same issue the initial query as the first time it is added, but I feel it would worth a try.

Try #3:

Is this a heap table? Could forwarding pointers be the cause if that column has several modifications done on it?

Try #4:

Would it be possible to isolate the database and avoid running it into 2000 compatibility mode and let it alone run on 2008 compatibility mode just for this particular issue?

I would like to know if that issue occurs only with the compatibility mode option or if this also occurs natively to SQL 2008.

Try #5:

Try moving out the table value function, they often gets it the way of performance.

Let us know, the more details we have the better our suggestions will be.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Linda_web (5/16/2013)
We have very strange situation I hope someone can help, we copied a database from sql 2000 sp4 to sql 2008 R2 SP2 , runs with compatibility level 2000, it has table valued function basically returns text column, the statement has select * from mytable where len(convert(varchar(4000), TextCol)) > 25
Same function works in sql 2000, but doesn’t work in sql 2008, simply no result just runs hours and hangs and we have to kill it. Any idea why? And what I need to do in sql 2008,
BTW: this new server has more RAM and CPU than old server, old server on 2003, but this sql on win 2008 r2
Thank you

Please post the code for the function and the query using it. Without it, folks are just guessing.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Miles Neale SSChampion Points: 13147 More actions · Answer 11

Sean Lange (5/16/2013)
One suggestion, since you are now on 2008 can you change the datatype of your text column to varchar(max)? That will likely help your query because it won't have to convert each and every row.

To add to what Sean has stated, you might want to move not to varchar(max) for text ntext and Image datatypes since they will be deprecated in future versions of SQL Server. Simply stated if you change it now while you are looking at it later when you move up to a newer version of SQL Server you will not have to change it.

You can find documentation on this on the following page - http://msdn.microsoft.com/en-us/library/ms143729.aspx

Not all gray hairs are Dinosaurs!