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!
9 thoughts on “Understanding LOB data (2008/2008R2 & 2012)”
Thanks a ton for a brilliant post!!
Using the information I found here, I was able to create a stored procedure for the community to use, which makes moving tables, indexes, heaps and LOBS really easy to do.
See http://sql10.blogspot.com/2013/07/easily-move-sql-tables-between.html for more.
I have found Mark’s procedure to be extremely helpful and would recommend it to anyone faced with having to migrate data between filegroups. I’ve only run into one small glitch so far which was easily overcome and is documented in a comment at the link Mark provided above.
Thanks for the nice demo!
I followed test script for SQL Server 2008/2008R2. How can I rebuild back as non-partitioned table.
Just rebuild on a filegroup (instead of a scheme) and the object will be non-partitioned again!
It did it! I rebuild back on FG2, now I see all the data and LOB on FG2.
Great work, thanks for sharing this :)
I know this is a bit old post..i came across this issue in SQL 2014, where if I rebuild a table with nvarchar(max)
same database behaves differently in SQL 2008 and 2014 ( using the same method\settings)
1) in 2008 R2 SP2 – TLog grows to 30-40GB
2) in 2014 SP2 – Tlog grows to 220GB
Both DBs were in full recovery.
Command: ALTER INDEX [TABLE__IE2] ON [DBNAME].[dbo].[TABLE] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = OFF, FILLFACTOR = 80)
Comment: ObjectType: Table, IndexType: NonClustered, ImageText: N/A, NewLOB: Yes, FileStream: N/A, ColumnStore: No, AllowPageLocks: Yes, PageCount: 6636385, Fragmentation: 0.01
how can I reduce this cause we are planing to move this database to an always on availability group.
I also noticed that if I do the rebuild with OFFLINE log will only grow to 50GB.
thanks in advance for any advice\comments.
Hey there Udula – I was talking with Paul about this and he thinks it’s a known bug. You should contact Product Support.
Hope that helps!
Thank you…we’ll log a case with MS support.