Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

same function from sql 2000 doesn't work on sql 2008 r2 Expand / Collapse
Author
Message
Posted Thursday, May 16, 2013 6:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:00 AM
Points: 14, 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
Post #1453488
Posted Thursday, May 16, 2013 7:02 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:19 PM
Points: 22,511, Visits: 30,235
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?



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)
Post #1453497
Posted Thursday, May 16, 2013 7:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:00 AM
Points: 14, 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
Post #1453508
Posted Thursday, May 16, 2013 7:38 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:43 AM
Points: 1,201, Visits: 2,661
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.



Post #1453532
Posted Thursday, May 16, 2013 8:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:00 AM
Points: 14, 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
Post #1453558
Posted Thursday, May 16, 2013 8:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:43 AM
Points: 1,201, Visits: 2,661
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.



Post #1453563
Posted Thursday, May 16, 2013 8:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007
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 Moden's 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)
Post #1453579
Posted Thursday, May 16, 2013 8:39 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:29 PM
Points: 35,977, Visits: 30,266
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1453584
Posted Thursday, May 16, 2013 9:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:00 AM
Points: 14, 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
Post #1453603
Posted Friday, May 17, 2013 7:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 1:35 PM
Points: 1,381, Visits: 2,004
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.
Post #1454011
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse