Problem

When I first developed the FAQ area on

SQLServerCentral.com, it performed wonderfully. After a number of large FAQs

began to be entered in though and as the library grew to over a few hundred

entries, I noticed that the web page would some times display much slower and

often timeout. I tried everything to remedy like more aggressive caching and

query hints. None worked and the frustrated readers continued to e-mail me about

the FAQ area and the Script areas slowness..

Solution

Since we're at a hosting provider, we're limited to what we can do

hardware-wise to remedy this problem. For example, we can't move the text and

image columns to a different file group on a different drive array. So, I

decided to try the new (not so new anymore) TEXT IN ROW feature that ships with

all editions of SQL Server 2000. To understand this feature, you must first

understand how text, ntext and image columns work. Take for example the below

abridged schema:

CREATE TABLE [FAQ2] (

[FAQID] [int] IDENTITY (1, 1) NOT NULL ,

[TITLE] [varchar] (200),

[AUTHORID] [int] NULL ,

[RATING] [numeric](18, 4) NULL,

[FAQBODY] [text] NOT NULL)

Once this table is created, notice how in the Table Properties screen below

how it shows its size as 16 bytes. Text and image columns can store up to 2 GB

of data though. So, why the disparity? First you must remember the total amount

of space that can be stored on a data page in SQL Server, which is 8060 bytes.

Hardly enough room for a 2GB text field! So, instead of storing the text or

image column on the same data page, it simply stores a 16 byte pointer to where

it can be found. This is sometimes why text and image columns perform slower

than a varchar(2000). In our varchar(2000) example, SQL Server never needs to

leave the data page to find its data and perform reads much quicker than with a

text column.

TEXT IN ROW to the rescue! The TEXT IN ROW feature allows you to store

smaller amounts of text and image data onto the data page. The great thing about

this feature is it lets you have your cake and eat it to. It will store data

less than a preset amount on the data page and anything above is still done the

traditional way. It's a great way of solving the issue of an analyst coming to

you and saying they "may" someday need all those text columns, even though

they're only using 200 characters at the most.

To enable the feature, use the SP_TABLEOPTION system stored procedure and

specify the table as shown below:

SP_TABLEOPTION 'FAQ2',

'TEXT IN ROW', 'ON'

Now the feature is turned on and has defaulted to anything less than or equal

to 256 bytes will be stored on the data page. This 256 byte setting is the

optimal setting for the feature. You may want to set this higher nonetheless.

For example, I noticed by running the below query that the average column was

about 600 bytes.

SELECT AVG(DATALENGTH(FAQBODY)) FROM FAQ2

I also determined from the following query that the maximum that this column

was storing was 9,243, a little too much for a varchar field.

SELECT MAX(DATALENGTH(FAQBODY)) FROM FAQ2

So I took the average column length (600) and decided to store all columns

equal to or less than 600 bytes in the data row. You can do this by using the

SP_TABLEOPTION syntax again as shown below:

SP_TABLEOPTION 'FAQ2',

'TEXT IN ROW', '600'

As soon as you do enable, you will see no effect until new rows are inserted

into the table. Most people can't wait this long however so the workaround would

be to run a quick UPDATE statement like the one shown below to move the eligible

items back to the data page:

UPDATE FAQ2

SET FAQBODY = FAQBODY

Any new items are automatically moved if their eligible. You will also know

when you go to Table Properties (shown below), that the table shows the FAQBODY

text column as 600 bytes. Data stored in the data page operates much like a

varchar field. For example, if you try to insert 280 characters of data into the

FAQBODY column, only 280 bytes of space will be used in the row. Anything

greater than 600 bytes stores the 16 byte pointer in the row and the remainder

is stored off the data page.

When you turn the option off by using the below syntax, it may take a long

time for this to finish as it has to move all the data back off the data row.

This is a logged operation so your transaction log may fill up quickly and your

table will be locked while this is occuring.

SP_TABLEOPTION 'FAQ2',

'TEXT IN ROW', 'OFF'

Note : If you have multiple text or image columns in a table that has this

option turned on, SQL Server will try to fit as much as it can on a single data

page. Anything that can't fit will be stored off the data page. Make sure that

the combined size of all the columns does not exceed 8060 bytes to avoid any

problems with this.

Negative Effects of TEXT IN ROW

Before implementing this option in production, make sure you fully test your

application. This extensive testing would be needed to confirm that your

application does not use one of the following text and image statements:

• READTEXT
• UPDATETEXT
• WRITETEXT

This syntax will not work on tables using the TEXT IN ROW option.

Conclusion

We at SQLServerCentral.com have seen a tremendous improvement in the time out

rate of our FAQ area as soon as we turned on this option. The data retrieval of

these columns are at least 50% faster from our initial tests. As a general rule

make sure that you have justified why you're using a text or image columns and

confirm that these actions can't be done through a larger varchar field. Let us

know what you think of text and image column performance by clicking on the Your

Opinion button below.