changed behaviour of textptr sp122, sp134/sp135

[luc_vanderveurst]

Hello,

I have noticed that the behaviour of textptr has been changed between ASE versions sp122 and sp135.

If you have a table

create table tt(i int, j text null)

and insert a null value for t

insert tt(1, null)

and then execute

select textptr(j) from tt

then you get the following result in sp122 :

----------------------------------
NULL                              

and the following result in sp134/135 :

----------------------------------

0x51030000011000000000000000000000

What's the reason behind this change ? And how do I check if a page has been allocated to a text column or not ?

Thanks,

Luc.

[kevin_sherlock]

Well,  maybe has something to do with in-row LOB feature?  That is, a null value is "stored" in-row, and maybe has a valid text pointer.  something is weird here.

Try to add another column to the output.  IE, run something like

select convert(binary(16),textptr(j)), textptr(j), datalength(j)

from ttt

Also post output of:

sp_help ttt

sp_helpdb <yourdbname>

from both sp122 and sp135 versions.

Btw, for me, a text value has a page (or pages) if "datalength(<textcolname>)" is not null or greater than zero.

[Former Member]

Thanks Luc and Kevin,

It seems our default is to allocate a page for each text field even if its null.

We loaded our databases from a 15.5 server so not sure why the default is set this way.

Perhaps we missed an option somewhere ?

It might explain why we've seen a drop in performance with one of our procedures. Will set up a test.

Thanks again.

[c_baker]

This was an old behavior of ASE.  Long ago.  How old is the database?  Was it originally upgraded through a few releases.?

I seem to recall that at the point where we did fix the page allocation for zero length, that there was a dbcc command that needed to be run on existing tables/columns (dbcc upgrade_object) to stop allocating empty pages for null text columns.  This was in the EBF or MR release notes at the time.

Perhaps someone can remember the command that needs to be run.  I will try look in my archives.

Chris

[Former Member]

> How old is the database? 

The database was orginally built on 15.5 about 3 years ago and data migrated across to it.

> Was it originally upgraded through a few releases.?

Its been through 2 or 3 in place upgrades to different 15.7 versions.

We don't seem to be affected by it - but one of our procs is 3 times slower and our takes are wasting 20Gb of space.

Setting dealloc_first_txtpg seems to fix it for us.

Thanks

[c_baker]

I think I found it:

dbcc fix_text ((table_name | table_id))

Chris

[Former Member]

Hi

Thanks for this - it might help us with another issue we've been having with performance of text fields.

I don't have access to anything less than SP134 so would appreciate if you can see if the space usage changes between the 2 versions.

When I run sp_spaceused on one of my tables where ALL the text fields are null it shows huge space usage.

name        rowtotal  reserved          data           index_size       unused

Messages 56099    1048752 KB     7856 KB    1024128 KB     16560 KB

If I create the same table but use a varchar instead of a text field I get

name        rowtotal  reserved          data           index_size       unused

Messages 56099    6144 KB           4704 KB     864 KB            528 KB

It looks like the text field has 16k pre-allocate to it (Our server is set to 16k pages).

Could you see what the difference in spaceused is for your tables on SP< 134 and SP134.

(Have you reported it as an incident ?)

Thanks

Mike

[luc_vanderveurst]

Hi Mike,

Since we saw that textptr(column) returned non-null values when inserting a row with a NULL text value, the first thing we checked was if there was also space allocated to the column.

That didn't seem te case.

After creating the table :

sp_spaceused tt, 1
index_name size reserved unused
---------- ---- -------- ------
ttt        4 KB 32 KB    28 KB 

(1 row affected)
name rowtotal reserved data index_size unused
---- -------- -------- ---- ---------- ------
tt   0        64 KB    4 KB 4 KB       48 KB 
(return status = 0)

After inserting 5000 rows with NULL for the value of the text column :

sp_spaceused tt, 1
index_name size reserved unused
---------- ---- -------- ------
ttt        4 KB 32 KB    28 KB 

(1 row affected)
name rowtotal reserved data  index_size unused
---- -------- -------- ----- ---------- ------
tt   5000     128 KB   80 KB 4 KB       36 KB 

So the tablesize increased, but the indexsize (index ttt) didn't.

After update of the text column and giving it a value :

sp_spaceused tt, 1
index_name size     reserved unused
---------- -------- -------- ------
ttt        20004 KB 20016 KB 12 KB 

(1 row affected)
name rowtotal reserved data   index_size unused
---- -------- -------- ------ ---------- ------
tt   5000     20176 KB 148 KB 20004 KB   16 KB 

So that seems ok, we have 4K pages, 5000 x 4K = 20000K

It seems that the behaviour concerning allocating disk space didn't change between versions <sp122 and >=sp134, only the fact that textptr doesn't return NULL values has changed.

As for your questions: how do you check that the text column is NULL ?

Did you insert NULL explicitely ? Was a value inserted that was set to NULL afterwards ?

When a value was inserted, it will use 16K even it was the empty string ''.

Updating it to NULL won't have an effect unless dboption "deallocate first text page" is set to true.

(or setting the deallocation option on the table with : sp_chgattribute <table>, "dealloc_first_txtpg", 1)

No, I didn't create in incident yet.

Best Regards,

Luc.