SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Linda_web
Linda_web
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 46
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
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93219 Visits: 38955
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?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Linda_web
Linda_web
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 46
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
Summer90
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7035 Visits: 3831
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
Linda_web
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 46
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
Summer90
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7035 Visits: 3831
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
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61401 Visits: 17954
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211161 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Linda_web
Linda_web
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 46
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
Megistal
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2904 Visits: 2555
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search