Understanding LOB data (2008/2008R2 & 2012)

I know it’s been a while since I last blogged here on SQLskills. It’s been a busy few months with Immersion Events, conferences and even a bit of personal travel in there. And, we’ve also been blogging very regularly (weekly) at SQLServerPro here: Kimberly & Paul: SQL Server Questions Answered. And, this is what’s brought me to write this post. A few days ago, I blogged What about moving LOB data? and in part I discussed some of the issues moving SQL Server LOB data using OFFLINE operations. In part II (What about moving LOB data while keeping the table online?), I’m continuing the discussion with some additional points for ONLINE moves.

However, I also wanted to make sure that people fully understood how SQL Server LOB data works with regard to the physical location within the database… So, I created a script to walk you through it. This post will show you the specifics…

First, we’ll create a TestLOB database. Please note that this script drops TestLOB if it exists. If you already have a database with this name, it will be removed. Additionally, you will probably need to change the directory structure to something more appropriate on your system. These paths are the default data/log locations for SQL Server 2012 running as a default instance (MSSQLServer).

SET NOCOUNT ON
GO

USE [master]
GO

IF DATABASEPROPERTYEX('TestLOB', 'status') IS NOT NULL
BEGIN
    ALTER DATABASE [TestLOB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [TestLOB];
END
GO

CREATE DATABASE [TestLOB]
CONTAINMENT = NONE
ON  PRIMARY
(   NAME = N'TestLOBPrimary'
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestLOBPrimary.mdf'
, SIZE = 5120KB , FILEGROWTH = 1024KB ),
FILEGROUP [FG1]
(   NAME = N'FG1File1'
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FG1File1.ndf'
, SIZE = 61440KB , FILEGROWTH = 20480KB ),
(   NAME = N'FG1File2'
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FG1File2.ndf'
, SIZE = 61440KB , FILEGROWTH = 20480KB ),
FILEGROUP [FG2]
(   NAME = N'FG2File1'
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FG2File1.ndf'
, SIZE = 61440KB , FILEGROWTH = 20480KB ),
(   NAME = N'FG2File2'
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FG2File2.ndf'
, SIZE = 61440KB , FILEGROWTH = 20480KB ),
FILEGROUP [FG3]
(   NAME = N'FG3File1'
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FG3File1.ndf'
, SIZE = 61440KB , FILEGROWTH = 20480KB ),
(   NAME = N'FG3File2'
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FG3File2.ndf'
, SIZE = 61440KB , FILEGROWTH = 20480KB )
LOG ON
(   NAME = N'TestLOBLog'
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestLOBLog.ldf'
, SIZE = 102400KB , FILEGROWTH = 10240KB);
GO

USE TestLOB;
GO

Next, I’ll make the FG1 filegroup the default filegroup. For all objects that are not explicitly defined (using the ON clause), they will be created in FG1.

ALTER DATABASE TestLOB
MODIFY FILEGROUP FG1 DEFAULT;
GO

We’ll start by creating the TestLOBTable table within the FG1 filegroup

CREATE TABLE TestLobTable
(
c1  int identity,
c2  char(8000)      default 'this is a test',
c3  varchar(max)    NULL
); -- will be created on FG1
GO

We can verify the filegroup location for this object by using sp_help (review the results of the 5th grid returned).

Next, we’ll add 1000 rows to the table. Each row is basically 8kb because of the size of the c2 column. For these inserts, we will insert data for c1 and c2 but the value for c3 is NULL.

INSERT dbo.TestLobTable DEFAULT VALUES;
GO  1000 -- create an 80MB table

To review the size (as well as the location) of this data, we’ll run a quick query against sys.dn_db_file_space_usage (which works for ALL databases in SQL Server 2012 prior to 2012 this DMV only returned space usage information for tempdb [DBID = 2]).

Since there are no other objects in the database this very generic view of the location will be just fine:

SELECT *
FROM sys.dm_db_file_space_usage;

Or, if you’re on a version prior to SQL Server 2012 – you can look at the data space id set for the table (from sys.tables). Here’s the query you need:

SELECT t.name AS [Table Name]
, lob_data_space_id AS [Data Space ID]
, ds.name AS [Data Space Name]
FROM sys.tables AS t
INNER JOIN sys.data_spaces AS ds
ON t.lob_data_space_id = ds.data_space_id;
GO

Even if we create a clustered index on the table, the data doesn’t change filegroup. However, SQL Server does reorder the data within FG1 to match the key order defined by the clustering key. So, this operation can take quite bit of time and log space (depending on recovery model).

CREATE UNIQUE CLUSTERED INDEX TestLobTableCL
ON TestLobTable (c1);
GO

Again, we can verify that the object still lives on the FG filegroup by using sp_help (5th grid).

Now, things will get a bit more interesting…

In SQL Server 2012, you can rebuild a table even if it has LOB data. So, you might think that the following will move the entire table… let’s see:

CREATE UNIQUE CLUSTERED INDEX TestLobTableCL
ON TestLobTable (c1)
WITH (DROP_EXISTING = ON, ONLINE = ON)
ON FG2;
GO

Now, check the results of sp_help. sp_help says it lives on FG2. However, we curently don’t have any data in the LOB portion of this table. What if we add some??

UPDATE TestLobTable
SET c3 = REPLICATE (convert(varchar(max), 'ABC'), 8000) -- creates a 24 row
WHERE c1 % 17 = 0; -- only 58 rows
GO

Now, re-check the results from both sp_help and the LOB query. sp_help still thinks we’re writing to FG2 but that’s correct because sp_help ONLY returns information about the in_row portion of the table. Even if you had specified a location for LOB data using TEXTIMAGE_ON when creating the table… sp_help would still ONLY reference the location for the in_row structure.
The only way to know where SQL Server LOB data is going is to use the query above. And, nothing you do can move the LOB data using just a standard rebuild.

So, let’s try to rebuild the object again…

CREATE UNIQUE CLUSTERED INDEX TestLobTableCL
ON TestLobTable (c1)
WITH (DROP_EXISTING = ON, ONLINE = ON)
ON FG3;
GO

And, recheck with our queries. Again, our in_row data has moved but not our LOB data.

My SQLServerPro blog post mentioned that you do have one option using partitioning but a great point is that this requires Enterprise Edition. So, it’s not ideal. If you are running on Enterprise Edition (or Enterprise Eval/Developer Edition), the scripts continue and show how to go throught he move process using a partition function/partition scheme.

• Here’s the test script for SQL Server 2008/2008R2 – be sure to adjust the paths/directories: MovingLOBData SQL Server 2008R2.sql (11.52 kb)
• Here’s the test script for SQL Server 2012 (leveraging ONLINE rebuilds) – again, make sure you adjust the paths/directories:
  MovingLOBData SQL Server 2012.sql (10.59 kb)
• Here’s a zip of the scripts that I use to add rows while I show the online operation (for 2012):
  AddLOBRows_Loop.zip (676.00 bytes)

And, in this week’s SQLServerPro blog post I’m going to give you other options – that can be done ONLINE without Enterprise Edition (thanks to a cool trick by Brad Hoff that I just learned too)!

However, the end result is that moving around and working with LOB data is more challenging. To be honest, if you plan to have a lot of LOB data, you will want to be very strategic with how you design that data/table (even considering better isolation for it) so that you have more options available later if you want to move it to another hard drive.

Having said all of that, it has gotten A LOT better now that SQL Server 2012 supports online operations for indexes where a LOB column is included. So, that’s a great start. But, moving LOB data is non-trivial and really needs to be thought out properly especially if you have a lot of data to move/migrate.

Thanks for reading!
kt