Recently, Erin (Blog|Twitter) and I encountered a bug in SQL Server that can affect some scenarios in a very negative manner.  When working with a database that was designed in SQL Server 2000 using the legacy LOB data types, text, ntext, and image, it is possible to encounter a problem when the ‘text in row’ table option has been configured and the data types for the columns were later migrated to the newer varchar(max), nvarchar(max), or varbinary(max) data types in SQL Server.  If you have a table that fits the specific scenario in question, and you try to analyze the table for data compression using the sp_estimate_data_compression_savings stored procedure, the end result is a hung session with a Schema Modification lock being held in tempdb that requires a restart of the instance to clear.  NOT GOOD!!!!!

We were able to reproduce this problem in SQL Server and we’ve subsequently filed a bug internally with Microsoft as well.  The good thing is that there is a work around, which I’ll cover later in this blog post, if you happen to run into this specific scenario and bug.

To start off with, if you create a table using varchar(max) and try to use sp_tableoption to set ‘text in row’ for the table it will error out:

DROP TABLE dbo.Table_1

CREATE TABLE dbo.Table_1
(
RowID int NOT NULL,
TestColumn varchar(max) NULL,
OtherColumn varchar(50) NULL,
ChangeDate datetime NOT NULL
)

exec sp_tableoption 'Table_1', 'text in row', '7000';

 

Msg 2599, Level 16, State 1, Procedure sp_tableoption, Line 102
Cannot switch to in row text in table "Table_1".

This is expected behavior by the Engine since the ‘text in row’ option only applies to the legacy data types and cannot be used with the newer LOB data types in SQL Server 2005+. For these, you instead must use the ‘large value types out of row’ option.

However, if you have a table that was created in SQL 2000 or before, and used text, image, or ntext columns, and the ‘text in row option’ was set:

DROP TABLE dbo.Table_1

CREATE TABLE dbo.Table_1
(
RowID int NOT NULL,
TestColumn text NULL,
OtherColumn varchar(50) NULL,
ChangeDate datetime NOT NULL
)

EXECUTE sp_tableoption 'Table_1', 'text in row', '7000';</blockquote>

and then later you upgraded to 2005+ and changed the column data type to varchar(max) or one of the other LOB data types:

ALTER TABLE Table_1;
ALTER COLUMN TestColumn varchar(max);

The ‘text in row’ value remains configured for the table.

SELECT text_in_row_limit
FROM sys.tables
WHERE name = 'Table_1';

If you attempt to estimate the compression for this table it will generate an error because the compression code creates a copy of the table in tempdb and tries to call sp_tableoption to set the option on the temp table used for sampling.

WARNING: Running the below code will result in a locked session that requires restarting the SQL instance to clear! DO NOT DO THIS ON A PRODUCTION SQL SERVER!

EXECUTE sp_estimate_data_compression_savings 'dbo','Table_1', 0, 1, 'row'

This command will result in error 2599 occurring, however, the SP doesn’t handle the error correctly for some reason, and it ends up with the session waiting on a SCH-M lock in tempdb that is being blocked by itself and never goes away.  The proc will actually complete and return the error for 2599 in SQL Server 2008 R2 + SP1, but it results in a stuck session for SQL Server 2008R2 RTM, SQL Server 2008R2 + SP2, and all versions of SQL Server 2012 that we’ve tested so far (we are waiting for further information from Microsoft at this point about why this might occur, so this may be updated in the near future, but I wouldn’t rely on this behavior currently).
In addition to this, you can’t clear the ‘text in row’ value from the table because executing sp_tableoption will return the above error.  The only work around that I’ve found so far for this issue is to add a dummy column to the table with one of the legacy types, turn off the option, and then drop the column.

ALTER TABLE Table_1 ADD [dummy] text;
EXECUTE sp_tableoption 'Table_1', 'text in row', 'OFF';
ALTER TABLE Table_1 DROP COLUMN [dummy];

Once you do this, sp_estimate_data_compression_savings  can estimate the compression savings. If you have migrated data types from legacy LOB types to the current LOB types, you should check for this scenario before attempting to run sp_estimate_data_compression_savings against the tables that changed.

I’ve done a bit of testing and I don’t think that this problem is prevalent in SQL Server instances in the field for a couple of reasons.  Firstly, it requires that you are attempting to estimate the compression savings for a table affected by the problem, and in my experience, data compression is not really widely used.  Secondly, it requires that you migrated from one of the legacy LOB data types to a current one through the use of ALTER TABLE DDL explicitly (the SSMS UI will generate a script to create a new table, copy all the data into it, drop the existing tables indexes and constraints, then drop the old table, and the rename the new table to the old table name, then build all the necessary constraints and indexes, so the table option won’t be in effect after the designer based change).

You really need the perfect storm of conditions to hit this specific problem inside of SQL Server, but it is still something to be wary of.

Hopefully you won’t hit it, but I’m sure someone else will at some point.